SQL SERVER – Missing Index Script
Para analisar a performance de uma base de dados esta script é um bom ponto de partida. Avalie o campo de impacto estimado (Estimated_Impact), quanto maior o valor maior a melhoria que poderá obter.
Nota, cada tabela deve ter um número limitado de índice, até 10. Avalie a necessidade de existirem índices duplicados no artigo “Duplicated Indexes”.
SELECT Estimated_Impact
, Last_Seek
, [TableName]
, SQLScript
FROM
( SELECT Estimated_Impact= GS.avg_user_impact * (GS.user_seeks + GS.user_scans)
, Last_Seek = GS.last_user_seek
, [TableName] = OBJECT_NAME(D.OBJECT_ID,D.database_id)
, SQLScript = 'CREATE INDEX [IX_' + OBJECT_NAME(D.OBJECT_ID,D.database_id)
+ '_' + REPLACE(REPLACE(REPLACE(ISNULL(D.equality_columns,''),', ','_'),'[',''),']','')
+ CASE WHEN D.equality_columns IS NOT NULL AND D.inequality_columns IS NOT NULL
THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(D.inequality_columns,''),', ','_'),'[',''),']','') + ']'
+ ' ON ' + D.statement
+ ' (' + ISNULL (D.equality_columns,'')
+ CASE WHEN D.equality_columns IS NOT NULL AND D.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END
+ ISNULL (D.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + D.included_columns + ')', '')
FROM sys.dm_db_missing_index_groups G
INNER JOIN sys.dm_db_missing_index_group_stats GS ON GS.group_handle = G.index_group_handle
INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
WHERE D.database_ID = DB_ID()
) T
WHERE Estimated_Impact > 500
ORDER BY Estimated_Impact DESC