Single-Use Plans in the Procedure Cache

Blitz Result: Single-Use Plans in the Procedure Cache

When SQL Server gets a query, it has to do a lot of work to build an execution plan. To save CPU, it caches these execution plans in memory. When a similar query comes in, SQL Server may be able to reuse that plan.

This part of our SQL Server sp_Blitz script checks sys.dm_exec_cached_plans and sys.dm_exec_query_plan to get the total amount of memory used by cached plans that have only been executed once. This isn’t bulletproof accurate, though: when Optimize for Ad Hoc is used, we’ve seen times when this number will be 1 even though the query has been called twice.

To Fix the Problem

If you’re losing a significant amount of memory to single-use plans sticking around in the procedure cache, you can explore 2008’s Optimize for Ad Hoc option or perhaps Forced Parameterization. You’ll need to do some testing first though: both of these options have the potential to increase CPU. You’ll know when you’ve got the settings right, though, because both CPU and plan cache memory use will drop.