DBAtlas · Atlas de operações de banco de dados

Ler sugestões de índices faltantes das DMVs (com ressalvas)

SQL ServerManutenção

Comando

USE [Musteri];
SELECT TOP (25)
       mid.statement                                   AS table_name,
       mid.equality_columns, mid.inequality_columns, mid.included_columns,
       migs.user_seeks, migs.user_scans,
       CAST(migs.avg_total_user_cost AS DECIMAL(18,2)) AS avg_query_cost,
       migs.avg_user_impact                            AS est_impact_pct,
       CAST(migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact / 100.0 AS DECIMAL(18,0)) AS score,
       migs.last_user_seek,
       N'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + N'_missing_'
         + CAST(mid.index_handle AS NVARCHAR(10)) + N'] ON ' + mid.statement + N' ('
         + ISNULL(mid.equality_columns, N'')
         + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
         + ISNULL(mid.inequality_columns, N'') + N')'
         + ISNULL(N' INCLUDE (' + mid.included_columns + N')', N'') AS create_stmt_draft
FROM   sys.dm_db_missing_index_details mid
  JOIN sys.dm_db_missing_index_groups mig       ON mig.index_handle = mid.index_handle
  JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
WHERE  mid.database_id = DB_ID()
ORDER BY score DESC;

-- 2019+: the query text behind each suggestion:
SELECT TOP (25) mid.statement AS table_name, misq.group_handle, misq.user_seeks,
       SUBSTRING(st.text, misq.last_statement_start_offset / 2 + 1, 300) AS statement_text
FROM   sys.dm_db_missing_index_group_stats_query misq
  JOIN sys.dm_db_missing_index_groups mig  ON mig.index_group_handle = misq.group_handle
  JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
  CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) st
WHERE  mid.database_id = DB_ID()
ORDER BY misq.user_seeks DESC;

O que faz

Pontua as sugestões de índices faltantes que o otimizador registrou no momento da compilação por uso, custo estimado e impacto, e gera um rascunho de statement CREATE INDEX; no 2019+ também mostra a consulta por trás de cada sugestão.

Quando usar

Após um novo release da aplicação, para uma varredura rápida das necessidades de índice de consultas com muita leitura, ou como lista inicial para uma revisão de design de índices.

Atenção

Não aplique sugestões às cegas: o otimizador ignora os índices existentes, frequentemente produz sugestões quase duplicadas (sobrepostas) e listas INCLUDE largas demais, e ordena as colunas por igualdade/desigualdade, e não por seletividade. Todo índice novo custa escrita e armazenamento; considere primeiro ampliar um índice existente. As DMVs são zeradas em restart, banco offline e mudanças de índice na tabela. Consulta somente leitura; segura.

Abrir no DBAtlas →

Relacionados