SA0143 : Single use Ad-hoc plans are using considerable amount of the procedure cache

In SQL Server, ad-hoc query plans are generated for individual queries that are not frequently reused. These plans can clutter the cache, leading to increased memory consumption without adding value. When SQL Server’s cache is filled with such single-use plans, it prevents the efficient storage of query plans that might be reused, resulting in performance degradation.

For example:

1-- Example of an ad-hoc query that could fill the cache with a one-time-use plan
2SELECT * FROM Employees WHERE EmployeeID = 1234;

In this example, if similar queries with different literals are run repeatedly, each one may generate a new ad-hoc plan, quickly filling the cache with plans that are unlikely to be reused, rather than storing valuable plans that can improve performance.

• Memory is wasted on ad-hoc plans rather than being available for useful reusable plans or data caching.

• Other essential operations may experience degraded performance due to insufficient memory availability.

Implement the ‘Optimize For Ad-Hoc Workloads’ setting to manage the SQL Server query plan cache more efficiently.

Follow these steps to address the issue of excessive cached ad-hoc plans:

1. Determine if there are a significant number of single-use ad-hoc plans in the cache. You can use the following query to assess the situation:

 1-- Query to check for single-use ad-hoc plans
 2SELECT 
 3    cp.objtype AS PlanType,
 4    COUNT(*) AS PlanCount,
 5    SUM(cp.size_in_bytes) / 1024 / 1024 AS SizeInMB
 6FROM 
 7    sys.dm_exec_cached_plans AS cp
 8WHERE 
 9    cp.objtype = 'Adhoc'
10    AND cp.usecounts = 1
11GROUP BY 
12    cp.objtype

1. If you identify a large number of single-use ad-hoc plans, enable the Optimize For Ad-Hoc Workloads server option to conserve memory. This setting ensures that only a small stub of the ad-hoc plan is cached on its first execution.

1-- Enable Optimize For Ad-Hoc Workloads
2EXEC sp_configure 'show advanced options', 1;
3RECONFIGURE;
4EXEC sp_configure 'optimize for ad hoc workloads', 1;
5RECONFIGURE;

1. Monitor the change’s impact on memory usage and plan cache efficiency. Adjust as necessary based on your specific workload and performance requirements.

For example:

1-- Example of enabling Optimize For Ad-Hoc Workloads
2EXEC sp_configure 'show advanced options', 1;
3RECONFIGURE;
4EXEC sp_configure 'optimize for ad hoc workloads', 1;
5RECONFIGURE;

The rule has a ContextOnly scope and is applied only on current server and database schema.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Bugs

optimize for ad hoc workloads Server Configuration Option

Other Resources