THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

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.

Published Monday, July 27, 2009 12:27 PM by Jonathan Kehayias

Comments

 

Elisabeth Redei said:

Lies damned lies and statistics - Part II In previous post I listed situations where your statistics

August 10, 2009 8:44 AM
 

kendra little said:

Thanks for having posted this. I have used it quite a few times and it's been a big help!

December 10, 2010 5:08 PM
 

Fatherjack said:

Jonathan,

I have taken your script as a starting point and have altered it to review the plan cache for updates to indexes. It returns usable data but I have to take this back to the dm_db_index_usage_stats dmv to see if the indexes that are being updated are actually not being used. This dmv gives that data in the user_updates column anyway so using the plan cache seems a little redundant, other than as a way to see which queries are actually causing the index updates/inserts. Other than some time exercising my mind do you think have I wasted my time or is it worth continuing?

January 12, 2011 9:57 AM
 

Jonathan Kehayias said:

I was going to use your first name FatherJack, but it was weird replying to a comment by starting with Jonathan when your comment started with it as well.

What exactly do you want to know here?  Are you looking for indexes that are unused or what queries are causing insert/updates to occur?  If you only care about the unused index part, then I agree, there is no point hitting the plan cache for that information.  

January 12, 2011 10:06 AM
 

Fatherjack said:

Um, yeah it is a bit odd! SQLSarg it is :)

I start on this from the post at http://www.jasonstrate.com/2010/12/can-you-dig-it-missing-indexes/ and had the thought that I could find index update operations and then potentially remove indexes that are slowing down transactions if it looked justified. I didn't fully think it through to the point of thinking it would be useless info but wondered if it was possible and what it might show me. In the way that using the plan cache shows the queries that are missing the indexes is better than just the dmv info that shows the columns to index but you don't know what query you are making better.

So, I set about re-working the SQL on that/this page. I have it working but it obviously has no data about how/if the index is being used. Its interesting but not useful. I can see how often indexes are being updated and by how many different queries but I cant use the info to change/improve things. To do that I need to know how useful the index is, which is where the dmv comes in. And that's where the redundancy of using the plan cache for this info became obvious - there was no need to use it. Unless I was missing something.

I'm guessing that as you haven't said OMG that's AWESOME then it was all an exercise in coding (my XML is shockingly poor) and proved the point that there wasn't any advantage to using this.

Thanks.

J

January 12, 2011 10:23 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement