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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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(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; |
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.
Download script Missing_Index.
Get the following error with this script
Msg 4121, Level 16, State 1, Line 13
Cannot find either column “n” or the user-defined function or aggregate “n.VALUE”, or the name is ambiguous.
Script fails for me when I run it. I tried on SQL 2005, 2008 and 2012. Error below.
Msg 4121, Level 16, State 1, Line 11
Cannot find either column “n” or the user-defined function or aggregate “n.VALUE”, or the name is ambiguous.
It looks like the script plugin U-cased the word value in the XML when pasting it into the blog. I will fix that.
n.VALUE should be n.value
There a numerous case discrepancies that cause errors on a case sensitive server. The table aliases MII, CA, and SI as well as the columns SI.Impact and sub.NAME.
I have fixed the CS stuff and re-uploaded the script
@Jason
FYI: Your table aliasing breaks when used on a case-sensitive server: ca != CA
The script is updated for CS.
Excellent script, thank you!
Hello Jason,
Excellent script. The only thing that would imporve its usefulness, if you can
a. Make it to take database name (so that a name would tell the script to pull info for a particuliar database)
b. Skip databases that are in noreovery mode (e.g. logshipping).
Good work !
Thanks,
Kin