optimize for adhoc workload setting

Bob sql 456 Reputation points

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.


SQL Server 2017 EE



SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,922 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 14,666 Reputation points Microsoft Vendor

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

    0 comments No comments

0 additional answers

Sort by: Most helpful