Plan Guide & Plan Cache – FAQ – 1


Here are answers gleaned from the web to questions and doubts that I had in my mind as I started to make sure that our Plan Guides are in fact being used.


Plan Cache

Monitor plan cache size and data cache size

  1. In general, as more queries are run, the amount of memory used for data page caching should increase along with the amount of memory used for plan cache.
  2. Version
    • In SQL Server 2005 prior to Service Pack 1, the maximum limit for plan cache could grow to be up to 80 percent of the total buffer pool before memory pressure would start forcing plans to be evicted. This can result in severe performance degradation for those queries that depend on good data caching behavior.
    • For any amount of memory greater than 4 GB, Service Pack 2 changes the size limit that plan cache can grow to before memory pressure is indicated.
  3. One of the easiest places to get a comparison of the pages used for plan cache and the pages used for data cache is the performance counters. Take a look at the following counters: SQL Server: Plan Cache\Cache Pages(_Total) and SQLServer: BufferManager\Database pages.


;with cteTotal
			sum( cntr_value)

	FROM sys.dm_os_performance_counters

			counter_name IN ( 'Cache Pages')

	AND (
			instance_name = '_Total'                                                                                                                          



	FROM sys.dm_os_performance_counters

	--BufferManager\Database pages
			     [object_name] = 'SQLServer:Buffer Manager'
			 and [counter_name] = 'Database pages'                                                                                                                  



		, tblSOSPC.counter_name

		, [numberofPages]
			= tblSOSPC.cntr_value

		, [sizeInMB]
			= tblSOSPC.cntr_value * 8192
				/ ( 1024 * 1024)

		, [sizeInGB]
			= cast
						tblSOSPC.cntr_value * 8192.00
							/ ( 1024 * 1024 * 1000)
					as decimal(10,2)

		, [%OfCache]
			= cast
					(tblSOSPC.cntr_value * 100.00 )
					as decimal(10, 2)

		, [%OfTotal]
			= cast
					(tblSOSPC.cntr_value * 100.00 )
					as decimal(10, 2)

FROM sys.dm_os_performance_counters tblSOSPC

cross apply cteTotal cteT

cross apply cteBufferPages cteBP

				tblSOSPC.[counter_name] IN ( 'Cache Pages')

AND	    (
			tblSOSPC.[instance_name] != '_Total'                                                                                                                          




  1. Percentile
    • SQL Plans
      • 90% of Cache Total
      • 24% of Overall Memory Total
    • Objects Plans ( Stored Procedures, Triggers, Views, and Triggers )
      • 7.5% of Overall Total
      • 1.2 % of Total
  2. Total
    • SQL Plans is 3.5 GB

Plan Cache

I have seen blog posts that suggest that upon creating a plan cache, one should clear the cache, and observe & track usage of the plan guide.

But, this does not seem to indicate Microsoft’s full intent.

Here is what the doc states:

Plan Guide Effect on the Plan Cache

  1. Creating a plan guide on a module removes the query plan for that module from the plan cache.
  2. Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value.
  3. Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

It appears that MSFT in fact goes the extra mile to indicate that as part of its codebase, a plan is created, and plan cache is walked through, and corresponding entries are pruned.


Level – SQL Instance

Optimize for Adhoc Queries?

Here is a worthy code originally written by Brett Hawton on the Idera’s web site ( Link )

James’ SQL Footprint ( Link )


	Brett Hawton has a query which can help you determine if you need to use 'optimize for ad hoc workloads'

		[Cache Store Type]
			= objtype

        , [Total Num Of Plans]
			= COUNT_BIG(*)

        , [Total Size In MB]
			= cast
					SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576
						as decimal(14, 2)

        , [All Plans - Ave Use Count]
			= AVG(usecounts)

        , [Size in MB of plans with a Use count = 1]
			= cast
					SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/ 1048576
						as decimal(14, 2)

        , [Number of of plans with a Use count = 1]
			= SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Size in MB of plans with a Use count = 1] DESC

DECLARE @AdHocSizeInMB decimal (14,2)
DECLARE @TotalSizeInMB decimal (14,2)

DECLARE @switchOn	   varchar(255)
DECLARE @switchOff	   varchar(255)

Declare @AdHocSizeInMBHighWatermark int
Declare @AdHocPercentileHighWatermark int
declare @AdHocPercentile float

set @AdHocSizeInMBHighWatermark = 200
set @AdHocPercentileHighWatermark = 25

set @switchOn = 'Switch on Optimize for ad hoc workloads as it will make a significant difference'
set @switchOff = 'Setting Optimize for ad hoc workloads will make little difference'

	  @AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes ELSE 0 END) as decimal(14,2))) / 1048576
        , @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
FROM sys.dm_exec_cached_plans 

if (@TotalSizeInMB > 0) 

	set @AdHocPercentile = cast(
									(@AdHocSizeInMB * 100 )
										/ @TotalSizeInMB
									as decimal(10, 2)


		[Current memory occupied by adhoc plans only used once (MB)]
			= @AdHocSizeInMB

        , [Total cache plan size (MB)]
			= @TotalSizeInMB

			, [% of total cache plan occupied by adhoc plans only used once]
				= CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))

		, [% Adhoc]
			= @AdHocPercentile

	200MB or > 25%
IF  (
					> @AdHocSizeInMBHighWatermark -- 200MB
		or ( 
					(@AdHocSizeInMB / @TotalSizeInMB) * 100
					> @AdHocPercentileHighWatermark  -- 25
				= @switchOn
				= @switchOff






  1. The code basically gets the size of the total plan cache and also the size used by Adhoc queries
  2. If the size used by Ad-hoc queries is over 200 MB in size or over 25% it recommends to turn on “optimize of ad hoc queries

Cache Type

Compiled Plan Stu / Compiled Plan Stub

  1. Introduced in Microsoft SQL Server 2008
    • Name
      • SQL Server v2008 – RTM
        • Compiled Plan Stu
      • SQL Server v2008 – SP1
        • Compiled Plan Stub
  2. The first time a query is compiled, it is added in the Plan Cache under this title
  3. How does it work?
    • When a query is first executed, the query is placed in the plan cache as a compiled plan stub
      • A small fraction of an an actual plan
    • The second time the query is received, the query is compiled and it is now recorded as a compiled plan

Auto Parameterization

Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization ( 2014-11-27 )


Optimization level can be either “Trivial” or “Full”.


So under simple parameterization mode, SQL Server will only parameterize queries with a trivial plan. This is good, because there is no risk of hurting performance as a result of reusing the wrong plan. If the same plan is good for all literal values, then it makes no sense to compile the query again and again for each value. This is what SQL Server does by default.

From Query Plan, Is Query Statement Parameterized?

The easiest way to find out if a query is parameterized is to use graphical XML plan. Just point to the operator and take a look at some of the seek predicate.

Let’s use this update example:

update t1 set c1 = 12, c2 = 23 where c1 = 12

The table t1 has c1 as clustered primary key (the reason why I also update c1 is related to customer’s problem which I will talk about later). So the plan has a clustered index seek predicate on c1.

If the plan is parameterized, you will see the seek predicate on c1 as “Scalar Operator (CONVERT_IMPLICIT(int,..)” or “Scalar Operator (@2)” as shown in figure 1. But if the plan is not parameterized, the hard coded value will show up in the seek predicate like “Scalar Operator (12)” as shown in figure 2 below.



Hard Coded



SQL Server Profiler

Andrew Fryer – SQL Server 2008 Plan Guides

There are event classes to see if it’s being used or missed e.g. you might have changed the schema for example and these  are cunningly named as

  • plan guide successful
  • plan guide unsuccessful




Single/Batch Query

If you try to indicate a plan guide on a multi-statement batch, you will get the error posted below.



set quoted_identifier off

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

set @querytext = "if exists(select 1 from MailingListEntryRecords where listID = 8 and receiverID='' and receiverCD='ST')
update MailingListEntryRecords set listUID='27a9f40fae',email='',lastupdateDt=getdate()  where listID = 8 and receiverID='' and receiverCD='ST'
insert MailingListEntryRecords(listID,receiverID,receiverCD,listUID,email) values(8,'','ST','27a9f40fae','')

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


Msg 10523, Level 16, State 3, Procedure sp_get_query_template, Line 345
Cannot generate query template because @querytext does not contain a valid single query.



Unfortunately, Microsoft’s documentation is not always clear as to when a particular functionality was added.

In those cases, we skipped that important information, as well.


Guide Plan

  1. Geek City: Clearing a Single Plan from Cache

Plan Guide

  1. Plan Guides



  1. Creating Plan guide using Query Plan handle


Guide Plan – SQL Profiler

  1. Andrew Fryer – SQL Server 2008 Plan Guides


Guide Plan – View Properties

  1. View Plan Guide Properties


Guide Plan – Troubleshooting

  1. Troubleshooting Plan Cache Issues


Optimize for ad-hoc workloads

  1. SQL Server Quickie #23 – Plan Cache Pollution
  2. Masayuki.Ozawa – The cache of the stub by optimize for ad hoc workloads
  3. James’ SQL Footprint – Ad hoc query optimization in SQL Server
  4. Jose Barreto – SQL Server 2008 Optimize for Ad Hoc Workloads
  5. Database Engine Instances (SQL Server) \ Configure Database Engine Instances \ Server Configuration Options (SQL Server) \ optimize for ad hoc workloads Server Configuration Option
  6. Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”


Parameterization – Simple, Forced

  1. Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization


Parameterization – Simple

  1. Jack Li – How Simple Parameterization works


Plan Caching

  1. Caching Mechanisms

Catalog Objects


  1. sys.syscacheobjects (Transact-SQL)
  2. Query Processing – Retrieving Information about Execution Plans – Main_Page – Monitoring & Tuning – Tuning Tools – SQL Server Query Optimizer


  1. sys.dm_exec_cached_plans
  2. sys.dm_exec_cached_plans



Leave a Reply

Please log in using one of these methods to post your comment: Logo

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