SQL SERVER – AdHoc Queries and Optimize for Adhoc Workloads

Optimize for AdHoc Queries reduces the first-use plan-cache footprint of ad hoc batches. A customer variable-query workload prompted testing.

SELECT name,value,value_in_use FROM sys.configurations
WHERE name=N'optimize for ad hoc workloads';
SELECT objtype,cacheobjtype,COUNT_BIG(*) AS EntryCount,
       SUM(CONVERT(bigint,size_in_bytes))/1048576.0 AS CacheMB
FROM sys.dm_exec_cached_plans WHERE usecounts=1
GROUP BY objtype,cacheobjtype ORDER BY CacheMB DESC;

When enabled, SQL Server can retain a compiled-plan stub after the first use. The batch still compiles and executes. A later qualifying use can create a full cached plan. The option does not eliminate compilation.

The customer CPU observation fell from about 100 percent to 60 percent after the change. That was a specific before/after result. It is not a guaranteed reduction. Measure memory, compilation and reuse separately.

Inspect single-use entries and application parameterization. Changing literals differs from changing query structure. Assess parameter-sensitive behavior and retain a baseline before configuration changes. This read-only inventory does not alter the setting or clear plans.

Reference: Ad hoc plan stubs and limitations.

Related reading

• Comprehensive Database Performance Health Check
• blog post
• SQL in Sixty Seconds video
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A plan stub is not elimination of compilation, it is a smaller first-use cache entry.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.