Missing Indexes Script

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.

The following script gets altered on display. n.VALUE is displayed but in the code it is actually n.value. The code display is wrong but it is correct in the code as presented in the editor. If copying from this page, please change the U-cased “VALUE” in the XML segment to “value” so it will work. A download of the script has been added at the end.

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  fls.database_id,fls.OBJECT_ID,fls.DB_Schema_Obj,fls.Name,fls.objtype,fls.execution_count
		,fls.equality_columns,fls.inequality_columns,fls.included_columns,fls.Impact,fls.unique_compiles
		,fls.user_seeks,fls.avg_total_user_cost,fls.avg_user_impact,fls.last_user_seek
		,fls.query_plan,fls.sql_text_inExecplan,fls.QueryDef
		,fls.SimilarQueries,fls.SimilarQueryPlans,fls.QueriesRelatedtoPlan
	FROM    #finalsel AS fls
	WHERE   InnerRowNum = 1
	ORDER BY Impact DESC;

DROP TABLE #MissingIndexInfo;
DROP TABLE #MissingIdxSuperInfo;
DROP TABLE #top20;
DROP TABLE #finalsel;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

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