optimize for adhoc workload setting

Question

Hi Experts,

What is 'optimize for ad hoc workloads' do mean in short?

What is the benefit we get if we set 'optimize for ad hoc workloads' = 1.

How to verify if we really have to set this up on sql instance or leave it to default 0.

Environment:

SQL Server 2017 EE

Cheers,

Bob

Answer 1

Hi @Bob sql

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches.

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This may help to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that aren't reused. However, enabling this option may affect your ability to troubleshoot single-use plans.

See optimize for ad hoc workloads server configuration option.

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. 

Note that this value gets reset when you restart SQL Server services.

Refer to this article for more details: SQL SERVER – When to Turn On Optimize for Ad Hoc Workloads?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.