Optimize for ad-hoc workloads

SQL Server 2008 introduced a new advanced option called “Optimize for ad-hoc workloads”.
According to Microsoft:

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

This is interesting for those of us who have software querying the database in on-the-fly queries instead of, for instance, stored procedures. Without optimize for ad hoc workloads turned on every query will be cached in the procedure cache, including those queries that generate an execution plan and only use it once. As you can imagine this can take a big bite out of your memory, memory you can better use for your buffer cache!

So just how big can that bite get? Well according to Greg Low’s recently published “Plan Caching and Recompilation in SQL Server 2012” it can get pretty big:

SQL Server Version Cache Pressure Limit
SQL Server 2012, SQL Server 2008 and SQL Server 2005 SP2 75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1 75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000 SQL Server 2000 4GB upper cap on the plan cache

Source: Plan Caching and Recompilation in SQL Server 2012, G. Low

This means that if you have a SQL Server 2012 server with 100GB of memory you can use

(0.75 x 4) + (0.10 x 60) + (0.05 x 36) = 3 + 6 + 1.8 = 10.8GB on your procedure cache!

With the optimize for ad-hoc workload option enabled, whenever a execution plan is generated for the first time it will write a stub (which is much smaller in size) in the procedure cache instead of the full execution plan. When the same query is run a second time, and the execution plan can be reused, then the full execution plan will be written to the procedure cache.

So let’s find out how it works and how it can help you to get some space back from the procedure cache!

For this demo we will make use of the AdventureWorks database.

First we want to make sure optimize for ad-hoc workloads is turned off:

-- If you don’t have advanced options enabled yet
EXECUTE sp_configure 'show advanced option', 1
RECONFIGURE

-- Turn optimize for ad hoc workloads off
EXECUTE sp_configure 'optimize for ad hoc workloads', 0

RECONFIGURE

Next step is to clear the procedure cache, make sure you do not use this on production!

DBCC FREEPROCCACHE

Let’s find out if there is anything in the procedure cache:

SELECT usecounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%HumanResources%' AND
text NOT LIKE '%dm_exec_cached_plans%'

It should return nothing since we cleared the cache.

So let’s select something to generate a execution plan:

SELECT * FROM HumanResources.Employee

Now let’s run our procedure cache query again:

SELECT usecounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%HumanResources%' AND
text NOT LIKE '%dm_exec_cached_plans%'

The result should look something like this:

adhoc_01

As you can see the query we just ran is cached on first usage.

Now, let’s enable the optimize for ad-hoc workload option:

EXECUTE sp_configure 'optimize for ad hoc workloads', 1

RECONFIGURE

And let’s clear the procedure cache again:

DBCC FREEPROCCACHE

If we run our SELECT query again and look into the procedure cache you should see something like this:

adhoc_02

As you can see our execution plan has a different cacheobjtype showing us our plan is a stub. You can also see the stub is a lot smaller then the full plan!

If we now run our SELECT query again you will notice the cacheobjtype change from Compiled Plan Stub to Compiled Plan and it grows in size:

adhoc_03

This is normal behavior, the execution plan will only be saved as the full plan when it is ran a second time (or more).

So if you have a lot of queries that generate a one-time execution plan you will save a lot of memory space (that can now be used for the buffer cache) by turning the optimize for ad-hoc workload option on!

Write a Reply or Comment

Your email address will not be published.