Optimize for Ad Hoc Workloads: How to Stop Plan Cache Bloat in SQL Server

SQL Server compiles an execution plan for every query and keeps it in the plan cache; when the same query arrives again, it reuses the cached plan instead of compiling it again. But many applications embed values in the query text and send a slightly different query each time. WHERE CustomerNo = 1001 and WHERE CustomerNo = 1002 are two different queries to SQL Server. The result: the plan cache fills up with thousands of single-use plans that will never be used again, and memory that could go to reusable plans, or even the buffer pool, is wasted.

This article shows how to measure plan cache bloat, what the optimize for ad hoc workloads setting does, and how to fix the actual source of the problem.

Measuring Plan Cache Bloat

First check whether the problem really exists. The query below groups the plans in the plan cache by type and shows how much memory plans used only once take up:

SELECT objtype,
       COUNT(*)                                             AS plan_count,
       SUM(CAST(size_in_bytes AS bigint)) / 1048576         AS total_mb,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)       AS single_use_plans,
       SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS bigint) ELSE 0 END) / 1048576 AS single_use_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY total_mb DESC;

If single-use plans in the Adhoc row make up a significant share of the plan cache (for example several hundred megabytes or gigabytes), you have plan cache bloat.

What Optimize for Ad Hoc Workloads Does

With this setting on, the first time a query runs SQL Server stores only a small plan stub, not the full plan. If the same query comes a second time, the plan is compiled again and this time stored in full. Queries that never run again then take up only a few hundred bytes in the cache instead of a full plan.

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;

No restart is needed. Existing plans aren’t affected; it applies only to queries compiled from then on. The same setting is on the Advanced page of the server properties in SSMS:

Should You Turn It On?

For most servers, yes. The cost is small: a query that runs a second time is compiled once more. The gain, on systems with many ad hoc queries, is hundreds of megabytes of memory. That’s why the setting is on many DBAs’ standard build list.

The Real Fix: Parameterization

Optimize for ad hoc workloads eases the symptom; it doesn’t remove the cause. The permanent fix is for the application to send parameterized queries:

-- Bad: the value is embedded in the query text; every value means a new plan
-- SELECT * FROM dbo.Orders WHERE CustomerNo = 1001;

-- Good: a parameterized query; one plan is reused
EXEC sys.sp_executesql
     N'SELECT OrderNo, OrderDate, Amount FROM dbo.Orders WHERE CustomerNo = @CustomerNo',
     N'@CustomerNo int',
     @CustomerNo = 1001;

Using parameterized commands in ORMs and application libraries makes queries use the plan cache efficiently and also protects them against SQL injection.

If you can’t change the application, there are two more options:

• Forced parameterization: ALTER DATABASE SalesDB SET PARAMETERIZATION FORCED; SQL Server automatically turns constants in queries into parameters. It can relieve the plan cache considerably, but for some queries it can lead to worse plans because of parameter sensitivity. Measure it in a test environment with Query Store first.
• Plan guides and Query Store hints: enabling parameterization for specific query patterns in a targeted way.

Checklist

• The size of single-use ad hoc plans in the plan cache has been measured.
• optimize for ad hoc workloads is on.
• The query patterns producing the most single-use plans are identified and shared with the application team.
• If the application can’t change, forced parameterization has been measured in a test environment.
• FREEPROCCACHE isn’t used as a routine maintenance step.