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.

  1. Learn about how SQL Server ends up with this problem
  2. Use sp_BlitzCache to find which queries are involved
  3. 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.

Return to sp_Blitz or Ask Us Questions