SQL Server – Query Plan – Which queries on a Specific Column

Background

As a quick follow up to our last post which looks for a specific table in the Query Plan, let us be a bit more succinct and look for a specific column.

 

Code

Look for all Column References


use [remindme]
go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go

DECLARE @SchemaName AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128) 

set @SchemaName = 'dbo';
set @TableName = 'ContactEvent';

-- Make sure the name passed is appropriately quoted
if (@SchemaName is not null)
begin

    IF (LEFT(@SchemaName, 1) <> '[' AND RIGHT(@SchemaName, 1) <> ']')
    begin
        SET @SchemaName = QUOTENAME(@SchemaName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@SchemaName, 1) <> '['
    begin
        SET @SchemaName = '['+@SchemaName;
    end
    IF RIGHT(@SchemaName, 1) <> ']'
    begin
        SET @SchemaName = @SchemaName + ']';
    end

end

-- Make sure the name passed is appropriately quoted
if (@TableName is not null)
begin

    IF (LEFT(@TableName, 1) <> '[' AND RIGHT(@TableName, 1) <> ']')
    begin
        SET @TableName = QUOTENAME(@TableName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@TableName, 1) <> '['
    begin
        SET @TableName = '['+@TableName;
    end
    IF RIGHT(@TableName, 1) <> ']'
    begin
        SET @TableName = @TableName + ']';
    end

end

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

          [queryPlan]
			= cast(cast(qp.query_plan as nvarchar(max)) as XML)
        , cp.usecounts
        , [queryObject]
            = quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))
        , [query]
            = st.text
        , DatabaseName
            = o.n.value('@Database', 'sysname')
        , SchemaName
            = o.n.value('@Schema', 'sysname')
        , TableName
            = o.n.value('@Table', 'sysname')
        , IndexName
            = o.n.value('@Index', 'sysname')

FROM sys.dm_exec_cached_plans AS cp 

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

cross apply qp.query_plan.nodes('//RelOp') as r(n)

cross apply r.n.nodes('*/Object') as o(n)

where qp.dbid = db_id()
and o.n.value('@Schema', 'sysname') = @SchemaName
and o.n.value('@Table', 'sysname') = @TableName

OPTION(MAXDOP 1, RECOMPILE)
;

 

Output:
Result

 

 

Look for Column Reference in specific Where Clauses

In this new example, we went in and looked at specific queries and examined the Query Plan.

Sample Query Plans

SeekPredicates –  SeekPredicate – Prefix – RangeColumns – ColumnReference

QueryPlan

 

Sample Query Plan 2

T2

Sample Query


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go

DECLARE @DatabaseName AS NVARCHAR(128)
DECLARE @SchemaName AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128) 

set @DatabaseName = quoteName(db_name())

set @SchemaName = 'dbo';
set @TableName = 'Contacts';
set @ColumnName = 'memberIdentifier'

set @SchemaName = 'dbo';
set @TableName = 'Contacts';
set @ColumnName = 'active'

-- Make sure the name passed is appropriately quoted
if (@SchemaName is not null)
begin

    IF (LEFT(@SchemaName, 1) <> '[' AND RIGHT(@SchemaName, 1) <> ']')
    begin
        SET @SchemaName = QUOTENAME(@SchemaName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@SchemaName, 1) <> '['
    begin
        SET @SchemaName = '['+@SchemaName;
    end
    IF RIGHT(@SchemaName, 1) <> ']'
    begin
        SET @SchemaName = @SchemaName + ']';
    end

end

-- Make sure the name passed is appropriately quoted
if (@TableName is not null)
begin

    IF (LEFT(@TableName, 1) <> '[' AND RIGHT(@TableName, 1) <> ']')
    begin
        SET @TableName = QUOTENAME(@TableName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@TableName, 1) <> '['
    begin
        SET @TableName = '['+@TableName;
    end
    IF RIGHT(@TableName, 1) <> ']'
    begin
        SET @TableName = @TableName + ']';
    end

end

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

SELECT

          query_plan
            = cast((cast(qp.query_plan as nvarchar(max))) as XML)

        , cp.usecounts

        , [queryObject]
            = quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))

        , [queryText]
            = max(st.text)

        , DatabaseName
            = max(r.node.value('@Database', 'sysname'))

        , SchemaName
            = max(r.node.value('@Schema', 'sysname'))

        , TableName
            = max(r.node.value('@Table', 'sysname'))

        , ColumnName
            = max(r.node.value('@Column', 'sysname'))

        , ColumnName2
            = max(r2.node2.value('@Column', 'sysname'))

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

         , xmlFragmentParent
            = cast(max(cast(node.query('../..') as nvarchar(max))) as xml)

         , xmlFragmentGrandParent
            = cast(max(cast(node.query('../../..') as nvarchar(max))) as xml)

FROM sys.dm_exec_cached_plans AS cp 

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

--outer apply qp.query_plan.nodes('//RelOp/IndexScan/SeekPredicates/SeekPredicateNew/SeekKeys/Prefix/RangeColumns/ColumnReference') as r(node)
outer apply qp.query_plan.nodes('//SeekPredicates//RangeColumns/ColumnReference') as r(node)

outer apply qp.query_plan.nodes('//Predicate//Identifier/ColumnReference') as r2(node2)

where qp.dbid = db_id()

and
	(

		(

				( r.node.value('@Schema', 'sysname') = @SchemaName )
			and ( r.node.value('@Table', 'sysname') = @TableName )
			and ( r.node.value('@Column', 'sysname') = @ColumnName )

		)		

		or
		(

				( r2.node2.value('@Schema', 'sysname') = @SchemaName )
			and ( r2.node2.value('@Table', 'sysname') = @TableName )
			and ( r2.node2.value('@Column', 'sysname') = @ColumnName )
		)		

	)

/*
and
(

    (

        --qp.query_plan.exist('//SeekPredicates/SeekPredicate/Prefix/RangeColumns/ColumnReference
        qp.query_plan.exist('//ColumnReference
                            [
                                      @Database = sql:variable("@DatabaseName")
                                  and @Schema = sql:variable("@SchemaName")
                                  and @Table = sql:variable("@TableName")
                                  and @Column = sql:variable("@ColumnName")
                            ]') = 1

    )

)

*/

group by
          cast(qp.query_plan as nvarchar(max))
        , cp.usecounts
        , quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))

OPTION(MAXDOP 1, RECOMPILE)
;

Explanation

  1. We used the Query.Plan exist to dig into the plan and latched on to filtering elements
    • The specific elements we are using are SeekPredicates and Predicate
    • Once we have the ColumnReference node we matched on the specific Schema\Table\Column we are looking for

 

Summary

I was really stuck for a couple of days trying to figure out how to use Wildcard in XPath.

And, so googled until I felt faint and got on last Trains, past when  the last Buses ran.

Finally Saturday morning, no Work day, found that I needed two forward slashes to get deep descendants.

Courtesy of MSFT – XPATH Examples 

 

Expression Refers
bookstore//title All <title> elements one or more levels deep in the <bookstore> element (arbitrary descendants). Note that this is different from the expression in the next row.
bookstore/*/title
All <title> elements that are grandchildren of <bookstore> elements.

 

References

  1. XPath Examples
    https://msdn.microsoft.com/en-us/library/ms256086(v=vs.110).aspx
  2. Brad Vander Zanden, Knoxville | The University of Tennessee, Knoxville
    http://web.eecs.utk.edu/~bvz/cs460/notes/xml/xpath.html

Dedicated

Dedicated to the Mikael Eriksson’s of the World!
MikaelEriksson

With stats like Top 0.15 and 6 million people reached, you are HERO!

 

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