Come identificare gli indici candidati alla cancellazione

La creazione di indici inutili porta a due spiacevoli conseguenze: maggiore tempo necessario per l’esecuzione di istruzioni di insert/update/delete, inutile spreco di spazio su disco.

Come fare per identificare gli indici del database candidati a una bella operazione di drop?

Ci si rifa a una serie di viste di sistema che SQL Server ci mette a disposizione per valutare quante volte un indice è stato oggetto di modifica, quante volte è stato usato per un’operazione di query, quanto spazio su disco occupa:

WITH A AS (
	SELECT
		DB_NAME(A.[DATABASE_ID]) [DbName],
		OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
		I.[NAME] AS [INDEX NAME], 
		A.LEAF_INSERT_COUNT, 
		A.LEAF_UPDATE_COUNT, 
		A.LEAF_DELETE_COUNT,
		A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT as LEAF_EVENTS
	FROM  SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
		INNER JOIN SYS.INDEXES AS I 
			ON I.[OBJECT_ID] = A.[OBJECT_ID] 
			AND I.INDEX_ID = A.INDEX_ID
	WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
), B AS (
	SELECT
		DB_NAME(S.[DATABASE_ID]) [DbName],
		OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
		I.[NAME] AS [INDEX NAME], 
		USER_SEEKS, 
		USER_SCANS, 
		USER_LOOKUPS, 
		USER_UPDATES 
	FROM SYS.DM_DB_INDEX_USAGE_STATS AS S 
		INNER JOIN SYS.INDEXES AS I 
			ON I.[OBJECT_ID] = S.[OBJECT_ID] 
			AND I.INDEX_ID = S.INDEX_ID 
	WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
), iSize as (
	SELECT
		tn.[name] AS [OBJECT NAME],
		ix.[name] AS [INDEX NAME],
		SUM(sz.[used_page_count]) * 8 / 1024 AS [Index size (MB)]
	FROM sys.dm_db_partition_stats AS sz
		INNER JOIN sys.indexes AS ix
			ON sz.[object_id] = ix.[object_id] 
			AND sz.[index_id] = ix.[index_id]
		INNER JOIN sys.tables tn
			ON tn.OBJECT_ID = ix.object_id
	GROUP BY tn.[name], ix.[name]
), RES1 AS (
	SELECT
		COALESCE(A.DbName, B.DbName) as DbName,
		COALESCE(A.[OBJECT NAME], B.[OBJECT NAME]) as [OBJECT NAME],
		COALESCE(A.[INDEX NAME], B.[INDEX NAME]) as [INDEX NAME],
		A.LEAF_INSERT_COUNT, 
		A.LEAF_UPDATE_COUNT, 
		A.LEAF_DELETE_COUNT,
		A.LEAF_EVENTS,
		B.USER_SEEKS, 
		B.USER_SCANS, 
		B.USER_LOOKUPS, 
		B.USER_SEEKS + B.USER_SCANS + B.USER_LOOKUPS AS USER_QUERY_EVENTS,
		B.USER_UPDATES
	FROM A
		FULL OUTER JOIN B
			ON A.[OBJECT NAME] = B.[OBJECT NAME]
			AND A.[INDEX NAME] = B.[INDEX NAME]
			AND A.DbName = B.DbName
)
SELECT RES1.*, iSize.[Index size (MB)]
FROM RES1
	LEFT JOIN iSize
		ON iSize.[OBJECT NAME] = RES1.[OBJECT NAME]
		AND iSize.[INDEX NAME] = RES1.[INDEX NAME]

NOTA IMPORTANTE: Le statistiche legati all’utilizzo dell’indice, relativamente cioè alle operazioni di seek, scan e lookpu, sono disponibili solo dall’ultimo avvio di SQL Server e se la cache è bella densa, forse anche per un periodo più recente!