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.
Monitor plan cache size and data cache size
- 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.
- 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.
- 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.
counter_name IN ( 'Cache Pages')
instance_name = '_Total'
[object_name] = 'SQLServer:Buffer Manager'
and [counter_name] = 'Database pages'
= tblSOSPC.cntr_value * 8192
/ ( 1024 * 1024)
tblSOSPC.cntr_value * 8192.00
/ ( 1024 * 1024 * 1000)
(tblSOSPC.cntr_value * 100.00 )
as decimal(10, 2)
(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')
tblSOSPC.[instance_name] != '_Total'
- 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
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
- Creating a plan guide on a module removes the query plan for that module from the plan cache.
- 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.
- 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'
SET NOCOUNT ON;
[Cache Store Type]
, [Total Num Of Plans]
, [Total Size In MB]
SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576
as decimal(14, 2)
, [All Plans - Ave Use Count]
, [Size in MB of plans with a Use count = 1]
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)
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
if (@TotalSizeInMB > 0)
set @AdHocPercentile = cast(
(@AdHocSizeInMB * 100 )
as decimal(10, 2)
[Current memory occupied by adhoc plans only used once (MB)]
, [Total cache plan size (MB)]
, [% of total cache plan occupied by adhoc plans only used once]
= CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))
, [% Adhoc]
200MB or > 25%
> @AdHocSizeInMBHighWatermark -- 200MB
(@AdHocSizeInMB / @TotalSizeInMB) * 100
> @AdHocPercentileHighWatermark -- 25
- The code basically gets the size of the total plan cache and also the size used by Adhoc queries
- 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“
Compiled Plan Stu / Compiled Plan Stub
- Introduced in Microsoft SQL Server 2008
- SQL Server v2008 – RTM
- SQL Server v2008 – SP1
- The first time a query is compiled, it is added in the Plan Cache under this title
- 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
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.
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
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 receiverIDemail@example.com' and receiverCD='ST')
update MailingListEntryRecords set listUID='27a9f40fae',firstname.lastname@example.org',lastupdateDt=getdate() where listID = 8 and receiverIDemail@example.com' and receiverCD='ST'
insert MailingListEntryRecords(listID,receiverID,receiverCD,listUID,email) values(8,'firstname.lastname@example.org','ST','27a9f40fae','email@example.com')
@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.
- Geek City: Clearing a Single Plan from Cache
- Plan Guides
- Creating Plan guide using Query Plan handle
Guide Plan – SQL Profiler
- Andrew Fryer – SQL Server 2008 Plan Guides
Guide Plan – View Properties
- View Plan Guide Properties
Guide Plan – Troubleshooting
- Troubleshooting Plan Cache Issues
Optimize for ad-hoc workloads
- SQL Server Quickie #23 – Plan Cache Pollution
- Masayuki.Ozawa – The cache of the stub by optimize for ad hoc workloads
- James’ SQL Footprint – Ad hoc query optimization in SQL Server
- Jose Barreto – SQL Server 2008 Optimize for Ad Hoc Workloads
- Database Engine Instances (SQL Server) \ Configure Database Engine Instances \ Server Configuration Options (SQL Server) \ optimize for ad hoc workloads Server Configuration Option
- Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”
Parameterization – Simple, Forced
- Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization
Parameterization – Simple
- Jack Li – How Simple Parameterization works
- Caching Mechanisms
- sys.syscacheobjects (Transact-SQL)
- Query Processing – Retrieving Information about Execution Plans – Main_Page – Monitoring & Tuning – Tuning Tools – SQL Server Query Optimizer