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



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


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'
 
-- 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
	top 1
 
          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]
            = (st.text)
 
        , DatabaseName
            = (r.node.value('@Database', 'sysname'))
 
        , SchemaName
            = (r.node.value('@Schema', 'sysname'))
 
        , TableName
            = (r.node.value('@Table', 'sysname'))
 
        , ColumnName
            = (r.node.value('@Column', 'sysname'))
 
        , ColumnName2
            = (r2.node2.value('@Column', 'sysname'))
 
         , xmlFragment
            = cast((cast(node.query('.') as nvarchar(max)) ) as xml)
 
         , xmlFragmentParent
            = cast((cast(node.query('../..') as nvarchar(max))) as xml)
 
         , xmlFragmentGrandParent
            = cast((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()
where 1=1
 
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 )
        )       
 
    )
 


/* 
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
    Link
  2. Brad Vander Zanden, Knoxville | The University of Tennessee, Knoxville
    Link

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s