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.