O SQL Server é um poderoso sistema de gerenciamento de banco de dados que oferece várias ferramentas e recursos para otimizar o desempenho do seu banco de dados. Um desses recursos é a capacidade de identificar índices ausentes que podem ter um impacto significativo na sua carga de trabalho.

Neste post do blog, discutiremos o conceito de índices ausentes e como eles podem ser benéficos para seus bancos de dados do SQL Server.

O que são Índices Ausentes?

Índices ausentes são índices que não estão presentes no seu banco de dados, mas que podem potencialmente melhorar o desempenho das suas consultas. Quando o SQL Server executa uma consulta, ele procura a maneira mais eficiente de recuperar os dados necessários. Se não houver índices adequados disponíveis, ele pode ter que realizar uma varredura completa da tabela ou usar índices menos eficientes, resultando em uma execução mais lenta da consulta.

Identificando Índices Ausentes

O SQL Server fornece um conjunto de exibições de gerenciamento dinâmico (DMVs) e objetos de gerenciamento dinâmico (DMOs) que podem ser usados para identificar índices ausentes. Essas exibições e objetos contêm informações valiosas sobre o desempenho do seu banco de dados, incluindo consultas caras, tipos de espera e índices ausentes.

Uma maneira de identificar índices ausentes é usando a exibição sys.dm_db_missing_index_details. Essa exibição fornece informações detalhadas sobre os índices ausentes, como o nome da tabela, colunas e colunas incluídas. Ao analisar essas informações, você pode determinar quais índices estão faltando e têm um alto impacto na sua carga de trabalho.

Considerações antes de Criar Índices Ausentes

Antes de criar índices ausentes, é importante considerar alguns pontos essenciais:

1. Analisar o impacto do índice ausente na sua carga de trabalho. Determine quais tipos de consultas (SELECT, INSERT, UPDATE ou DELETE) estão usando a tabela e avalie os benefícios potenciais de criar o índice ausente.
2. Avoid criar índices duplicados ou desnecessários. Índices duplicados podem impactar negativamente o desempenho do seu banco de dados. Se as colunas de um índice existente já corresponderem às colunas do índice ausente sugerido, considere aproveitar o índice existente tornando-o mais amplo em vez de criar um novo.
3. Garanta que o novo índice não seja muito amplo. Adicionar muitas colunas a um índice pode aumentar seu tamanho e potencialmente degradar o desempenho. Inclua apenas as colunas necessárias no índice para otimizar sua eficiência.

Script para Encontrar Índices Ausentes

Aqui está um exemplo de script que pode ser usado para encontrar índices ausentes para todos os bancos de dados no SQL Server:

SELECT 
    migs.user_seeks AS [Estimated Index Uses],
    migs.avg_user_impact AS [Estimated Index Impact %],
    migs.avg_total_user_cost AS [Estimated Avg Query Cost], 
    db_name(mid.database_id) AS DatabaseID,
    OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS [SchemaName],
    OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') 
    + CASE
        WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_'
        ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '')
    + ']'
    + ' ON ' + mid.statement
    + ' (' + ISNULL(mid.equality_columns, '')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL(mid.inequality_columns, '')
    + ')'
    + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR = ?, ONLINE = ?, SORT_IN_TEMPDB = ?);', '') AS [Create TSQL],
    mid.equality_columns, 
    mid.inequality_columns, 
    mid.included_columns,
    migs.unique_compiles,
    migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
ORDER BY [Estimated Index Uses] DESC OPTION (RECOMPILE);

Este script recupera informações sobre o número estimado de usos do índice, porcentagem de impacto, custo médio da consulta, ID do banco de dados, nome do esquema, nome da tabela e a instrução T-SQL para criar o índice ausente. Também inclui detalhes sobre as colunas de igualdade, colunas de desigualdade, colunas incluídas, compilações exclusivas e última busca do usuário.

Ao executar este script, você pode obter informações valiosas sobre os índices ausentes em seus bancos de dados do SQL Server e tomar decisões informadas sobre a otimização do desempenho do seu banco de dados.

Esperamos que este post do blog tenha fornecido uma melhor compreensão dos índices ausentes no SQL Server e como eles podem ser benéficos para o desempenho do seu banco de dados. Se você tiver alguma ideia ou pergunta, deixe um comentário abaixo.

Obrigado por ler!