Reporte de Missing Index en SQL Server

Muchas veces en nuestro trabajo como DBAs o Desarrolladores de SQL Server, hemos visto en los planes de ejecución unas recomendaciones de gran impacto a través de los Missing Index. Con esto, en algún momento pienso que sería muy interesante generar un reporte de todas las sugerencias de índices que tenga una base de datos en SQL Server. Sin embargo primero debemos saber cómo interpretar un Missing Index.

Generar Reporte de Missing Index

No es una tarea que considero como algo que debe ser programado y seguir sus recomendaciones de una forma definitiva. Sin embargo los datos generados nos pueden dar una idea de las consultas que vamos ejecutando a lo largo del tiempo y las columnas que vamos necesitando de nuestras tablas.

Para obtener el reporte de missing index, consultamos las tablas de sistema que se explican en el post referenciado en el primer párrafo. En el detalle que obtenemos podremos ver lo siguiente:

• Las columnas que se requieren en un índice sugerido
• Las columnas que se requieren en el campo INCLUDE
• El costo del ahorro con la creación del índice
• El porcentaje de impacto con la creación
• La cantidad de veces que se realiza un SEEK por las columnas mostradas

Selecciona la base de datos sobre la cual quieres obtener el reporte y ejecuta el siguiente código.

	SELECT 
		'MISSING INDEXES' AS [REPORT],
		@@SERVERNAME AS [ServerName],
		[dbs].name AS [DatabaseName],
		[sch].name AS [SchemaName],
		[obj].name AS [ObjectName],
		[ddmid].statement AS [FullName],
		[CostSavings] = ROUND(
					[ddmigs].[avg_total_user_cost] * 
					[ddmigs].[avg_user_impact] * 
					([ddmigs].[user_seeks] + [ddmigs].[user_scans]
					), 0) / 100 ,
		[ddmigs].[avg_total_user_cost] AS [AVG_TOTAL_USER_COST],
		[ddmigs].[avg_user_impact] AS [AVG_USER_IMPACT],
		[ddmid].[equality_columns] AS [Equality],
		[ddmid].[inequality_columns] AS [Inequality],
		[ddmid].[included_columns] AS [Included],
		[ddmigs].[user_seeks] AS [UserSeeks],
		[ddmigs].[last_user_seek] AS [LastUserSeek]
	FROM [sys].[dm_db_missing_index_groups] AS [ddmig]
	INNER JOIN [sys].[dm_db_missing_index_group_stats] AS [ddmigs]
		ON [ddmig].[index_group_handle] = [ddmigs].[group_handle]
	INNER JOIN [sys].[dm_db_missing_index_details] AS [ddmid]
		ON [ddmid].[index_handle] = [ddmig].[index_handle]
	INNER JOIN [sys].[objects] AS [obj]
		ON [obj].object_id = [ddmid].object_id
	INNER JOIN [sys].[schemas] AS [sch] 
		ON [sch].schema_id = [obj].schema_id
	INNER JOIN [sys].[databases] [dbs]
		ON [dbs].database_id = [ddmid].database_id
	ORDER BY [CostSavings] DESC

Si prefieres puedes crear un procedimiento almacenado para una ejecución periódica. Tienes el código en el github.