Início / dba / Perguntas / 130845
Accepted
Ryan
Asked: 2016-03-01 14:21:16 +0800 CST

Índice do SQL Server, diferença entre fragmentação média e fragmentação total

  • 772

Usando o servidor SQL, se eu navegar para uma tabela no explorador de objetos, vá para seus índices, vá para suas propriedades e visualize a guia de fragmentação, a seção superior tem 2 linhas para mim, o preenchimento da página em porcentagem e a fragmentação total em porcentagem.

Também tenho uma consulta que uso ocasionalmente para obter informações de índice. Cortando alguns campos não relacionados, esta é a consulta:

SELECT t.name, ix.name, avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats  (db_id(), NULL, NULL, NULL, 'DETAILED') ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
WHERE  ix.index_id > 0
    AND ix.name IS NOT NULL

Quando executo esta consulta, obtenho 3 colunas, as 2 primeiras apontando para o próprio índice e, para esta consulta, a terceira é uma fragmentação em porcentagem para esse índice.

Esses 2 números geralmente são muito próximos, às vezes idênticos, mas também podem ser e frequentemente são diferentes. Tive casos em que a discrepância é enorme, mais de 50% em alguns casos, mas normalmente é algo como <1% vs 15-30%. O resultado das consultas é quase sempre o percentual mais alto.

A reconstrução do índice zerará completamente ou quase esses dois números (embora não necessariamente ambos sejam zero). Portanto, agora tenho 2 números para fragmentação em um índice. De acordo com a documentação de sys.dm_db_index_physical_stats e Index Properties (FragmentationPage) , ambos devem ser a fragmentação lógica do índice, portanto, devem sempre ser o mesmo número. Como é que estou obtendo números diferentes para o que deveria ser a mesma estatística?

sql-server fragmentation

1 respostas

  1. Best Answer

    Há uma grande diferença entre sua consulta e a consulta enviada pela interface do usuário (que você pode espionar usando rastreamento do lado do servidor, eventos estendidos etc.). No seu, o último argumento sys.dm_db_index_physical_statsé DETAILED:

    ... sys.dm_db_index_physical_stats( ..., 'DETAILED') ...

    Considerando que a interface do usuário está dentro do orçamento e envia SAMPLED:

    ... sys.dm_db_index_physical_stats( ..., 'SAMPLED') ...

    DETAILEDdigitaliza todas as páginas, enquanto SAMPLEDdigitaliza apenas 1 por cento . Isso mesmo, 1% . Obviamente, você paga por mais precisão, mas confiar nas informações sobre cada 100ª página para fazer qualquer julgamento sobre o estado do índice não é muito útil. Você sempre pode alterar sua consulta manual para SAMPLEDse seu objetivo for apenas corresponder à inutilidade da interface do usuário. :-)

    (Para ser justo, este tópico sobre a página de propriedades da interface do usuário deve documentar explicitamente o método usado para determinar a fragmentação. Tenho uma confirmação de Pedro Lopes, da Microsoft, de que ele o manterá em mente para uma próxima revisão do documento.)

    Apenas nunca use ou confie na interface do usuário. Não importa que seja uma dor de cabeça olhar para mais de um índice de cada vez, também é complicado descobrir o que está fazendo, e você sempre tem que se preocupar se ainda está fazendo o que você costumava pensar que fazia ... simplesmente não vale a pena a conveniência ocasional de procurar uma coisa em vez de ter uma consulta verificável e repetível para executar ...

    • 4

relate perguntas