Plan Guide & Plan Cache – FAQ – 1

Background

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

https://msdn.microsoft.com/en-us/library/cc293620.aspx

  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.

Code



;with cteTotal
(
	[sum]
)
as
(
	SELECT 
			sum( cntr_value)

	FROM sys.dm_os_performance_counters

	WHERE  
		(
			counter_name IN ( 'Cache Pages')
	    )

	AND (
			instance_name = '_Total'                                                                                                                          
		)

)

,cteBufferPages
(
	[value]
)
as
(
	SELECT 
			[cntr_value]

	FROM sys.dm_os_performance_counters

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


)

SELECT 
		  tblSOSPC.instance_name

		, 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 )
						/cteT.[sum]
					as decimal(10, 2)
				)

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

FROM sys.dm_os_performance_counters tblSOSPC

cross apply cteTotal cteT

cross apply cteBufferPages cteBP

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

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




Output

cachedPages

Explanation

  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
https://msdn.microsoft.com/en-us/library/ms190417.aspx

  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.

Settings

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 )

Code



/*
	Brett Hawton has a query which can help you determine if you need to use 'optimize for ad hoc workloads'
*/
SET NOCOUNT ON;

SELECT 
		[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'

SELECT 
	  @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) 
begin

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

end

SELECT 
		[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  (
		   ( 
				@AdHocSizeInMB 
					> @AdHocSizeInMBHighWatermark -- 200MB
		   )
		or ( 
				( 
					(@AdHocSizeInMB / @TotalSizeInMB) * 100
				) 
					> @AdHocPercentileHighWatermark  -- 25
			)
	)
begin
		select
			[Recommendation]
				= @switchOn
					
end
ELSE
begin
        select
			[Recommendation]
				= @switchOff
end
GO

 

Output

OptimizeForAdHocQueries-20160520-0500PM

 

Explanation

  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 )
http://www.madeiradata.com/parameterization-part-6-simple-vs-forced/

 

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

OptimizationLevel-Trivial

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.

Parameterized

IsQueryStatementParameterized-Parameterized

Hard Coded

IsQueryStatementParameterized-Hardcoded

 

SQL Server Profiler

Andrew Fryer – SQL Server 2008 Plan Guides
https://blogs.technet.microsoft.com/andrew/2008/11/14/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

Performance-PlanguideSuccessful

 

 

Single/Batch Query

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

 

SQL


set quoted_identifier off
go

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='daniel@gmail.com' and receiverCD='ST')
update MailingListEntryRecords set listUID='27a9f40fae',email='daniel@gmail.com',lastupdateDt=getdate()  where listID = 8 and receiverID='daniel@gmail.com' and receiverCD='ST'
else
insert MailingListEntryRecords(listID,receiverID,receiverCD,listUID,email) values(8,'daniel@gmail.com','ST','27a9f40fae','daniel@gmail.com')
"

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

Error


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.

 

Summary

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.

References

Guide Plan

  1. Geek City: Clearing a Single Plan from Cache
    http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx


Plan Guide

  1. Plan Guides
    https://msdn.microsoft.com/en-us/library/ms190417.aspx

 

Creation

  1. Creating Plan guide using Query Plan handle
    https://denaliblogs.wordpress.com/category/plan-guides/

 

Guide Plan – SQL Profiler

  1. Andrew Fryer – SQL Server 2008 Plan Guides
    https://blogs.technet.microsoft.com/andrew/2008/11/14/sql-server-2008-plan-guides/

 

Guide Plan – View Properties

  1. View Plan Guide Properties
    https://msdn.microsoft.com/en-us/library/bb895162.aspx

 

Guide Plan – Troubleshooting

  1. Troubleshooting Plan Cache Issues
    https://msdn.microsoft.com/en-us/library/cc293620.aspx

 

Optimize for ad-hoc workloads

  1. SQL Server Quickie #23 – Plan Cache Pollution
    http://www.sqlpassion.at/archive/2015/07/13/how-optimize-for-adhoc-workload-impacts-your-plan-cache/
  2. Masayuki.Ozawa – The cache of the stub by optimize for ad hoc workloads
    http://blog.engineer-memo.com/2011/02/14/optimize-for-ad-hoc-workloads-%E3%81%AB%E3%82%88%E3%82%8B%E3%82%B9%E3%82%BF%E3%83%96%E3%81%AE%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3%83%A5%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/
  3. James’ SQL Footprint – Ad hoc query optimization in SQL Server
    http://jamessql.blogspot.com/2012/05/ad-hoc-query-optimization-in-sql-server.html
  4. Jose Barreto – SQL Server 2008 Optimize for Ad Hoc Workloads
    https://blogs.technet.microsoft.com/josebda/2009/03/19/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
    https://msdn.microsoft.com/en-us/library/cc645587(v=sql.110).aspx
  6. Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”
    http://dba.stackexchange.com/questions/65237/query-plan-cache-bloated-by-ad-hoc-queries-even-with-optimize-for-ad-hoc-workl

 

Parameterization – Simple, Forced

  1. Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization
    http://www.madeiradata.com/parameterization-part-6-simple-vs-forced/

 

Parameterization – Simple

  1. Jack Li – How Simple Parameterization works
    https://blogs.msdn.microsoft.com/psssql/2013/12/04/how-simple-parameterization-works/

 

Plan Caching

  1. Caching Mechanisms
    https://msdn.microsoft.com/en-us/library/cc293623.aspx

Catalog Objects

sys.syscachedobjects

  1. sys.syscacheobjects (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/ms187815.aspx
  2. Query Processing – Retrieving Information about Execution Plans – Main_Page – Monitoring & Tuning – Tuning Tools – SQL Server Query Optimizer
    https://www.toadworld.com/platforms/sql-server/w/wiki/9849.query-processing-retrieving-information-about-execution-plans

sys.dm_exec_cached_plans

  1. sys.dm_exec_cached_plans
    https://msdn.microsoft.com/en-us/library/ms187404.aspx
  2. sys.dm_exec_cached_plans
    http://www.rmjcs.com/SQLServer/DMVs/sysdm_exec_cached_plans/tabid/194/Default.aspx

 

 

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