Método para encontrar índices desnecessários de um banco de dados operacional do SQL Server?

Question

Além de desperdiçar espaço no banco de dados, índices desnecessários no SQL Server podem retardar a inserção e atualização de operações. Os desenvolvedores que não têm experiência em princípios de banco de dados, às vezes, tendem a criar índices de tabelas que não fazem sentido para as consultas em execução.

Existe um procedimento ou ferramenta comum para o SQL Server 2005/2008 para analisar a carga de trabalho do banco de dados e dar dicas de que os índices nunca são usados ou não são necessários em um determinado banco de dados operacional?

Obrigado!

    

Answer 1

Estou vendo um script T-SQL bonitinho aqui ( link ) para mostrar os índices não utilizados que devem ser executados no SQL Server 2005. Outro link aqui também.

Parece que dm_db_index_usage_stats é a chave para tudo isso. Muito arrumado! (Consulte link Vou ter que olhar alguns bancos de dados de produção que criei agora para ver como são essas estatísticas ( smile )

Edit: Alguns muito bom addt'l fundo aqui: link

    

Answer 2

Algumas coisas a serem lembradas sobre o uso de sys.dm_db_index_usage_stats:

1. A saída contém apenas índices que foram usados desde a última vez em que o banco de dados foi iniciado. Quando um banco de dados é encerrado, todas as entradas do cache de informações na memória desse banco de dados são removidas. Da mesma forma, o cache não sobrevive a uma reinicialização instantânea. Não há como limpar manualmente as entradas de um determinado banco de dados sem reiniciar o banco de dados. Os vários artigos mencionados na primeira resposta (e também no meu blog) descrevem como capturar a saída em vários momentos para fazer a análise de séries temporais.
2. Certifique-se de testar seu ciclo de negócios inteiro . Você não deseja remover um índice usado para um relatório de fim de mês ou uma consulta de CEO, mesmo que seja tentador.
3. Assegure-se de compreender as várias contagens e o que elas significam antes de tomar uma decisão sobre se o índice está sendo usado de forma lucrativa ou simplesmente mantido às custas.

Espero que isso ajude.

PS Mais uma coisa para os outros que estão lendo isso e imaginando se existe um método equivalente para o SQL Server 2000 - não, nós (como eu estava na equipe no momento) apenas adicionamos a capacidade para 2005 em diante.

    

Answer 3

Temos um artigo wiki no SQLServerPedia com um script para fazer isso, além de um vídeo tutorial sobre como usá-lo:

Um para encontrar índices que não estão sendo usados:

link

E outro para encontrar índices que você deve adicionar:

link

    

Answer 4

tente juntar-se à esquerda em select * de Sys.dm_db_index_usage_stats. Você pode ver quais nunca foram tocados. Certifique-se de que eles estão disponíveis há tempo suficiente, mas isso pode ocorrer no dia seguinte à criação de um novo índice ...

Os índices também não devem ser usados hoje, mas à medida que o tamanho dos dados aumenta, eles são considerados uma aposta melhor pelo otimizador. Normalmente, com pequenas (novas) tabelas, o otimizador irá sempre verificar, mas começará a procurar quando o número de linhas tiver subido acima do ponto de inflexão

Dave J

Edit: Evan me bateu nisso, mas ele está no local.

Editar 2: conselho corrigido, esqueci o bit anti-junção!

    

Answer 5

Eu fiz alguma consulta relacionada ao índice e mostrei isso no meu blog ( link )

    

Answer 6

Tenha em mente que o SQL Server não conhece seus negócios. Não sabe que decisões táticas e estratégicas de negócios sua empresa fez, está fazendo e fará.

Isso significa que um índice que está faltando hoje pode não ser relevante amanhã ou pode se tornar mais relevante. A mesma lógica se aplica a índices que estão subutilizados ou não são usados.

Quando o SQL Server RTM, eu escrevi meus próprios scripts para ler e relatar nas tabelas DM e pensei em automatizar sua atividade. Uma olhada em um relatório de índice ausente onde o SQL Server pedia que todas as colunas restantes de uma tabela fossem adicionadas como colunas "Incluir" em um índice de uma tabela com conteúdo muito dinâmico era o impedimento de que precisava não automatize o processo.

Ainda uso meus scripts, mas aplico as decisões de negócios da minha empresa ao que faço - algo que posso fazer em uma empresa menor.

Como sempre, conheça seus dados, conheça seus negócios, conheça a direção da sua empresa.