Índice do SQL Server e uso do espaço de partição

Índice do SQL Server e uso do espaço de partição

Índice do SQL Server e uso do espaço de partição
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2020-05-04 | Comentários | Palavras-chave: 1 | 2 Mais> Administração de banco de dados

Problema

Recentemente, em

Uso do espaço de arquivos e grupos de arquivos do SQL Server – Parte 1, escrevi sobre como aprimorar o
nossa maneira de ver as informações sobre tabelas grandes, bem como os arquivos e grupos de arquivos
que os contêm. O problema que temos hoje é que existem algumas consultas
pode ser necessário analisar completamente um cenário, mas montá-los em um único local pode
ser pesado.

Solução

Eu queria continuar a discussão anterior aprimorando minhas consultas iniciais com
informações adicionais sobre índices e partições. Se você se lembra, tivemos uma mesa
chamado PartExample, que tinha um cluster particionado
chave e pelo menos um índice não agrupado que não estava alinhado pela partição. A saída
da minha consulta inicial não tornou óbvio que qualquer dado da tabela
estava no grupo de arquivos principal:

Para começar, eu gostaria de ver algo assim; uma tabela dinâmica com uma coluna
para cada índice e o espaço (em MB) que o índice ocupa em cada arquivo:

Isso ainda indica que um dos meus arquivos tem o tamanho errado, mas também mostra
que as porcentagens de preenchimento estão desativadas, que o índice de cluster não possui linhas na segunda
no grupo de arquivos Part4 e que
O ix_dt foi criado acidentalmente no
Grupo de arquivos PRIMÁRIO. Mas como obtenho esses resultados?

Posso pré-agregar o tamanho por arquivo da função de gerenciamento dinâmico,
sys.dm_db_database_page_allocations (que, reconhecidamente,

Eu aviso sobre o uso excessivo em uma dica anterior):

DECLARE @object_id int = OBJECT_ID(N'PartExample');
SELECT 
  FileID  = extent_file_id,
  IndexID = index_id,
  SizeMB  = CONVERT(decimal(18,2), COUNT(allocated_page_page_id)*8.192/1024)
FROM sys.dm_db_database_page_allocations(DB_ID(), @object_id, NULL, NULL, N'LIMITED')
GROUP BY extent_file_id, index_id;

Resultados:

Definitivamente, parece algo que poderíamos virar de lado, girando manualmente
usando IndexID (e como sabemos o valor correspondente
nomes, podemos definir facilmente os cabeçalhos das colunas):

DECLARE @object_id int = OBJECT_ID(N'PartExample');
;WITH dist AS
(
  SELECT
    FileID  = extent_file_id,
    IndexID = index_id,
    SizeMB  = CONVERT(decimal(18,2),COUNT(allocated_page_page_id)*8.192/1024)
  FROM sys.dm_db_database_page_allocations(DB_ID(), @object_id, NULL, NULL, N'LIMITED') AS pa
  GROUP BY extent_file_id, index_id
)
SELECT
  p.FileID,
  [cix_pe (index_id = 1)] = p.[1],
  [ix_dt (2)]             = p.[2]
FROM dist PIVOT (SUM(SizeMB) FOR IndexID IN ([1],[2])) AS p;

Resultados:

E você pode ver nesta saída como podemos produzir a captura de tela acima,
unir esses resultados à consulta original e ter esses índices dinâmicos
colunas ao lado dos detalhes do arquivo:

DECLARE @object_id int = OBJECT_ID(N'PartExample');
;WITH dist AS
(
  SELECT FileID  = extent_file_id,
         IndexID = index_id,
         SizeMB  = CONVERT(decimal(18,2),COUNT(allocated_page_page_id)*8.192/1024)
  FROM sys.dm_db_database_page_allocations(DB_ID(), @object_id, NULL, NULL, N'LIMITED')
  GROUP BY extent_file_id, index_id
),
p AS
(
  SELECT FileID, [1], [2] FROM dist
  PIVOT (SUM(SizeMB) FOR IndexID IN ([1], [2])) AS p
),
finfo AS
(
  SELECT FG          = fg.name,
         FileID      = f.file_id,
         LogicalName = f.name,
         [Path]      = f.physical_name,
         FileSizeMB  = f.size/128.,
         UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], 'SpaceUsed'))/128.
  FROM sys.database_files AS f
  INNER JOIN sys.filegroups AS fg ON f.data_space_id = fg.data_space_id
)
SELECT
  [Filegroup] = f.FG,
  f.FileID,    
  f.LogicalName,
  FileSizeMB  = CONVERT(decimal(18,2), f.FileSizeMB),
  FreeSpaceMB = CONVERT(decimal(18,2), f.FileSizeMB-f.UsedSpaceMB),
  [%]         = CONVERT(decimal(5,2), 100.*(f.FileSizeMB-f.UsedSpaceMB)/f.FileSizeMB),
  [cix_pe (index_id = 1)] = p.[1],
  [ix_dt (2)]             = p.[2]
FROM finfo AS f LEFT OUTER JOIN p ON f.FileID = p.FileID
ORDER BY f.FileID;

Resultados:

No modo de solução de problemas, porém, não conheço todos os detalhes do índice
com antecedência, e eu não quero ter que discutir isso sozinho. eu quero ligar
um procedimento armazenado que faz esse grunhido funcionar para mim, por…

Leia Também  Como o COVID-19 afeta as conferências - Brent Ozar Unlimited®

Construindo os detalhes do PIVOT dinamicamente

No exemplo de código acima, destaquei as partes da consulta eventual que
precisaria ser derivado dinamicamente para combinar o tabular e o pivotado
dados em um único conjunto de resultados. Também podemos adicionar dinamicamente uma coluna de contagem de partições
para qualquer índice com mais de uma partição. Eu criei o seguinte procedimento armazenado
em um banco de dados do utilitário comum em todas as nossas instâncias,
e pode ser executado a partir daí sem precisar estar no contexto certo do banco de dados
(você só precisa passar o nome correto do banco de dados):

CREATE PROCEDURE dbo.AssessDistribution_ByTable
  @ObjectName     sysname,
  @SchemaName     sysname       = N'dbo',
  @DatabaseName   nvarchar(260) = NULL,
  @FileGroupName  nvarchar(260) = NULL
AS
BEGIN
  SET NOCOUNT ON;   DECLARE @sql            nvarchar(max)  = N'SELECT @oi = OBJECT_ID(@on);',
          @ObjectID       int,
          @PivotColNames  nvarchar(max)  = N'',
          @PrettyHeaders  nvarchar(max)  = N'',
          @MaxHeaders     nvarchar(max)  = N'',
          @Context        nvarchar(1024) = COALESCE(QUOTENAME(@DatabaseName) + N'.', '')
                                           + N'sys.sp_executesql',
          @FullObjectName nvarchar(520)  = QUOTENAME(COALESCE(@SchemaName, N'dbo'))
                                           + N'.' + QUOTENAME(@ObjectName);   EXEC @Context @sql, N'@on nvarchar(512), @oi int OUTPUT', @FullObjectName, @ObjectID OUTPUT;   IF @ObjectID IS NULL
  BEGIN
      RAISERROR(N'%s does not exist in db %s.', 11, 1, @FullObjectName, @DatabaseName);
      RETURN;
  END   SET @sql = N'SELECT
    @pcn += N'','' + QUOTENAME(index_id),
    @mh  += N'','' + QUOTENAME(index_id) + '' = MAX('' + QUOTENAME(index_id) + '')'',
    @ph  += N'', ['' + COALESCE([name],''(heap'')
         +  N'' ('' + CASE WHEN index_id < 2 THEN ''id '' ELSE '''' END
         + RTRIM(index_id) + '') size] = ps.['' + RTRIM(index_id) + '']''
         + CASE WHEN EXISTS (SELECT 1 FROM sys.partitions WHERE
             [object_id] = @oi AND index_id = i.index_id AND partition_number > 1)
            THEN N'', [part cnt ('' + RTRIM(index_id) + N'')] = pc.['' + RTRIM(index_id) + '']''
            ELSE '''' END
    FROM sys.indexes AS i WHERE [object_id] = @oi;';   EXEC @Context @sql,
       N'@oi int, @pcn nvarchar(max) OUTPUT, @ph nvarchar(max) OUTPUT, @mh nvarchar(max) OUTPUT',
       @ObjectID, @PivotColNames OUTPUT, @PrettyHeaders OUTPUT, @MaxHeaders OUTPUT;   SET @sql = N';WITH dst AS (
      SELECT FileID  = extent_file_id,
             IndexID = index_id,
             SizeMB  = CONVERT(decimal(18,2),COUNT(allocated_page_page_id)*8.192/1024),
             PartitionCount = COUNT(DISTINCT partition_id)
      FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, NULL, NULL, N''LIMITED'')
      GROUP BY extent_file_id, index_id
    ),
    ps AS (SELECT FileID, $pcn$ FROM dst PIVOT (SUM(SizeMB) FOR IndexID IN ($pcn$)) p),
    pc AS (SELECT FileID, $mh$ FROM (SELECT FileID, $pcn$ FROM dst
           PIVOT (MAX(PartitionCount) FOR IndexID IN ($pcn$)) p) AS x GROUP BY FileID),
    finfo AS (
      SELECT FG          = fg.name,
             FileID      = f.file_id,
             LogicalName = f.name,
             [Path]      = f.physical_name,
             FileSizeMB  = f.size/128.0,
             UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], N''SpaceUsed''))/128.0,
             GrowthMB    = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128.0 END,
             MaxSizeMB   = NULLIF(f.max_size, -1)/128.0,
             DriveSizeMB = vs.total_bytes/1048576.0,
             DriveFreeMB = vs.available_bytes/1048576.0
      FROM sys.database_files AS f
      INNER JOIN sys.filegroups AS fg ON f.data_space_id = fg.data_space_id
      CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs
      WHERE fg.name = COALESCE(@FileGroupName, fg.name)
    )
    SELECT
      [Filegroup] = f.FG,
      f.FileID,    
      f.LogicalName,
      f.[Path],
      FileSizeMB  = CONVERT(decimal(18,2), f.FileSizeMB),
      FreeSpaceMB = CONVERT(decimal(18,2), f.FileSizeMB - f.UsedSpaceMB),
      [% Free]    = CONVERT(decimal(5,2), 100.0*(f.FileSizeMB - f.UsedSpaceMB) / f.FileSizeMB),
      GrowthMB    = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), ''% warning!''),
      MaxSizeMB   = CONVERT(decimal(18,2), f.MaxSizeMB)
      $ph$,
      DriveSizeMB = CONVERT(bigint, DriveSizeMB),
      DriveFreeMB = CONVERT(bigint, DriveFreeMB),
      [% Free]    = CONVERT(decimal(5,2), 100.0*(DriveFreeMB)/DriveSizeMB)
    FROM finfo AS f
    LEFT OUTER JOIN ps ON f.FileID = ps.FileID
    LEFT OUTER JOIN pc ON f.FileID = pc.FileID
    ORDER BY [Filegroup], f.FileID;';   SET @sql = REPLACE(REPLACE(REPLACE(@sql,
             N'$ph$',  @PrettyHeaders),
             N'$pcn$', STUFF(@PivotColNames, 1, 1, N'')),
             N'$mh$'STUFF(@MaxHeaders, 1, 1, N''));   PRINT @sql;   EXEC @Context @sql, N'@ObjectID int, @FileGroupName sysname', @ObjectID, @FileGroupName; END
GO

Quando o executo com uma chamada como esta:

EXEC dbo.AssessDistribution_ByTable 
     @ObjectName   = N'PartExample',
     @SchemaName   = N'dbo',
     @DatabaseName = N'FGExample';

Eu recebo esta saída:

O que me aproxima do resultado original que eu estava procurando no começo
do

Leia Também  Anunciando dois cursos gratuitos de fundamentos ao vivo nesta quarta e quinta-feira

Parte 1 –
Apenas indico que um índice está alinhado por partição pela presença ou ausência do
coluna de contagem de partições e mostre a coluna MaxSize
no local errado na captura de tela acima:

Ressalvas

Como em qualquer solução complicada, existem algumas falhas aqui.

  • Mencionarei novamente que sys.dm_db_database_page_allocations
    tem algum

    desafios potenciais de desempenho. Eu usaria essa abordagem somente quando você
    tenha certeza de que você precisa investigar a distribuição de dados entre grupos de arquivos
    e arquivos de dados.

  • Não testei completamente isso com pontos de montagem ou cotas; SQL Server pode
    nem sempre ser uma fonte confiável de informações sobre a porcentagem de espaço livre
    ou espaço para crescer.
  • Eu não olhei como integrar tipos de compactação nessa saída,
    nem procurei índices columnstore ou otimizados para memória, qualquer um dos quais pode
    desempenhar um papel nas discrepâncias de armazenamento.
Próximos passos

Esta solução supõe que eu já conheça a tabela de problemas a investigar. Se eu apenas
sabia qual banco de dados está tendo um problema de espaço, eu poderia alterar o procedimento para obter
detalhes para a tabela superior, ou tabelas n superiores, em termos
de espaço ou contagem de linhas? Se eu apenas souber que a instância está tendo problemas, eu poderia
deixar de fora os detalhes de qual banco de dados eu preciso investigar? Fique atento à parte
3! Até então, consulte estas dicas relacionadas e outros recursos:

Última atualização: 2020-05-04

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor que remonta ao ASP clássico e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e também blogs do sqlblog.org.

Ver todas as minhas dicas



cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br