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%'
WHEN objtype = 'adhoc'
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:
- In the Object Explorer, right-click on the server.
- Go to the advanced page and change the property “Optimize for ad hoc Workloads” to true.
Also, you may to implement the change, using T-SQL, run the following query:
SP_CONFIGURE 'Show Advanced Options', 1
SP_CONFIGURE 'optimize for ad hoc workloads', 1
Note: Enabling optimize for ad hoc workloads affects only new plans. The plans that are already in the plan cache are unaffected.
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.