You may increase your SQL performance enabling optimize for ad hoc workloads

What is the optimize for ad hoc workloads option?

When you write a query in SQL Server, the query optimizer needs to create a “Plan” of how it is going to execute that query.

Usually, SQL Servers save that plan in the plan cache to be reused again at a later date.

Optimize for Ad Hoc Workloads is a server-level option that changes the behavior of plan cache for single-use queries.

Turning this on, SQL Server will not store the entire plan when the query is executed the first time, and it will store a plan stub instead, reducing what we call plan bloat.

This reduces the amount of size one-time queries take up in the cache allowing more reusable plans to remain in the cache for more extended periods.

That’s great because some of those plans won’t ever get reused.

Should I Enabled it?

If your workload indeed involves lots of ad hoc queries (different statements on every execution), enabling Optimize for Ad hoc Workloads maybe your best option.

You can check the size of your ad hoc plan and Total Plan Cache, using the query below:

SELECT AdHoc_Plan_MB, Total_Cache_MB,
        AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc plan%'
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) P

If your ad hoc plan cache is 15-30% of total Plan Cache, enabling the Optimize for Ad Hoc Workloads may be beneficial to your SQL server.
However, when there are unparameterized queries, enabling Optimize for Ad hoc Workloads certainly will not help, those original queries will still be getting generated.
The primary issue with forced parameterization is what we call parameter sniffing.
Parameterization is not recommended as a default setting. However, that doesn’t mean it isn’t useful. It is always better for you to find the root cause than just treating the symptoms.

How can I turn on the Optimize for Ad Hoc Workloads option?

You can enable the Optimize for Ad Hoc Workloads using SSMS, following these steps:

  1. In the Object Explorer, right-click on the server.
  2. Go to the advanced page and change the property “Optimize for ad hoc Workloads” to true.
Figure 1 – Optimize for ad hoc workloads option.

Also, you may to implement the change, using T-SQL, run the following query:

SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

Note: Enabling optimize for ad hoc workloads affects only new plans. The plans that are already in the plan cache are unaffected.

More information

Microsoft – optimize for ad hoc workloads Server Configuration Option. 
Optimize for ad hoc workloads – at the database level – in SQL Server, Aaron Bertrand – MSSQLTips.
Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option, Ahmad Yaseen – SQLShack.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.