SQL Server – Finding Missing Indexes by Shredding Dynamic Management Views

Background

I continue to struggle with XPATH.  And, so like an Addict, I am overtly using it.

 

Finding Missing Indexes

Execution Plan

Sample

Here is a sample Execution Plan that contains the MissingIndexGroup Element.


<p1:MissingIndexGroup xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Impact="51.0332">
  <p1:MissingIndex Database="[HRDB]" Schema="[dbo]" Table="[ModuleTopic]">
    <p1:ColumnGroup Usage="EQUALITY">
      <p1:Column Name="[schoolID]" ColumnId="1" />
      <p1:Column Name="[active]" ColumnId="5" />
    </p1:ColumnGroup>
    <p1:ColumnGroup Usage="INCLUDE">
      <p1:Column Name="[moduleID]" ColumnId="2" />
      <p1:Column Name="[topicID]" ColumnId="3" />
      <p1:Column Name="[sortorder]" ColumnId="4" />
      <p1:Column Name="[weight_per_unit]" ColumnId="10" />
    </p1:ColumnGroup>
  </p1:MissingIndex>
</p1:MissingIndexGroup>

Explanation

  1. The MissingIndexGroup Node is the topmost element ( for our discussion )
    • Attributes
      • Contains the Impact of missing Index
  2. The MissingIndex Node ( underneath MissingIndexGroup )
    • Attributes
      • Database
      • Schema
      • Table
  3. ColumnGroup Node ( underneath MissingIndex )
    • Attributes
      • Usage
        • Indicates whether the Node is Equality or Include
        • Equality reflects the Key portion of the Index
        • Include reflects any included columns
  4. Column
    • Attributes
      • Usage
        • Column Name
        • Column ID

 

Code




declare @columnKey sysname
declare @columnIncluded sysname

set @columnKey = 'EQUALITY'
set @columnIncluded = 'INCLUDE'

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')       
SELECT 
		  [UseCountByImpact]
			=(
				dec.usecounts 
					* r.node.value('@Impact', 'decimal(32,2)')
		     )

		, dec.usecounts

		, dec.refcounts

        , Impact
            = r.node.value('@Impact', 'decimal(32, 2)')

		, dec.objtype

        , dec.cacheobjtype

		, [sqlText]
			= [des].[text]

        , deq.query_plan 

		, xmlFragment
            = cast((cast(r.node.query('.') as nvarchar(max)) ) as xml)

		, [Database]
            = cast(r.node.query('data(MissingIndex[1]/@Database)') as sysname)

		, [Schema]
            = cast(r.node.query('data(MissingIndex[1]/@Schema)') as sysname)

		, [Table]
            = cast(r.node.query('data(MissingIndex[1]/@Table)') as sysname)
 
 		, [ColumnKey]
            = cast(r.node.query('data(MissingIndex[1]/ColumnGroup[@Usage=sql:variable("@columnKey")]/Column/@Name)') as nvarchar(4000))

 		, [ColumnIncluded]
            = cast(r.node.query('data(MissingIndex[1]/ColumnGroup[@Usage=sql:variable("@columnIncluded")]/Column/@Name)') as nvarchar(4000))
            
FROM sys.dm_exec_cached_plans AS dec 

CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des 

CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq 

cross apply deq.query_plan.nodes(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') as r(node)

ORDER BY 
		(
			(
				dec.usecounts 
					* r.node.value('@Impact', 'decimal(30,3)')
			)
		) desc


go


 

Output:
missingIndexes

 

Explanation:

  1. We are querying the following Dynamic Management Views
    • sys.dm_exec_cached_plans
    • sys.dm_exec_sql_text
    • sys.dm_exec_query_plan
  2. We shredded the Query Plan into relations
    • The XPath we targeted is N’/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup’
    • This gives us each MissingIndexGroups
  3. Here are the correspondent entries
    • MissingIndex
      • Contains the entire Missing Index Group
      • To get it we access query(‘.’)
    • Database
      • Formulae = query(‘data(MissingIndex[1]/@Database)’) 
    • Schema
      • Formulae = query(‘data(MissingIndex[1]/@Schema)’)
    • Table
      • Formulae = query(‘data(MissingIndex[1]/@Table)’)
    • Column
      • The column element is one step further down the line
      • It is also a many to 1 relationship
      • And, contains different element based on the Usage
      • Available usages includes EQUALITY and INCLUDE
      • We are projecting two headers, columnKey and columnIncluded
        • ColumnKey
          • query(‘data(MissingIndex[1]/ColumnGroup[@Usage=sql:variable(“@columnKey”)]/Column/@Name)’)
        • columnIncluded
          • query(‘data(MissingIndex[1]/ColumnGroup[@Usage=sql:variable(“@columnIncluded”)]/Column/@Name)’)

Crediting

Crediting Matija Lah, Microsoft MVP.

In a blog post, he addressed an error message that I was tripping over:

Msg 2396, Level 16, State 1, Line 26
XQuery [query()]: Attribute may not appear outside of an element

More here

And, toadWorld which I used as a template.

And, then post original copy, chose to break down Key and Included Columns.  To do so sample code provided by Remus Rusanu here.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s