In a previous article, I went over how SQL Server will recommend an index that it believes will help a query run faster, and I showed how to find missing index recommendations. Now, as I said in that article, you do not want to just blindly add whatever index SQL Server is suggesting. It is very good at recognizing when an index is needed, but not so good recommending what that index should be. The best course of action is to determine which query needs the index, and then begin testing. And while SQL Server does make it easy to see these recommended indexes, finding out which queries actually generated the recommendations isn’t quite as straight forward.
In order to find the query that generated the missing index recommendation, we’re going to have to dig through some XML in the plan cache. That’s right, I said XML. And even though SQL Server does have built in XML capabilities, they can sometimes be cumbersome and difficult to understand.
Thankfully, there are some really smart people in the SQL Server world who have already figured this out for us! The code below is a modification of a script originally written by Jason Strate (and Jonathan Kehayias has an excellent script that I have used for this as well!) This script starts by querying the sys.dm_exec_cached_plans and sys.dm_exec_query_plan DMVs to get any available execution plans that have a MissingIndexes element. Once the execution plan is recovered, the script parses through the XML of the plan to retrieve the database, schema, and table names, as well as the columns recommended in the index, and the cost and impact associated with the recommended index. It also retrieves the execution plan so that you can open it up and see the exact t-sql query that requested the index.
|
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 |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; GO WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') , PlanMissingIndexes AS (SELECT query_plan, usecounts FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE qp.query_plan.exist('//MissingIndexes') = 1) , MissingIndexes AS (SELECT stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]', 'sysname') AS DatabaseName, stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]', 'sysname') AS SchemaName, stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]', 'sysname') AS TableName, stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]', 'float') AS Impact, ISNULL(CAST(stmt_xml.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS FLOAT), 0) AS Cost, pmi.usecounts UseCounts, STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname') FROM stmt_xml.nodes('//ColumnGroup') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) WHERE cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY' FOR XML PATH('')),1,2,'' ) AS equality_columns, STUFF(( SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname') FROM stmt_xml.nodes('//ColumnGroup') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) WHERE cg.value('(@Usage)[1]', 'sysname') = 'INEQUALITY' FOR XML PATH('')),1,2,'' ) AS inequality_columns, STUFF((SELECT DISTINCT ', ' + c.value('(@Name)[1]', 'sysname') FROM stmt_xml.nodes('//ColumnGroup') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) WHERE cg.value('(@Usage)[1]', 'sysname') = 'INCLUDE' FOR XML PATH('')),1,2,'' ) AS include_columns, query_plan, stmt_xml.value('(@StatementText)[1]', 'varchar(4000)') AS sql_text FROM PlanMissingIndexes pmi CROSS APPLY query_plan.nodes('//StmtSimple') AS stmt(stmt_xml) WHERE stmt_xml.exist('QueryPlan/MissingIndexes') = 1) SELECT TOP 200 DatabaseName, SchemaName, TableName, equality_columns, inequality_columns, include_columns, UseCounts, Cost, Cost * UseCounts [AggregateCost], Impact, query_plan FROM MissingIndexes WHERE DatabaseName = '[YourDatabaseName]' AND TableName = '[YourTableName]' ORDER BY Cost * UseCounts DESC; |
NOTE: on the next to the last line of the script, I have a WHERE clause so you can specify the database name and table name to help you hone your query results in to find the specific index you are looking for. If you would rather pull back everything for the database, you just specify the database name, or you could even completely remove the WHERE clause to pull back all execution plans in cache with a MissingIndexes element (not recommended!).
ALSO NOTE: This query can take a while to run, especially on larger, busier servers. I recommend testing it out on a non-production server, because downloading free stuff off of the internet and running it straight in production is stupid.
Pingback: SQL Scripts: How To Find Missing Indexes - SQL Nuggets
Hi
I’m using it with WHERE database = XXXX
but how do I exclude offline databases from the first query?
Msg 942, Level 14, State 4, Line 4
Database ‘YYYY’ cannot be opened because it is offline.
I think you could just change the bottom of the script as follows:
FROM MissingIndexes m
INNER JOIN sys.databases d
ON m.DatabaseName = d.name
WHERE d.state = 0
ORDER BY Cost * UseCounts DESC;