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.
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) T
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, 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)