SQL SERVER – When to Turn On Optimize for Ad Hoc Workloads?

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.

SQL SERVER - When to Turn On Optimize for Ad Hoc Workloads? Workloads1-800x246

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.

Solarwinds

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.

SQL SERVER - When to Turn On Optimize for Ad Hoc Workloads? Workloads

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)

Solarwinds
, , ,
Previous Post
SQL Authority News – SQL Server 2017 and Automatic Tuning
Next Post
SQL SERVER – Compatibility Level 80 and Table Hint Behavior

Related Posts

7 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.

    Reply
    • 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];

      Reply
  • I have ad-hoc plan cache is about 90% on my productions servers. So, I should turn on this option, right?

    Reply
  • Why do you divide by 1048576?

    Reply
  • 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

    Reply
  • Should you need to filter out by cached plans only used once “objtype = ‘Adhoc’ AND usecounts = 1”

    Reply
  • suraj pudasaini
    January 25, 2019 9:29 pm

    My Adhoc% is 52, which is not between 20-30%. Currently it is set to false. Should I change it to True?

    Reply

Leave a Reply

Menu