on April 12, 2017
A while back, I got a question about enabling SQL Server’s ‘Optimize for Adhoc Workloads’ setting. The gist of the question was whether or not enabling this setting might free up extra memory on their SQL Server instance.
I don’t get excited about the ‘Optimize for Adhoc Workloads’ setting anymore
Once upon a time, I was really excited about getting this configuration item in SQL Server 2008. Early versions of SQL Server 2005 weren’t all that great at managing the size of the execution plan cache: it could really balloon up and eat away at the buffer pool. But the SQL Server team did a good job at tuning those algorithms in later service packs for 2005 and future versions, and it became much less of an issue.
Personally, I’ve never had a case where enabling ‘Optimize for Adhoc Workloads’ improved performance in a way that I could measure. It may save you a small amount of memory, it may not.
I don’t mean this as a big insult. Trying to save a penny every time you go to the grocery store could add up, if you grocery shop very frequently. But hopefully that’s not one of your major revenue sources over time.
What does ‘Optimize for Adhoc’ do?
Enabling this setting means that the first time a query runs, SQL Server will just store a small “stub” for the query’s execution plan. The second time that query runs, it will store the full plan in cache. So it essentially saves you some (not all) of the memory for query plans that aren’t re-used.
How do I know if it’s on?
This query looks at your current settings:
SELECT * FROM sys.configurations
WHERE name = N'optimize for ad hoc workloads';
GO
If the ‘value_in_use’ column is set to 1, the setting is enabled on your instance. If 0, it is not enabled.
How much memory would this save me?
Since the benefit here is to not store full execution plans that are only used once, check how many single-use execution plans are in memory after your instance has been up and running for a while to estimate the memory it might save you.
Here’s a simple query to estimate this from sys.dm_exec_cached_plans:
/* Size of single use adhoc plans in execution plan cache */
SELECT
objtype,
cacheobjtype,
SUM(size_in_bytes)/1024./1024. as [MB]
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
and objtype = 'Adhoc'
GROUP BY objtype, cacheobjtype;
GO
If you see a high number here, I wouldn’t just enable ‘Optimize for Adhoc’ and call it a day. Instead, I want to see some samples of what those single use queries are, because…
There may be a better fix!
My biggest issue with ‘Optimize for Adhoc’ is that it might cause you to sweep some problems under the rug that wouldn’t be too tricky to resolve in a better way.
If you have a lot of single use plans, look at what the queries actually are that are generating all these plans! Many times I have found them to come from a single application or job where someone used dynamic SQL and just never thought about parameterizing the code it generated. Parameterizing the code can be a better fix long term and allow plan reuse. (As with anything, test, because plan reuse is not always faster.)
Here’s a simple query to explore what those single-use plans are, by joining to sys.dm_exec_sql_text:
SELECT TOP 100
cacheobjtype,
[text] as [sql text],
size_in_bytes/1024. as [KB]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
usecounts = 1
and objtype = 'Adhoc'
ORDER BY [KB] DESC;
GO
Note: if you’ve got a large plan cache, this query might take a while. I’ve got a TOP 100 and an order by in the query just to keep it from returning more rows to SSMS than you can practically review. Consider removing the order by to see a quick sample (but similar queries may not be near one another, of course).
How many of those stubs do I have in cache?
If you have this setting enabled, you can see the plan stubs in memory. Here’s a query that puts those plan stubs into the context of your whole execution plan cache:
SELECT
objtype,
cacheobjtype,
SUM(CASE usecounts WHEN 1 THEN
1
ELSE 0 END ) AS [Count: Single Use Plans],
SUM(CASE usecounts WHEN 1 THEN
size_in_bytes
ELSE 0 END )/1024./1024. AS [MB: Single Use Plans],
COUNT_BIG(*) as [Count: All Plans],
SUM(size_in_bytes)/1024./1024. AS [MB - All Plans]
FROM sys.dm_exec_cached_plans
GROUP BY objtype, cacheobjtype;
GO
Can ‘Optimize for Adhoc Workloads’ slow down my server?
I had one case where enabling ‘Optimize for Adhoc Workloads’ caused a very measurable performance degradation– not an outage, but a lot of slowness.
This problem occurred on an instance with a very high transaction rate. The instance hit a bug in memory management in SQL Server 2008R2 that only occurred when ‘Optimize for Adhoc Workloads’ was enabled. Luckily you can enable and disable this setting with SQL Server online. When we did so, we could see processing rates speed up when we disabled the setting, and slow down when we enabled it. We could also measure the difference in SQL Server wait stats (specifically, CMEMTHREAD waits showed up when it was enabled).
That bug has been fixed and it was definitely an edge case.
This isn’t to say I’d never use ‘Optimize for Adhoc Workloads’. Not at all. Potentially I might find a case someday where it does make a measurable difference and it’s the best option for some reason.
No configuration is completely “safe” all the time, even well documented and often used configurations. Following best practices unfortunately doesn’t mean you can’t hit bugs.
How to enable and disable the setting
You can manage this setting easily with TSQL. Changing this configuration item doesn’t require a SQL Server restart.
/* First check if you have any pending configurations.
Running RECONFIGURE will put all pending items into effect! */
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
/* If everything looks OK, continue */
/* Enable advanced options */
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
/* This enables optimize for adhoc. Set to 0 to disable.*/
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE
GO
Should ‘Optimize for Adhoc’ be part of your standard configuration?
Arguing about this setting is a lot like arguing about whether or not it’s better to put cream in your coffee: if the coffee is decent, it probably tastes fine either way.
My general preference is to not turn on a setting unless I have a good reason to believe that it will make a positive difference, so I don’t turn this on by default. Instead, I’d rather monitor the number of single use plans in cache, and investigate and act accordingly if that number starts spiking upwards.
But admittedly, that’s being fussy: if I had a flock of 1,000 SQL Servers to manage myself and I knew they were all patched to recent supported versions, I’d probably enable it on them all and I wouldn’t feel a bit bad about it, because I wouldn’t have the bandwidth to do it the very best, artisanal, hand-crafted way.
REGISTER