Estou executando uma ferramenta de auto-indexação em nosso banco de dados MS SQL (modifiquei um script originário da Microsoft que analisa as tabelas de estatísticas de índice - Automated Auto Indexing ). A partir das estatísticas, agora tenho uma lista de recomendações para índices que precisam ser criados.
Edit: Os índices descritos acima pegam informações dos DMVs que informam o que o mecanismo de banco de dados usaria para índices se estivessem disponíveis e os scripts pegam as recomendações Top x (por buscas, impacto do usuário etc.) e as colocam em uma tabela.
(Edit acima parcialmente retirado da resposta de Larry Coleman abaixo para esclarecer o que os scripts estão fazendo)
Como sou novo no administrador de banco de dados e, depois de fazer uma pesquisa rápida na rede, estou relutante em mergulhar e adicionar cegamente os índices recomendados. No entanto, não tendo experiência na área, estou procurando alguns conselhos sobre como determinar se as recomendações são necessárias ou não.
Preciso executar o SQL Profiler ou é melhor examinar o código que consulta as tabelas? E você tem alguma outra dica?
Eu uso scripts de análise de índice de Jason Strate . Eles informam quanto seus índices existentes são usados, bem como quantos índices ausentes teriam sido usados. Normalmente, não adiciono índices, a menos que eles representem mais de 5 ou 10% das consultas em uma tabela.
Mais importante, porém, é garantir que o aplicativo responda rápido o suficiente para os usuários.
artigos do blog de análise de índice de Jason Strate)
Atualmente, uso sp_BlitzIndex® ao realizar análises de índice.
Existem alguns conceitos e termos que são importantes para entender ao lidar com índices. Buscas, varreduras e pesquisas são algumas das maneiras pelas quais os índices serão utilizados por meio de instruções select. A seletividade das colunas-chave é essencial para determinar a eficácia de um índice.
Uma busca ocorre quando o Otimizador de Consulta do SQL Server determina que a melhor maneira de localizar os dados solicitados é verificando um intervalo dentro de um índice. As buscas geralmente acontecem quando uma consulta é "coberta" por um índice, o que significa que os predicados de busca estão na chave de índice e as colunas exibidas estão na chave ou incluídas. Uma verificação ocorre quando o Otimizador de Consulta do SQL Server determina que a melhor maneira de localizar os dados é verificar todo o índice e filtrar os resultados. Uma pesquisa geralmente ocorre quando um índice não inclui todas as colunas solicitadas, seja na chave de índice ou nas colunas incluídas. O otimizador de consulta usará a chave clusterizada (contra um índice clusterizado) ou o RID (contra um heap) para "pesquisar" as outras colunas solicitadas.
Normalmente, as operações de busca são mais eficientes do que as varreduras, devido à consulta física de um conjunto de dados menor. Existem situações em que isso não ocorre, como um conjunto de dados inicial muito pequeno, mas que vai além do escopo da sua pergunta.
Agora, você perguntou como determinar a eficácia de um índice e há algumas coisas a serem lembradas. As colunas de chave de um índice clusterizado são chamadas de chave de cluster. É assim que os registros se tornam exclusivos no contexto de um índice clusterizado. Todos os índices não clusterizados incluirão a chave clusterizada por padrão, para realizar pesquisas quando necessário. Todos os índices serão inseridos, atualizados ou excluídos de cada instrução DML respectiva. Dito isso, é melhor equilibrar os ganhos de desempenho nas instruções select com os acertos de desempenho nas instruções insert, delete e update.
Para determinar a eficácia de um índice, você deve determinar a seletividade de suas chaves de índice. A seletividade pode ser definida como uma porcentagem de registros distintos em relação ao total de registros. Se eu tiver uma tabela [person] com 100 registros totais e a coluna [first_name] contiver 90 valores distintos, podemos dizer que a coluna [first_name] é 90% seletiva. Quanto maior a seletividade, mais eficiente é a chave de índice. Mantendo a seletividade em mente, é melhor colocar suas colunas mais seletivas primeiro em sua chave de índice. Usando meu exemplo anterior [person], e se tivéssemos uma coluna [last_name] que fosse 95% seletiva? Gostaríamos de criar um índice com [last_name], [first_name] como a chave de índice.
Eu sei que essa foi uma resposta um pouco prolixa, mas realmente há muitas coisas que determinam a eficácia de um índice, e muitas coisas com as quais você deve pesar os ganhos de desempenho.
Recentemente descobri um fantástico script gratuito do pessoal da BrentOzar Unltd http://www.brentozar.com/blitzindex/
Isso faz uma boa análise de quais índices existem, com que frequência eles são usados e com que frequência o mecanismo de consulta está procurando um índice que não existe.
Sua orientação é geralmente boa. Às vezes, fica um pouco sugestivo demais de ideias. Eu geralmente fiz o seguinte até agora:
Não adicionei todos os índices recomendados e voltei uma semana depois para descobrir que eles não são mais recomendados, pois o mecanismo de consulta está usando alguns dos outros novos índices!
Geralmente você deve evitar índices em:
Índices clusterizados são bons - normalmente eles são baseados em sua chave primária. Eles ajudam o mecanismo de banco de dados a colocar os dados no disco em ordem. Muito essencial entender isso para as maiores tabelas, pois um bom índice clusterizado geralmente reduz o espaço que a tabela ocupa.
Reduzi algumas tabelas de 900 MB para 400 MB, apenas porque antes eram pilhas não estruturadas. http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
Reorganizar/Reconstruir
Você deve procurar por índices fragmentados. Um pouco de fragmentação é bom, não fique obsessivo! http://technet.microsoft.com/en-us/library/ms189858.aspx Conheça a diferença entre reorganizar e reconstruir!
Revise regularmente
As consultas mudam, os volumes de dados mudam, novos recursos são adicionados, os antigos são removidos. Você deve examiná-los uma vez por mês (ou com mais frequência se tiver grandes volumes) e procurar onde pode ajudar o banco de dados!
Quantos
Em um vídeo recente, Brent recomenda (normalmente) não mais de 5 índices em uma tabela com muita escrita (por exemplo, tabela de pedidos), e não mais de 10 se for lido muito mais do que escrito (por exemplo, tabela de log para análise) http:/ /www.youtube.com/watch?v=gOsflkQkHjg
No geral
Depende!
Sua milhagem varia de acordo com o banco de dados. Cubra o óbvio (sobrenome do funcionário, data do pedido, etc.) em suas tabelas maiores (agora/futuras). Monitore, revise e ajuste conforme necessário. Deve fazer parte da sua lista de verificação de rotina ao gerenciar seu(s) banco(s) de dados :)
Espero que isto ajude!
Normalmente, passa-se por ter uma carga de trabalho específica (consultas) e testando cuidadosamente o impacto de cada novo índice na carga de trabalho. Esse processo iterativo deve sempre incluir uma análise cuidadosa dos planos de execução, que revelariam quais índices são usados. O tópico de análise de uma consulta é longo, e começar com o capítulo dedicado do MSDN Analisando uma consulta é uma boa aposta.
Às vezes, quando a carga de trabalho é muito complexa ou o conhecimento do design do banco de dados é incompleto, usa-se o Orientador de Otimização do Mecanismo de Banco de Dados , que faz algumas análises automáticas de sua carga de trabalho e propõe alguns índices. As propostas devem, naturalmente, ser cuidadosamente analisadas e o impacto deve ser medido imediatamente.
Então, se você seguir minha ideia, adicionar um índice e medir o impacto é realmente apenas um caso de teste A/B : você executa sua carga de trabalho sem o índice como linha de base, depois executa com o índice, mede e compara com a linha de base e então decidir, com base em métricas observadas e medidas, se o impacto é benéfico. A carga de trabalho é melhor um conjunto de testes de boa qualidade, mas também pode ser uma repetição de uma carga de trabalho capturada, consulte Como: Repetir um arquivo de rastreamento .
Uma resposta mais sintética é olhar para a
sys.dm_db_index_usage_statsvisualização e ver como os índices estão sendo utilizados, mas isso geralmente é uma abordagem para fazer análises no local em uma carga de trabalho desconhecida (ou seja, um consultor chamado para ajudar provavelmente começaria com isso).A partir do SQL 2005, o SQL Server tem DMVs que informam o que o mecanismo de banco de dados usaria para índices se estivessem disponíveis. As visualizações podem dizer quais colunas devem ser colunas-chave, quais colunas devem ser incluídas e, mais importante, quantas vezes o índice teria sido usado.
Uma boa abordagem seria classificar a consulta de índices ausentes pelo número de buscas e considerar a adição dos principais índices primeiro.
Veja também: os documentos oficiais do MS DMV
É 2021 e decidi adicionar mais uma resposta.
Versões recentes do SQL Server vêm com um novo recurso muito útil chamado Query Store
Depois de habilitá-lo para um banco de dados, você pode revisar as consultas mais "caras" (em termos de CPU ou E/S), as consultas de "execução mais longa", etc. - por um período de tempo. E, o mais importante, examine seus planos de execução .
Observar um plano de execução geralmente fornecerá uma recomendação de índice explícita. Mas mesmo que isso não aconteça, você sempre pode dizer por que uma consulta específica está sendo lenta (identificando "scans" no plano etc.)
PS. Minha preferência pessoal é observar as "principais consultas por tempo total de execução" b/c não apenas informará o quão lenta é uma consulta, mas também com que frequência ela está sendo executada. Porque às vezes uma consulta "lenta" é boa, se for executada uma vez por semana em um domingo. Mas uma consulta "rápida" que é executada 100 vezes por segundo - é a que torna o servidor lento.
Depende de como essa tabela é usada. por exemplo, digamos que eu tenha uma tabela que é lida muitas vezes, mas atualizações e inserções são raras. Além disso, sempre consulto a tabela em alguma coluna de chave estrangeira. Fará sentido criar um índice (não clusterizado) sobre essa chave estrangeira para acelerar as consultas de leitura. Mas a desvantagem é que sua atualização de inserção ficará lenta.
Existem poucas consultas de estatísticas que informam quanto tempo as consultas estão demorando. Comece com os mais lentos. Se o predicado de consulta não tiver índice, criar um ajudará.