Optimize for Ad Hoc Workloads is one of those server level settings that are not changed very often, but is still good to know about. Before we get into the details let’s talk about what it is. When using SQL Server, it reserves a portion of memory for Plan Cache. The Optimize for Ad Hoc Workloads setting controls what SQL Server places into this plan cache for single use queries. When it is turned off, all single use queries will have the entire plan cached, therefore consuming more space. By turning this on, you are asking SQL Server to not store the entire plan when the query is executed the first time, SQL Server will now only store a plan stub instead. Which consumes less memory than the full plan would. Something to keep in mind, the next time that the query is executed, it will flush the stub from the cache and replace it with the full plan.
There are two methods you can use to determine if this setting is on or off. The first being SQL Server Management Studio(SSMS). In the Object Explorer right click on the server and then click on Properties. The default is False, meaning that the entire plan will be placed in cache when a query is compiled and executed.
As with many things in SQL Server, you can also use TSQL to get this information. You will need to query sys.Configurations to get this setting via TSQL.
SELECT name
, value
, description
FROM sys.configurations
WHERE Name = ‘optimize for ad hoc workloads’
Here is the result of the above query.
How to determine if there are a lot of Single use Queries in Cache
I think there are two measures we need to understand when it comes to finding out what is consuming the cache. One is the amount of space being consumed by Ad Hoc queries and the other is the number of queries that are considered to be Ad Hoc queries.
Below is the Microsoft description of the dm_exec_cached_plans DMV. This DMV can be used to capture the amount of space consumed by Ad Hoc queries.
“Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.” – Microsoft
The code below uses the dm_exec_cached_plans to find out how much space in cache is being consumed by Ad Hoc queries.
From Pinal Dave:
SELECT
AdHoc_Plan_MB, Total_Cache_MB,
AdHoc_Plan_MB*100.0 / Total_Cache_MB AS ‘AdHoc %’
FROM (
SELECT SUM(CASE
WHEN objtype = ‘adhoc’
THEN CONVERT(BIGINT,size_in_bytes)
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
SUM(CONVERT(BIGINT,size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans) T
Below you will see the results of the above query. Note that only 2.8% of the cache is being consumed by Ad Hoc queries.
So now let’s see how many Ad Hoc query plans are currently in cache. The query below used a few additional funtions, sys.dm_exec_sql_text and sys.dm_exec_query_plan, both of which accepts the plan handle as an argument value.
SELECT SUM(c.usecounts)
, c.objtype
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q
GROUP BY c.objtype
The results are below
By looking at these two result sets you can see that although there are 2,542 Ad Hoc query plans in cache, they are consuming a small percentage of the total cache, 2.8%.
So what does this mean? Well, although there are a lot of Ad Hoc queries, if the server is suffering from pressure in memory or in the plan cache, it is probably not due to the Ad Hoc queries.
How do I get the Query Plan
There is a post at www.sqlshack.com that gives really good explanation on how to get the query plan.
This code from the above link. It not only tells us the query text and the plan, but also gives us the number of times that plan was used.
FROM [AdventureWorks2014].[Person].[Person]
Where BusinessEntityID = 3
, c.objtype
, t.text
, q.query_plan
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q
WHERE t.text LIKE ‘%select% *%’ AND q.dbid = 8
ORDER BY c.usecounts DESC
“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.”
Pinal Dave states this on his blog:
“Based on the result you can make your own conclusion and change your settings. I, personally, prefer to turn Optimize for Ad Hoc Workloads settings on when I see AdHoc Percentages between 20-30%. Please note that this value gets reset when you restart SQL Server services. Hence, before you change the settings, make sure that your server is up for a quite a few days.
If you are not sure, what to do, just reach out to me with the result and I will be happy to help you.
One final note, This is just one settings, which contributes to your SQL Server Performance”
I tend to be more in line with what Pinal Dave is saying with the understanding that this is only one setting, however all three make very valid points. You need to look at the big picture and decide for yourself on what the best settings is for your systems. There are a number of other SQL Server settings that could impact performance and you should review them all before making a change. You should look at the big picture for performance to determine if the server could benefit by changing this setting.
Thanks for stopping by my blog….