Início / dba / Perguntas / 117920
Accepted
mustaccio
Asked: 2015-10-14 11:04:45 +0800 CST

Resultados dm_db_index_physical_stats inconsistentes com as propriedades reais do objeto - quais podem ser os motivos?

  • 772

SQL Server 2008 RTM (10.0.1600.22)

Executando um relatório típico de fragmentação de índice em uma tabela:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count, 
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       and i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.avg_fragmentation_in_percent > 25

me diz que um índice nessa tabela tem 7 fragmentos, 7 páginas, cerca de 700 linhas e mais de 28% de fragmentação. No entanto, quando olho para as propriedades de armazenamento de índice no Management Studio, obtenho quase 9.000 páginas, mais de 12 milhões de linhas e 0,01% de fragmentação, que são os números esperados porque a tabela é grande e acabei de reconstruir e atualizar o índice estatísticas sobre a mesa.

O que, além de um possível bug na versão RTM sem suporte do SQL Server, pode ser o motivo dessa discrepância?

prova

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Antes de dar a alguém a chance de responder a isso, descobri o problema com minha consulta.

    dm_db_index_physical_stats(), ao executar no 'DETAILED'modo, retorna uma linha para cada nível de índice. No meu caso, o nível folha do índice realmente tem 0,01% de fragmentação e, portanto, é filtrado pelos s.avg_fragmentation_in_percent > 25critérios, deixando apenas um nível intermediário no resultado.

    Indiscutivelmente, você só gostaria de olhar para o nível folha do índice para identificar a fragmentação, portanto, a consulta deve ter a seguinte aparência:

    SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count, 
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       AND i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.index_level = 0 AND           -- leaf level only
    i.is_hypothetical = N'0' AND    -- exclude hypothetical indexes
    s.page_count > 1000 AND         -- look at large indexes only
    s.avg_fragmentation_in_percent > 25

    O critério i.is_hypothetical = N'0'vem da observação da consulta gerada pelo Management Studio no trace, conforme sugerido por Aaron Bertrand.

    Edit: Também pode fazer sentido excluir índices menores desta análise, porque sua fragmentação a) pode não ser importante e b) pode não ser significativamente melhorada.

    • 4

relate perguntas