Digging into the SQL Plan Cache: Finding Missing Indexes

This is one of those topics I planned to blog about a long time ago, but never actually got around to it, and consequently I keep having to hit search engines to find the code I provided in a forums post when I need to use it again.  A while back, I helped fellow SQL enthusiast Jeremiah Peschka (@peschkaj) out via twitter with finding plans that included missing index information in them.  Then a bit later I used the same code on a Forums Post and then again with Michelle Ufford (@sqlfool) who actually took the code and ran with it to create a very nice stored procedure that runs to capture plans with missing indexes into a table where they can be investigated at length later.  As you probably already know, missing index information is stored in the DMV’s in SQL Server 2005 and 2008, but one thing that is missing from the DMV’s is the SQL Statement that triggered the missing index information to be added.  This is available in the plan cache, if the query plan still exists in the cache.

To be perfectly honest, the quick way to get to the root of the problem is to just query the procedure cache and then click on the XML in the results window which will open up the execution plan graphically, and if you have SSMS 2008, the missing index information will show up in green text as a part of the window, and a simple right click will produce the script required to create the missing index.  Anybody can do that, so I don’t see the fun in playing there.  What is really nice about the XML plans is that they are 1) schema bound to a published schema and 2) hold a vast amount of information that can give your deep insight into how your queries are actually working.

The XML schema for the show plans are published on the following location:

http://schemas.microsoft.com/sqlserver/2004/07/showplan/

This allows the use of XML NAMESPACES for quick querying of information out of the XML document using XQUERY.  One of the question asked in the comments on Michelle’s blog post was how to get the information in the same format as the missing index DMV’s.  I thought this would be interesting to look at so I started playing with the XML document and looking at the schema to figure out how to go about doing it.  What I came up with was the following code:

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT query_plan,
       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,
       (   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
           FROM n.nodes('//ColumnGroup') AS t(cg)
           CROSS APPLY cg.nodes('Column') AS r(c)
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
           FOR  XML PATH('')
       ) AS equality_columns,
        (  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
           FROM n.nodes('//ColumnGroup') AS t(cg)
           CROSS APPLY cg.nodes('Column') AS r(c)
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
           FOR  XML PATH('')
       ) AS inequality_columns,
       (   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
           FROM n.nodes('//ColumnGroup') AS t(cg)
           CROSS APPLY cg.nodes('Column') AS r(c)
           WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
           FOR  XML PATH('')
       ) AS include_columns
INTO #MissingIndexInfo
FROM
(
   SELECT query_plan
   FROM (
           SELECT DISTINCT plan_handle
           FROM sys.dm_exec_query_stats WITH(NOLOCK)
         ) AS qs
       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
   WHERE tp.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1;

-- Trim trailing comma from lists
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),
   inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),
   include_columns = LEFT(include_columns,LEN(include_columns)-1);

SELECT *
FROM #MissingIndexInfo;

DROP TABLE #MissingIndexInfo;

This only begins to scratch the surface of the information available in the XML Showplan.  I’ll be doing a few more posts that show information available based on code I’ve used to answer questions on the forums out of the plan cache.

8 thoughts on “Digging into the SQL Plan Cache: Finding Missing Indexes

  1. In the above query the OBJECT_ID is returned. Is there a specific reason not to immediately convert OBJECT_ID into OBJECT NAME within same query ? Is there an issue with getting either table name against which index is missing/suggested or name of calling SP? when I try NEXT DAY converting OBJECT_IDs in the resultset of this query to OBJECT_NAMEs i am getting all nulls.

    Thank you.

    1. Hey Vlad,

      Did you read through the code and what it is doing? The name of the object is being output by the next column in the result set already:

      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,

  2. I hate to be over a year behind in ready these, but finding little nuggets like this are great, even when late. Thanks for sharing a very useful query.

  3. Great query, I found an issue where if there are multiple statements in a stored procedure, that the wrong value is being returned.

    The example is a stored procedure with 3 select statements in it, each with a where clause, no index on the where clause. Procedure plan indicates 3 missing indexes.

    Query correctly identifies the plan in the cache as having missing indexes, but impact and columns are wrong.

    n.value(‘(//MissingIndexGroup/@Impact)[1]’, ‘FLOAT’) AS impact,

    I changed to be:

    n.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’, ‘FLOAT’) as Impact,

    Propagate that change to you equality, inequality and include columns for proper results.

    This was on a sql2012 system.

  4. Hi Jonathan, I love your work and code and thank you for all your efforts, I have add the code below right after the line
    “AS statement,”
    — I added the code below so I can specifically call out the Database Name, Schema Name and Table Name
    n.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’, ‘FLOAT’) as Impact, — I moved Bob Horkey’s idea here
    n.value(‘(//MissingIndex/@Database)[1]’, ‘VARCHAR(128)’) AS DatabaseName,
    n.value(‘(//MissingIndex/@Schema)[1]’, ‘VARCHAR(128)’) AS SchemaName,
    n.value(‘(//MissingIndex/@Table)[1]’, ‘VARCHAR(128)’) AS TableName,
    — right next to the equality_Column, inequality_column and include_columns so I can create indexes in a more organized fashion
    — In addition, I also applied an Order by clause to the Select #MissingIndexInfo Statement
    SELECT *
    FROM #MissingIndexInfo ORDER BY DatabaseName,SchemaName,TableName, Impact;

  5. There is a case that return wron result.
    A query plan with two missingindex on the same table, the first with inequality on a column, and include some other, the second with equality on a different column.
    The result is a mix of the two missingindex columns

    here an extract of involved qp

    ………..

    Here the result of query

    statement equality_columns inequality_columns include_columns
    [MC].[dbo].[A_Device] [id] [Codice] [Cancellato], [id], [Tipo]
    [MC].[dbo].[A_Device] [id] [Codice] [Cancellato], [id], [Tipo]

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.