A single use execution plan is an execution plan that only gets used once, but occupies space in the plan cache.

Most of the time the knee jerk reaction I hear for resolving this is to set the optimize for adhoc workloads option. I try and do that as a last resort.

Most of the databases I tune perform OLTP workloads.  When I see a large plan cache it is usually related to non parameterized sql cluttering the cache.  When this is the case, there is another option besides enabling “optimize for adhoc workloads”.  This other option doesn’t just shrink the plan cache but it avoids the overhead of the compilations.

In this post I will show you how to find the memory footprint of your single use plans. I will then show you how to determine if this is non parameterized SQL causing a large footprint.   Last I will show you how we can fix the problem by using template plan guides (no code changes required).

To find the footprint of single use plans we can run the following query:

create table #single_use_plan_handles
(plan_handle varbinary(64),
size_in_bytes int
)
insert into #single_use_plan_handles
select plan_handle, size_in_bytes from sys.dm_exec_cached_plans where usecounts = 1
select count(1) number_of_queries, query_hash,
sum(convert(bigint,size_in_bytes)) size_in_cache_bytes,
max(size_in_bytes) size_of_each_bytes
from sys.dm_exec_query_stats a inner join #single_use_plan_handles b
on a.plan_handle = b.plan_handle group by query_hash having count(1) > 1 order by 3 desc
drop table #single_use_plan_handles

From running this query, it appears there are 3147 queries that are very likely the same query; however, they differ by the literal values used in them.  These 3147 queries are consuming 232 MB of our cache.  This is very likely non-parameterized SQL.

How did we determine this?  The trick is with using a query_hash.  If you are not familiar with a query_hash it is a little known but excellent feature that was introduced in sql 2008.  When you look in the plan cache, queries are referenced by a sql_handle. A sql_handle represents a query character for character. If there is any difference on one query from another (even if they are syntactically the same). A query_hash is generated during the query compilation process. Queries that differ only by their literal values will have the same query_hash.  By joining to sys.dm_exec_query_stats we were able to group the single use plans together in order to determine the impact of a particular query.

looking at the results, one of the largest uses of the plan cache is query_hash: 0x891BF706BDB9D8B8.  With the query_hash, we can now go inspect the plan cache to get the query text.

select top 10
text from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where a.query_hash = 0x891BF706BDB9D8B8

The output of this shows us the query with different AccountNumbers being passed into it:

With this information, we can fix this issue by creating a template plan guide.  Copy any one of the rows returned for the query text, and paste it into call to sp_get_query_template – you’ll need to escape any quotes when you do this.  You can see the example I provided below:

USE AdventureWorks2012
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'select c.customerid, c.accountnumber, s.orderdate, s.salesorderid from CustomerTest c left join sales.salesorderheader s on s.customerid = c.customerid WHERE c.AccountNumber = ''AW00000481'' order by s.orderdate',
@stmt OUTPUT,
@params OUTPUT
EXEC sp_create_plan_guide N'LookupCustomerOrderTemplate',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';

One very important note when creating plan guides: be sure you run the command in the appropriate database.  Plan guides are database specific, so if you run this command in master, it will not get picked up in our AdventureWorks2012 database.

Lets take a look at the impact of doing this now.  We will re-run our query to retrieve the sql text for the query hash:

select top 10
text from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where a.query_hash = 0x891BF706BDB9D8B8

There is now one row returned.  This row is now showing the query is parameterized:

Let’s take a look at our single use plans again:

create table #single_use_plan_handles
(plan_handle varbinary(64),
size_in_bytes int
)
insert into #single_use_plan_handles
select plan_handle, size_in_bytes from sys.dm_exec_cached_plans where usecounts = 1
select count(1) number_of_queries, query_hash,
sum(convert(bigint,size_in_bytes)) size_in_cache_bytes,
max(size_in_bytes) size_of_each_bytes
from sys.dm_exec_query_stats a inner join #single_use_plan_handles b
on a.plan_handle = b.plan_handle group by query_hash having count(1) > 1 order by 3 desc
drop table #single_use_plan_handles


We no longer see query_hash 0x891BF706BDB9D8B8 showing up in our single use plans.  This also means we freed up 230 MB of memory.

As a final check, I’ll show a chart that gives us a visual representation of the reduction of activity in the database due to the decrease in compilations.  I try not to pollute my blogs with SQLGrease marketing, but sometimes the charts help paint a picture regarding the impact of tuning.

You can see where we created the template plan guide at 02:30 AM (yes I blog when I can’t sleep).  In the bottom left corner, you can see we are only looking at activity for query_hash 0x891BF706BDB9D8B8.  There was a noticeable drop in activity when we installed the plan guide.  Most of our activity is CPU time and CPU_WAIT (my test server is under constant CPU pressure).  The reduction in activity is related to no longer consuming CPU for compilations.

If you have a sharp eye, you’ll notice CPU_WAIT – there is no such wait event as CPU_WAIT, this is actually a calculated value which is a measure of how long a query is sitting in a “runnable” state.

In real production systems where I’ve encountered this issue, it generally is a handful of queries that are consuming large portions of the cache.  Addressing the heaviest hitters can not just free up memory, but it can also improve performance by avoiding frequent query compilations.

SQLGrease and query timeout errors

How can SQLGrease help you get to the root cause of your query timeout errors?  Watch our demo video showing how SQLGrease can get to the root cause of very complex query timeouts.

See how you can get query level wait events without Query Store:

Free you say?  How can that be?