Plan Cache – Adhoc Shell Query

Background

In a recent post, we touched on the our attempts at finding at out whether our Plan Guides are working.

BTW, our SQL Engine is v2005.

When we queried sys.syscacheobjects we saw a lone parameterized query logged as a prepared statement.

But, also saw numerous Ad-hoc queries.

So what gives.

 

Research

Googling found us this gem in the book referenced below:

Microsoft SQL Server 2012 Internals
By Kalen Delaney, Craig Freeman
Troubleshooting Plan Cache Issues
https://msdn.microsoft.com/en-us/library/cc293620.aspx
Consider the same procedure being called dozens or hundreds of times. Remember that SQL Server 2005 will cache the adhoc shell query that includes the actual parameter for each individual call to the procedure, even though there may be only one cached plan for the procedure itself.

Instrumentation

Let us write a query and see what the DMVs says

Code



/*
	Credits:

		a) @ParameterizedPlanHandle
		   SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'
		   Parameterization and filtered indexes (part 1)
		   http://sqlblog.com/blogs/hugo_kornelis/archive/2014/01/19/parameterization-and-filtered-indexes-part-1.aspx

*/
set quoted_identifier on;

declare @sql            varchar(800)
declare @sqlFull        nvarchar(max)
declare @sqlParmAndText nvarchar(max)
declare @sqlWildcard    varchar(800)
 
declare @database       varchar(800)
declare @databaseWildcard   varchar(800)

DECLARE @templatetext           nvarchar(max);
DECLARE @parameters             nvarchar(max);

DECLARE @maxNumberofRecords	    int
 
set @sqlFull = 'select email from Student where studentID=''daniel@gmail.com'''
set @sql = 'select email from Student'

set @maxNumberofRecords = 10

 
if (@sql is not null)
begin
 
    set @sqlWildcard = @sql + '%'
 
end
 
 
if (@database is not null)
begin
 
    set @databaseWildcard = @database + '%'
 
end
 


/*
	Get Query Template
*/
if (@sqlFull is not null)
begin

	exec sp_get_query_template 
					  @querytext = @sqlFull
					, @templatetext = @templatetext output
					, @parameters = @parameters     output


	set @sqlParmAndText = '(' + @parameters + ')' + @templatetext

	select 
		  [@parameters] = @parameters
		, [@templatetext] = @templatetext
		, [@sqlParmAndText] = @sqlParmAndText


end

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

	  [refcounts]
	, [usecounts]
	, [plan_handle]
	, [size_in_bytes]
	, [cacheobjtype]
	, [objtype]
	, [text]
	, [queryPlanAsXML]
)
as
(

	select 
			  tblDECP.[refcounts]
			, tblDECP.[usecounts]
			, tblDECP.[plan_handle]
			, tblDECP.[size_in_bytes]
			, tblDECP.[cacheobjtype]
			, tblDECP.[objtype]
			, tblDest.[text]
			, [queryPlanAsXML]
				= tblDEQP.query_plan

	from   sys.dm_exec_cached_plans tblDECP

	CROSS APPLY sys.dm_exec_query_plan(tblDECP.plan_handle) tblDEQP

	CROSS APPLY sys.dm_exec_sql_text(tblDECP.plan_handle) AS tblDEST

	where  (
 
				--Query does not target sys schema
				( tblDEST.[text]  not like '%sys.%' )
 
		   )


	and  (

			  (
						( objType = 'Prepared' )

					and (

							    ( tblDEST.[text]  = @sqlParmAndText )
							or  ( @sqlParmAndText is null )
						)

			  )


		)

	union all 

	select top (@maxNumberofRecords)
					  tblDECP.[refcounts]
					, tblDECP.[usecounts]
					, tblDECP.[plan_handle]
					, tblDECP.[size_in_bytes]
					, tblDECP.[cacheobjtype]
					, tblDECP.[objtype]
					, tblDest.[text]
					, [queryPlanAsXML]
						= tblDEQP.query_plan

	from   sys.dm_exec_cached_plans tblDECP

	CROSS APPLY sys.dm_exec_query_plan(tblDECP.plan_handle) tblDEQP

	CROSS APPLY sys.dm_exec_sql_text(tblDECP.plan_handle) AS tblDEST

	where  (
 
				--Query does not target sys schema
				( tblDEST.[text]  not like '%sys.%' )
 
		   )


	and  (

			(

					( objType = 'Adhoc' )

				and (

							( tblDEST.[text]  like @sqlWildcard )
						or ( @sqlWildcard is null )

					)

			)


		)

)
select 
		  cteXML.[refcounts]
		, cteXML.[usecounts]
		, cteXML.[plan_handle]
		, [ParameterizedPlanHandle]
			= stmt.value
				(
					  '(@ParameterizedPlanHandle)'
					, 'varchar(64)'
				) 
		, cteXML.[size_in_bytes]
		, cteXML.[cacheobjtype]
		, cteXML.[objtype]
		, cteXML.[text]


		, [XMLDocument]
			= cteXML.[queryPlanAsXML].query('.')

		, [XMLFragment-QueryPlan]
			= cteXML.[queryPlanAsXML].query('//QueryPlan')

		, [QueryPlan-StmtSimple-Exist]
			= cteXML.[queryPlanAsXML].exist('//StmtSimple')

		, [QueryPlan-QueryPlan-Exist]
			= cteXML.[queryPlanAsXML].exist('//QueryPlan')

from   cte cteXML

CROSS APPLY cteXML.[queryPlanAsXML].nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
                                             AS batch(stmt)

order by
             cteXML.[usecounts] desc
		   , cteXML.[size_in_bytes] desc

 

Output

AdhocShellQuery

 

Explanation

  1. The prepared plan
    • Is referenced and used quite a bit ( refcount and usecount )
    • The queries text has both the parameter and query text
    • In its query plan, it has the the actual query plan
  2. The Ad-hoc plans
    • Referenced and used minimally ( refcount and usecount )
    • The size_in_bytes is smaller
    • The text has the full query text

 

Show Plan

If one digs into the Query Plans XML, one will see and notice the following

Prepared

QueryPlan-Prepared

AdHoc

QueryPlan-Adhoc

 

Explanation

  1. Prepared Statement
    • Actual Query Plan with Query’s Entity, Operator and Cost
  2. Adhoc
    • Actual Query’s Text
    • Just Query Operator

 

Query Plan

Prepared

Here are some sample Query Plans for Prepared Statements.

Query 1

QueryPlan-1-StudentID

 

Query 2

QueryPlan-2-StudentCourse

 

Explanation

  1. ParameterList
    • You want to pay attention to ParameterList section
    • It lists each argument referenced as ColumnReference elements
    • The arguments are positional
    • And, they contain the actual value that the Query Plan was compiled with

 

References

  1. Combining SQL and TEMPLATE Plan Guides
    Kalen Delaney | SQL Server Pro
    August 21, 2007
    Link

One thought on “Plan Cache – Adhoc Shell Query

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