SQL Server – Query Plan – Which Plans are targeting Specific Table

Background

One good thing about being a Development DBA, as opposed to Operational DBA, is that the task is not quite as regimented.

Different request comes in and one has to get out of the usual one track mind.

 

Background

So this morning one of our more caring developers says we are adding new columns and we need to make sure that we have corresponding indexes as we will be filtering on the new columns.
And, so I started thinking, I need more specifics, how will the columns be used, will we be filtering on composite columns, etc.

Code



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
go

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

set @SchemaName = 'Products';
set @TableName = 'CertificateHistory';


-- 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 
		  qp.query_plan 
		, 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)
;


 

 

Dedicated

This post is dedicated to my Ukraine’s brothers and sisters.

Christmas runs from Jan 7th to Jan 14th.

 

Sampled

Like J. Cole says, may your idols never become your rivals.

And, one of several ways to make sure of that is to always acknowledge and seek their blessings.

Sampled code from the following sources:

  1. Jonathan Kehayias – Finding Implicit Column Conversions in the Plan Cache
  2. Aaron Bertrand – Don’t just blindly create those “missing” indexes!
  3. Tertiary Weights Sort Performance
  4. Mikael Eriksson – How to filter XML execution plan data in a WHERE clause using TSQL
  5. Jason Strate – XQuery for the Non-Expert – Exist

One thought on “SQL Server – Query Plan – Which Plans are targeting Specific Table

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