SQL Server – Plan Guide – Is plan guide for “Forced Parameterization” been applied?

Background

Once one creates a plan guide, it is habitual to be curious as to whether it is being used.

In this post, through a couple of Dynamic Management Views (DMVs), we will delve into tracking the efficacy of our Plan Guides.

 

Dynamic Management Views (DMV)

Here are the views that we query:

  1. sys.syscacheobjects
  2. sys.dm_exec_cached_plans

sys.syscacheobjects

Code


set quoted_identifier off;
go


declare @sql			varchar(800)
declare @sqlWildcard	varchar(800)

declare @database	    varchar(800)
declare @databaseWildcard	varchar(800)


if (@sql is not null)
begin

	set @sqlWildcard = '%' + @sql + '%'

end


if (@database is not null)
begin

	set @databaseWildcard = @database + '%'

end

select top 1000 

			  tblSCO.cacheobjtype

			, tblSCO.objtype

			, [databaseID]
				= tblSCO.[dbid]


			, [database]
				= case
						when tblSCO.[dbid] in (32567 ) then 'Resource'
						else db_name(tblSCO.[dbid])
				  end

			, [databaseIDExecuted]
				= case
						when tblSCO.[dbidexec] in (0) then tblSCO.[dbid]
						else db_name(tblSCO.[dbidexec])
				  end

			, [databaseExecuted]
				= case
						when tblSCO.[dbidexec] in (0) then db_name(tblSCO.[dbid])
						else db_name(tblSCO.[dbidexec])
				  end


			, [object]
				= case

					when (
							tblSCO.objtype in ('procedures', 'views', 'triggers' )
						 )
						then
							  object_schema_name
							  (	
								  tblSCO.[objid]
								, tblSCO.[dbid]
							  )
							  + '.'
							  + object_name
							  (	
								  tblSCO.[objid]
								, tblSCO.[dbid]
							  )

					else 'N/A'

					end

			,  tblSCO.[objid]

			, tblSCO.[uid]

			, [username]
				= case

					when ( tblSCO.[uid] = -2) then 'N/A'

					else user_name(	tblSCO.[uid] )

				  end

			, tblSCO.[sql]

			, tblSCO.refcounts

			, tblSCO.usecounts

			, tblSCO.[setopts]

			, tblSCO.pagesused

			, tblSCO.sqlbytes

from   sys.syscacheobjects tblSCO

where  (

			--Query does not target sys schema
			( tblSCO.[sql]  not like '%sys.%' )

	   )



and    tblSCO.objtype in
			(
				'Prepared'
			)

and  (
		   (@sql is null )
		or ( tblSCO.[sql]  like @sqlWildcard )
	)


and  (
		   (@database is null )
		or ( db_name(tblSCO.[dbid])  = @database )
	)

order by
		   tblSCO.usecounts desc

Output

20160518-0648PM

 

Explanation

  1. Filter
    • objtype = prepared
      • In essence we want to skip objtype = ‘Adhoc’
  2. Result
    • cacheobjtype
      • Compiled Plan
    • objtype
      • Prepared
    • Database ID & Database Name
    • ObjId
      • For Prepared Plans, the system assigns an ephemeral Object ID
    • uid
      • Plan Guides
        • When as a result of a Plan Guide, the user id is the userid of the person that created the Plan Guide
      • API Prepared Statement
        • This is the case where we invoked Prepared Statement through Application Code
          • Java JDBC ( Link )
          • Microsoft .Net – SQLCommand.Prepare ( Link )
        • The userid is the session’s user id
    •  setopts
      • SET option settings that affect a compiled plan. These settings are part of the cache key.
        Changes to values in this column indicate users have modified SET options. ( Link )
      • Keep in mind that when different set options are in effect, identical records with different setopts will be stored

 

sys.dm_exec_cached_plans

Code


set quoted_identifier off;
go

declare @sql			varchar(800)
declare @sqlWildcard	varchar(800)

declare @database	    varchar(800)
declare @databaseWildcard	varchar(800)


if (@sql is not null)
begin

	set @sqlWildcard = '%' + @sql + '%'

end

if (@database is not null)
begin


	set @databaseWildcard = @database + '%'

end


select top 100

			  tblSCO.cacheobjtype

			, tblSCO.objtype

			, [databaseID]
				= tblDEST.[dbid]


			, [database]
				= case
						when tblDEST.[dbid] in (32567 ) then 'Resource'
						else db_name(tblDEST.[dbid])
				  end


			, [object]
				= case

					when (
							tblSCO.objtype in ('procedures', 'views', 'triggers' )
						 )
						then
							  object_schema_name
							  (	
								  tblDEST.[objectid]
								, tblDEST.[dbid]
							  )
							  + '.'
							  + object_name
							  (	
								  tblDEST.[objectid]
								, tblDEST.[dbid]
							  )

					else 'N/A'

					end

			,  tblDEST.[objectid]

			, [sql] 
				= tblDEST.[text]


			, tblSCO.refcounts

			, tblSCO.usecounts

from   sys.dm_exec_cached_plans tblSCO

CROSS APPLY sys.dm_exec_query_plan(tblSCO.plan_handle) tblSEQP

CROSS APPLY sys.dm_exec_sql_text(tblSCO.[plan_handle]) AS tblDEST

where  (

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

	   )



and    tblSCO.objtype in
			(
				'Prepared'
			)

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

and  (
		   ( @database is null )
		or ( tblDEST.[text]  like @databaseWildcard )
	)


order by

	tblSCO.usecounts desc



Output

sys__dm_exec_cached_plans_20160518_0654PM

 

Explanation

  1. Filter
    • objtype = prepared
      • In essence we want to skip objtype = ‘Adhoc’
  2. Result
    • Need to do more in depth study on refcounts and usecounts

 

objtype = ‘Adhoc’

sys.syscacheobjects

Code


set quoted_identifier off;
go

declare @sql			varchar(800)
declare @sqlWildcard	varchar(800)

declare @database	    varchar(800)
declare @databaseWildcard	varchar(800)

set @sql = 'select email from Student'

if (@sql is not null)
begin

	set @sqlWildcard = '%' + @sql + '%'

end


if (@database is not null)
begin

	set @databaseWildcard = @database + '%'

end

select top 1000 

			  tblSCO.cacheobjtype

			, tblSCO.objtype

			, [databaseID]
				= tblSCO.[dbid]


			, [database]
				= case
						when tblSCO.[dbid] in (32567 ) then 'Resource'
						else db_name(tblSCO.[dbid])
				  end

			, [databaseIDExecuted]
				= case
						when tblSCO.[dbidexec] in (0) then tblSCO.[dbid]
						else db_name(tblSCO.[dbidexec])
				  end

			, [databaseExecuted]
				= case
						when tblSCO.[dbidexec] in (0) then db_name(tblSCO.[dbid])
						else db_name(tblSCO.[dbidexec])
				  end


			, [object]
				= case

					when (
							tblSCO.objtype in ('procedures', 'views', 'triggers' )
						 )
						then
							  object_schema_name
							  (	
								  tblSCO.[objid]
								, tblSCO.[dbid]
							  )
							  + '.'
							  + object_name
							  (	
								  tblSCO.[objid]
								, tblSCO.[dbid]
							  )

					else 'N/A'

					end

			,  tblSCO.[objid]

			, tblSCO.[uid]

			, [username]
				= case

					when ( tblSCO.[uid] = -2) then 'N/A'

					else user_name(	tblSCO.[uid] )

				  end

			, tblSCO.[sql]

			, tblSCO.refcounts

			, tblSCO.usecounts

			, tblSCO.[setopts]

			, tblSCO.pagesused

			, tblSCO.sqlbytes

from   sys.syscacheobjects tblSCO

where  (

			--Query does not target sys schema
			( tblSCO.[sql]  not like '%sys.%' )

	   )



and    tblSCO.objtype in
			(
				  'Adhoc'
				, 'Prepared'
			)

and  (
		   (@sql is null )
		or ( tblSCO.[sql]  like @sqlWildcard )
	)


and  (
		   (@database is null )
		or ( db_name(tblSCO.[dbid])  = @database )
	)

order by
		   tblSCO.usecounts desc


 

Output

preparedandahdoc - 20160518-0719PM

Explanation

  1. We can see that we have a lone entry for objtype=’Prepared’
  2. But, a lot more entries for Adhoc

Technical Summary

It is important that we narrow our filtering to “objtype =  ‘Prepared ‘”.

It is possible that “objtype =  ‘Adhoc‘ ” will continue to have multiple entries for otherwise identical queries.

 

Addendum

  1. Date Posted :- 2016-05-20
    Plan Cache – Adhoc Query Shell ( Link )

 

One thought on “SQL Server – Plan Guide – Is plan guide for “Forced Parameterization” been applied?

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