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.
- Learn about how SQL Server ends up with this problem
- Use sp_BlitzCache to find which queries are involved
- Work with your developers to parameterize those queries, or consider using Optimize for Ad Hoc or Forced Parameterization depending on the problem you’re facing
To learn more about plan compilation and reuse, check out Grant Fritchey’s excellent book, SQL Server 2008 Query Performance Tuning Distilled.
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 the 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.