missing_idx

Frequently we hear the analogy that <insert item here> is like opinions, everybody has one and not all of them are good (some may stink).

Well, this may just be another one of those <items>.  Whether it stinks or not may depend on your mileage.

I had shared a similar script back in January 2012 and wanted to share something a little more current.  As is the case for many DB professionals, I am always tweaking (not twerking) and refining the script to try and make it more robust and a little more accurate.

This version does a couple of things differently than the previous version.  For one, this is a single database at a time (the prior version looped through all of the databases with a less refined query).  Another significant difference is that this query is designed to try and pull information from multiple places about the missing indexes and execution statistics.  I felt this could prove more advantageous and useful than to just pull the information from one place.

Here is the current working script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb..#MissingIndexInfo','U') IS NOT NULL 
	DROP TABLE #MissingIndexInfo;
IF OBJECT_ID('tempdb..#MissingIdxSuperInfo','U') IS NOT NULL 
	DROP TABLE #MissingIdxSuperInfo;
IF OBJECT_ID('tempdb..#top20','U') IS NOT NULL 
	DROP TABLE #top20;
 
SET NOCOUNT ON;
 
WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
 
SELECT query_plan, plan_handle,sql_handle,execution_count,
       n.VALUE('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, 
       --n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact, 
       DB_ID(REPLACE(REPLACE(n.VALUE('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id, 
       OBJECT_ID(n.VALUE('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.VALUE('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.VALUE('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID, 
       n.VALUE('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + 
           n.VALUE('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + 
           n.VALUE('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')  
       AS STATEMENT
INTO #MissingIndexInfo 
FROM  
( 
   SELECT query_plan,plan_handle,sql_handle,execution_count
   FROM (    
           SELECT DISTINCT plan_handle,sql_handle,execution_count
           FROM sys.dm_exec_query_stats
         ) AS qs 
       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp     
   WHERE tp.query_plan.exist('//MissingIndex')=1 
) AS tab (query_plan,plan_handle,sql_handle,execution_count) 
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n) 
WHERE n.exist('QueryPlan/MissingIndexes') = 1 
	And DB_ID(REPLACE(REPLACE(n.VALUE('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) = DB_ID()
 
CREATE CLUSTERED INDEX ci_sqlhandle ON #MissingIndexInfo(sql_handle)
 
SELECT mii.database_id,mii.OBJECT_ID,Mii.plan_handle,mii.sql_handle	,mii.execution_count
	,CA.equality_columns,CA.inequality_columns,CA.included_columns
	,CA.Impact
	,CA.unique_compiles,CA.user_seeks,CA.avg_total_user_cost,CA.avg_user_impact,CA.last_user_seek
INTO #MissingIdxSuperInfo
FROM #MissingIndexInfo MII
CROSS APPLY ( SELECT mid.database_id,mid.OBJECT_ID,mid.equality_columns
	, mid.inequality_columns
	, mid.included_columns
	,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost,migs.avg_user_impact
	,migs.last_user_seek
	,(avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
 
	FROM sys.dm_db_missing_index_group_stats AS migs 
		INNER JOIN sys.dm_db_missing_index_groups AS mig 
			ON migs.group_handle = mig.index_group_handle 
		INNER JOIN sys.dm_db_missing_index_details AS mid 
			ON mig.index_handle = mid.index_handle 
			AND mid.database_id = DB_ID() 
 
		) CA
 
WHERE 1 =1
	AND ca.database_id = mii.database_id
	AND ca.OBJECT_ID = mii.OBJECT_ID
 
SELECT DISTINCT TOP 20 plan_handle,MAX(Impact) AS Impact
	INTO #top20
	FROM #MissingIdxSuperInfo
	GROUP BY plan_handle
	ORDER BY Impact DESC;
 
WITH finalsel AS (
SELECT SI.*
 
	,ROW_NUMBER() OVER (partition BY si.equality_columns, si.inequality_columns,si.execution_count
							ORDER BY si.impact DESC) AS RowNum
	FROM #MissingIdxSuperInfo SI
		INNER Join #top20 t
			ON t.plan_handle = SI.plan_handle
)
SELECT fs.*
	,mii.query_plan
	,mii.sql_text AS sql_text_inExecplan
	,mii.STATEMENT AS DB_Schema_Obj
	,sub.name
	,ROW_NUMBER() OVER (partition BY fs.plan_handle,sub.name ORDER BY sub.name) AS InnerRowNum
	,(SELECT COUNT(*) 
		FROM sys.dm_exec_query_stats s 
		WHERE s.query_hash = sub.query_hash
				) AS SimilarQueries
	,(SELECT COUNT(*) 
		FROM sys.dm_exec_query_stats s 
		WHERE s.query_plan_hash = sub.query_plan_hash
				) AS SimilarQueryPlans
	,(	SELECT COUNT(qs.query_hash)
		FROM sys.dm_exec_query_stats qs
		WHERE qs.sql_handle = mii.sql_handle
		GROUP BY qs.sql_handle) AS QueriesRelatedtoPlan
	,(SELECT 
			REPLACE
			(
				REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
				REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
				REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
					CONVERT
					(
						NVARCHAR(MAX),
						N'--' + NCHAR(13) + NCHAR(10) + ist.TEXT + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
					),
					NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),
					NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
					NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),
					NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
					NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),
					NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0),N''
			) AS [processing-instruction(query)]
			FROM sys.dm_exec_sql_text(fs.sql_handle) AS ist 
		FOR XML PATH(''),TYPE
	) AS QueryDef
	,cp.objtype
	INTO #finalsel
	FROM finalsel fs
		INNER Join #MissingIndexInfo MII
			ON fs.database_id = mii.database_id
			And fs.OBJECT_ID = mii.OBJECT_ID
			And fs.plan_handle = Mii.plan_handle
		INNER Join sys.dm_exec_cached_plans cp
			ON fs.plan_handle = cp.plan_handle
		CROSS APPLY (SELECT TOP 1 ISNULL(p.name,'ADHOC') AS NAME,query_hash,query_plan_hash
						FROM sys.dm_exec_query_stats qs
						CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
						LEFT Outer Join sys.procedures p
							ON qt.objectid = p.OBJECT_ID
						WHERE qs.plan_handle = fs.plan_handle) sub
	WHERE RowNum = 1
	;
 
SELECT * 
	FROM #finalsel
	WHERE InnerRowNum = 1
	ORDER BY Impact DESC;
 
DROP TABLE #MissingIndexInfo;
DROP TABLE #MissingIdxSuperInfo;
DROP TABLE #top20;
DROP TABLE #finalsel

As has been the case in the past, this is not the end query.  I have a backlog of updates I want to make to the query and try to create something even more useful from it.

As a bit of fair caution, just because an index shows up as a recommended implementation from this script, it does not mean you should blanket implement it.  Also, like the previous script, the higher the impact value, the more probable the index will have a positive impact on your system.

In addition to the impact, I also look at the “Similar Queries” and “Similar Plans” numbers from the result set.  These values can sometimes lead to an understanding that their are other queries or other plans that could also benefit from the creation of the index.  Your mileage may vary.