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.
By the way, this check is a part of our SQL Server Health Check service.
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.
Enable or not – “Optimize for Ad hoc Workloads”?
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_bytesELSE 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 to turn “Optimize for Ad Hoc Workloads option” ON?
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
GORECONFIGUREGOSP_CONFIGURE 'optimize for ad hoc workloads', 1
GORECONFIGUREGO
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.
