SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Can You Dig It? – Missing Indexes

She can dig it!
D Sharon Pruitt

When I started using XQuery to dig into the plan cache, it was just searching for some parallelism and I really wasn’t aware that I was using XQuery.  I just kind of glanced over it and let it be.

Fast forward a couple years later and I was at a PASS session where Grant Fritchey (Blog | @GFritchey) was presenting.  In his session he discussed some performance tuning techniques and mentioned a method for finding missing indexes.  His method was quite different that what I had been used to.

Missing Index DMVs

Prior to that session, I used a fairly common technique to find missing indexes.  I would query some DMVs included with the release SQL Server 2005 released that were designed just for this purpose.

There are four DMVs that can be used for this.  They are:

Together these DMVs can be used find indexes in a server that could help improve the performance of some queries on the server.  I talk about using this information some in this blog post.  There is also more good information on this here and here.

There’s a slight problem with using these DMVs though.  While they do a decent job of indicating indexes that would be useful, they don’t let you know the specific queries that will be improved by adding the indexes.  Certainly an index that can be used in many places and across multiple queries would be good.  Knowing the query that will be improved will be better.

Missing Index Queries

Listing the indexes that would improve queries is possible using a couple DMVs other that the missing index DMVs above.  With a query against sys.dm_exec_cached_plans and a little XQuery this information is readily available.

As was mentioned in a previous post, the DMV sys.dm_exec_cached plans has the SHOWPLAN XML for an execution plan.  Within an execution plan one of the possible elements is MissingIndexes.  This element, when present, contains the details for an index that would improve the performance of the query.  It includes the name of table that could use the index, the columns for the index and included columns, and information of the improvement in the query.

To illustrate this, let’s consider the following query:

USE AdventureWorks

SELECT DueDate FROM Sales.SalesOrderHeader
WHERE DueDate Between '20010701' AND '20010731'

Looking at the execution plan for the query above, the following should be returned:


The execution plan indicates that this query could be improved by the addition of an index.  We can also look at the SHOWPLAN XML and find that there is an element for MissingIndexes which contains the following information:


Missing Indexes in the Plan Cache

Finding one execution plan with this missing index information in it is useful.  Going into the plan cache and finding all of the plans that this element exists within is a treasure trove and that is exactly what can be done.

Now that we know that there is a MissingIndexes element possible in the SHOWPLAN XML, we can utilize the exist() method to search for the element.  XQuery can then be leveraged to search the entire plan cache through the DMV sys.dm_exec_cached_plans to find all plans with the MissingIndexes element.

Building this logic into a query can be accomplished with the following:


WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan, usecounts
FROM sys.dm_exec_cached_plans ph
CROSS APPLY sys.dm_exec_query_plan(ph.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex')=1

Executing the query will return a list of all of the execution plans in the plan cache that have a MissingIndex element.  This result set below shows the output:


Once all of the plans with MissingIndex elements are discovered, the plans can be shred to determine the SQL text, the index that is missing, and the count of the number of times the plan was used.  The query below can be used to expand out this information:


;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
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 (
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
,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
,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 DatabaseName
,CAST('<?query --' + CHAR(13) + sql_text + CHAR(13) + ' --?>' AS xml) AS SQLText
,CAST('<?query --' + CHAR(13) + 'CREATE NONCLUSTERED INDEX IX_'         + REPLACE(REPLACE(REPLACE(SchemaName,'_',''),'[',''),']','')+'_'         + REPLACE(REPLACE(REPLACE(TableName,'_',''),'[',''),']','')+'_'         + COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(equality_columns,'_',''),'[',''),']',''),',',''),' ',''),'')         + COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(inequality_columns,''),'_',''),'[',''),']',''),',',''),' ',''),'')         + ' ON '         + SchemaName + '.' + TableName + '('             + STUFF(COALESCE(',' + equality_columns,'') + COALESCE(',' + inequality_columns,''), 1, 1, '') + ')'             + COALESCE(' INCLUDE (' + include_columns + ')','')  + CHAR(13) + ' --?>' AS xml) AS PotentialDDL
FROM MissingIndexes
ORDER BY DatabaseName
,SUM(usecounts) OVER(PARTITION BY DatabaseName
,TableName) DESC
,SUM(usecounts) OVER(PARTITION BY TableName
,inequality_columns) DESC
,usecounts DESC

The output can be use to determine the databases and tables that would most benefit from additional indexes.  The query output also includes the percent improvement, use count, query plan, SQL text, and potential DDL for creating the index.


Missing Index Wrap Up

If you are looking for more on the MissingIndex element in the execution plan, check out Jonathan Kehayias’ (Blog | @SQLSarg) post Digging into the SQL Plan Cache: Finding Missing Indexes.  I’ve accommodated some of pieces of his query into the one I have above.  In his, he queries to the StmtSimple element instead of MissingIndexes, this is pure genius since it makes the @StatementText variable available.  Also, the method he uses to retrieve the columns for the missing index is much cleaner than was in my original query so that was adopted as well.

Since I mention it every now and then and think it’s been a couple posts since it was last included, the <?query – Some Information –?> syntax was something I picked up when looking at Adam Machanic’s (Blog | @AdamMachanic) Who Is Active stored procedure.  You better be using this and if not, start today.

Now for the icing on the wrap-up.  After the last two posts on the plan cache I hope you’ve started to see some of the benefits of letting SQL Server identify potential bottlenecks.  This information is readily available and sitting there for the taking.  In the next few posts, we’ll dive into the structure of the XML schema to help inspire some additional ways to leverage the plan cache.

Final note, I hadn’t checked before writing this, but it appears Grant Fritchey has a post on this as well.  Go ahead and check that one out as well – it’s More Refinements on the Missing Indexes Query.

Related posts:

  1. Can You Dig It? – The Plan Cache
  2. Can You Dig It? – Parallelism in the Plan Cache
  3. Searching for Plans


Posted by Anonymous on 14 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Can You Dig It? ??? Missing Indexes - StrateSQL         [sqlservercentral.com]        on Topsy.com

Posted by Jason Brimhall on 14 December 2010

Thanks for the list of resources.

Posted by Anonymous on 18 December 2010

Pingback from  Internet Marketing Guru-Internet Marketing Guru &#8211; Outsource Some of the Work For Maximum Efficiency | Internet Marketing Strategies

Posted by Brad Chapman on 20 December 2010

Thank you.  I ran your query against one of our production servers and was quite surprised at the list of opportunities.

Posted by Jason Strate on 21 December 2010

Awesome to hear.  This is one of those queries everyone should know about.

Posted by pauln on 3 January 2011

Unfortunately every server I ran it against died at the same place in either query: "CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp"

I get a "Incorrect syntax near '.'." error.  It doesn't like the plan_handle for some insane reason.

Any suggestions?


Posted by pauln on 3 January 2011

Please ignore.  I was 'in' production databases when running the queries, not master.

Posted by Jason Strate on 3 January 2011

It should run in any database.  You may need to check the compatibility mode of the database.  If it's not in SQL Server 2005+ I believe it will have this error.

Leave a Comment

Please register or log in to leave a comment.