SQL Server Health Check

“Optimize for Ad Hoc Workloads” Option and SQL Performance

Updated October 29, 20254 min read

Ad hoc queries cause plan cache bloat. Ploom pushes against the door of a cupboard bursting with crumpled paper plans.

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.

When you turn 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:

T-SQL
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.
How to change “Optimize for Ad hoc Workloads” option
How to change the “Optimize for Ad hoc Workloads” option

Also, you may implement the change, using T-SQL, by running the following query:

T-SQL
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

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜