Tenho certeza de que haverá algumas respostas interessantes para isso, já que há muita discordância sobre quais métricas analisar. Eu escrevi DBCC INDEXDEFRAG, SHOWCONTIG e projetei suas substituições para 2005, além de escrever o conteúdo Books Online, então, darei minha opinião e explico os números no Books Online e o assistente de plano de manutenção para 2005, que eu escolhi.
As duas melhores métricas para procurar a fragmentação do índice são: 1) (2005) fragmentação média em porcentagem / (2000) fragmentação de varredura lógica 2) (2005) densidade média de páginas / (2000) média de bytes livres por página
Eles se aplicam igualmente a índices clusterizados e não clusterizados.
1 está medindo quanta fragmentação lógica existe. É quando a ordem lógica das páginas no nível da folha de um índice não corresponde à ordem física. Isso impede que o mecanismo de armazenamento faça leitura rápida eficiente durante as varreduras de intervalo. Portanto, o número 1 afeta o desempenho da varredura de intervalo, não o desempenho da pesquisa de singleton.
2 está medindo quanto espaço desperdiçado há em cada página no nível folha de um índice. Espaço desperdiçado significa que você está usando mais páginas para armazenar os registros, o que significa mais espaço em disco para armazenar o índice, mais I / O para ler o índice e mais memória para manter as páginas na memória no buffer pool.
Limites? Minha regra geral é menos de 10% de fragmentação, não faço nada. 10-30%, fazer um ALTER INDEX ... REORGANIZE (2005) / DBCC INDEXDEFRAG (2000). Mais de 30%, faz um ALTER INDEX ... REBUILD (2005) / DBCC DBREINDEX (2000). Estas são generalizações completas e os limites para você irão variar.
Para encontrar seus limites, faça um acompanhamento do desempenho da carga de trabalho em relação aos níveis de fragmentação e decida quando a degradação do desempenho é grande demais. Nesse ponto, você precisará abordar a fragmentação. Há um ato de equilíbrio entre viver com fragmentação e tomar o recurso de removê-lo.
Eu não toquei aqui nos trade-offs entre os dois métodos de remoção de fragmentação, coisas como FILLFACTOR / PADINDEX para tentar mitigar a fragmentação e fazer menos desfragmentação, mudanças nos padrões de esquema / acesso para aliviar a fragmentação ou diferentes tipos planos de manutenção.
Oh, btw, eu sempre recomendo não se preocupar com a fragmentação em índices com menos de 1000 páginas. Isso ocorre porque o índice é, provavelmente, principalmente residente na memória (e porque as pessoas pediram um número e eu tive que criar um).
Você pode ler mais sobre isso em meu artigo da TechNet Magazine sobre manutenção de banco de dados no link , no whitepaper de 2000 sobre o índice desfragmentar práticas recomendadas que eu ajudei a escrever no link e no meu blogue na categoria Fragmentação, no link .
Eu meio que respondi demais, eu acho, mas é um dos meus botões mais quentes. Espero que isso ajude: -)