“Optimize For Ad Hoc Workloads” Option And SQL Performance

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

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 may be 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 the 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, by 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.

Also, you may implement the change, using T-SQL, by running 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