Today, I have very interesting scenario to share with all of you. This is a true story which I encountered in my recent Comprehensive Database Performance Health Check. It is about a very popular SQL Server setting Optimize for Ad Hoc Workloads.
A Quick Story
One of my customers had engaged me to help them with their server’s performance. They had two different instances on a single server. Even though, they had hired me to fix one instance, they requested me to quickly look at the second instance, as well and help them with anything which I believe is absolutely wrong. Well, I am usually very easy to work with so I complied and also helped them with the second server.
Though both the instances were on the same machine they had absolutely different workload and for the same reason, I had to check them independently and give them different suggestions. During this engagement, I turned on the Optimize for Ad Hoc Workloads for one server and turned to the other server. A senior DBA who was helping me was observing this very carefully. At that time he did not say anything.
However, the next day I got called from their CTO in the early morning. They had a confession to make.
DBA’s Confession
When I was working with both the servers, Sr. DBA who was helping me, clearly saw that I had turned on the Optimize for Ad Hoc Workloads for one instance and turned off for another instance. He thought as I was getting paid for only one instance, I intentionally, set the settings off for the second server. As soon our call was off, he went ahead and changed the setting himself. He turned on the Optimize for Ad Hoc Workloads himself.
Now, after a while, they noticed their server was running a bit slow and their memory consumption was very high. To rule out the suspicious Sr. DBA turned off Optimize for Ad Hoc Workloads settings and suddenly they noticed an improvement in the performance. After several experiments they realized that the settings which I had set were correct and they did not need Optimize for Ad Hoc Workloads for the second server.
This is when he went to his CTO and told the entire story. Honestly, I truly appreciate his honest about the entire incident. I was happy that he is open to learn and man enough to accept his limitation of knowledge.
This is when CTO decided to reach out to me to understand – When to Turn On Optimize for Ad Hoc Workloads?
Optimize for Ad Hoc Workloads
If you are not familiar with this setting, here is the screenshot of the setting. You can find it in Advanced Server Properties.
Every SQL Server Performance Expert has their own rule. I have a very simple rule. If your adhoc plan cache is 20-30% of total Plan Cache, you should turn on the Optimize for Ad Hoc Workloads. In another scenario it may be not beneficial to you and in very rare scenario, it will degrade your server’s settings.
Here is the script which gives you the size of the AdHoc Plan Cache and Total Plan Cache.
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 size_in_bytes
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
SUM(size_in_bytes) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans) TBased 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, if you are interested to talk about all the settings, which can help you to immediately help to improve your SQL Server Performance, you may want to consider signing up for SQL Server Performance Tuning Practical Workshop.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Great Tip!
In my case, I was getting an “Arithmetic overflow error converting expression to data type int.” error. I had to use sum(cast(size_in_bytes as bigint)) to fix it.
Same as Eric, except I just multiplied the summed values * 1.0.
SELECT [AdHoc_Plan_MB],
[Total_Cache_MB],
[AdHoc %] = [AdHoc_Plan_MB] * 100.0 / [Total_Cache_MB]
FROM (
SELECT
[AdHoc_Plan_MB] = SUM(CASE
WHEN [objtype] = ‘adhoc’ THEN [size_in_bytes]
ELSE 0
END * 1.0) / 1048576.0,
[Total_Cache_MB] = SUM([size_in_bytes] * 1.0) / 1048576.0
FROM [sys].[dm_exec_cached_plans]
) [T];
I have ad-hoc plan cache is about 90% on my productions servers. So, I should turn on this option, right?
Why do you divide by 1048576?
I found that if I convert the values to floats before summing, it fixes the overflow issue..
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(float,size_in_bytes)
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
SUM(convert(float,size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans) T
Should you need to filter out by cached plans only used once “objtype = ‘Adhoc’ AND usecounts = 1”
I believe this is the right way… if a cached plan has been used thousands of times, then it’s not a waste of RAM. It might also be good to “tune” this threshold – something like “… and useCounts < @threshold …"
My Adhoc% is 52, which is not between 20-30%. Currently it is set to false. Should I change it to True?
I am planning to enable this on our PROD servers since adhoc cache is very huge. Aside from the adhoc cache percentage, are there other measures in SQL that we can check to verify that enabling this parameter is really helpful?
Our AdHoc % is about 60%, should I turn it ON or OFF?
I believe Dave wanted to say if it is > 20 – 30 %, not between. I have adhoc 77% on server. I am parameterizing SPs to lower this but also I have switched the option ON.
I personally turn it on when I see over 25 % of ad-hoc queries.