SQL Server: Identificando índices "inúteis"

Após 2 meses sem novas postagens, hoje vou mostrar um script muito útil para nós DBAs SQL Server. Usei como “base” o script do artigo Unused Indexes in your databases (Portal SQL Server Central)

O Script

/*

Parte(A) identifica índices sem entrada na DMV 'dm_db_index_usage_stats',

isto indica que o índice nunca foi utilizado desde a inicialização do SQL Server

*/

SELECT DB_NAME(), OBJECT_NAME(i.object_id) AS 'Table', ISNULL(i.name, 'heap') AS 'Index', x.used_page_count AS 'SizeKB'

FROM sys.objects o

INNER JOIN sys.indexes i

ON i.[object_id] = o.[object_id]

LEFT JOIN sys.dm_db_index_usage_stats s

ON i.index_id = s.index_id and s.object_id = i.object_id

LEFT JOIN sys.dm_db_partition_stats x

ON i.[object_id] = x.[object_id] AND i.index_id = x.index_id

WHERE OBJECT_NAME(o.object_id) IS NOT NULL AND OBJECT_NAME(s.object_id) IS NULL

AND o.[type] = 'U' AND ISNULL(i.name, 'heap') <> 'heap'

UNION ALL

/*

Parte(B) identifica índices que não são mais utilizados desde a inicialização

da instância do SQL Server

*/

SELECT DB_NAME(), OBJECT_NAME(i.object_id) AS 'Table', ISNULL(i.name, 'heap') AS 'Index', x.used_page_count AS 'SizeKB'

FROM sys.objects o

INNER JOIN sys.indexes i

ON i.[object_id] = o.[object_id]

LEFT JOIN sys.dm_db_index_usage_stats s

ON i.index_id = s.index_id and s.object_id = i.object_id

LEFT JOIN sys.dm_db_partition_stats x

ON i.[object_id] = x.[object_id] AND i.index_id = x.index_id

WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0

AND o.[type] = 'U' AND ISNULL(i.name, 'heap') <> 'heap'

ORDER BY 2 ASC

Para o script foram utilizadas as seguintes referências:

sys.objects
http://msdn.microsoft.com/pt-br/library/ms177596.aspx

sys.indexes
http://msdn.microsoft.com/pt-br/library/ms190283.aspx

sys.dm_db_index_usage_stats
http://msdn.microsoft.com/pt-br/library/ms188755.aspx

sys.dm_db_partition_stats
http://msdn.microsoft.com/pt-br/library/ms187737.aspx

Unused Indexes in your databases
http://www.sqlservercentral.com/scripts/Indexing/72486/

Aproveitei o script e criei um report dinâmico, possibilitando escolher a instância e databases que serão analisados. Na próxima semana crio um post com o passo a passo de como criar este report.

Espero que o script seja útil para você amigo(a) DBA.

Até mais,

Bruno Santos