sp_BlitzCache Finding: Queries with Multiple Execution Plans

Queries have been found with multiple execution plans. This can be caused by multiple things, but the two primary causes are a lack of parameterization or improper parameterization.

When queries aren’t parameterized, SQL Server will end up creating a separate plan for each set of literal values. This can cause significant plan cache bloat and lead to memory problems over time.

Even when queries are parameterized, SQL Server may create multiple execution plans for the same query. SQL Server will create a separate execution plan for different variations of parameter length.

How to Fix the Problem

To fix this problem, you need to identify queries with multiple execution plans and figure out why these queries are generating multiple plans.

Run sp_BlitzCache @ExpertMode = 1 and look at the Query Hash column for the query with multiple plans. Get that hash, and then pass it into the following T-SQL in the WHERE clause:

You’ll see what looks like a long list of nearly identical queries, but look carefully – they’re in different databases, or they have slightly different white spacing, or comments are different, or whatever.

If you’d like to find more queries that sp_BlitzCache didn’t report as performance problems, this query will list all of them in the cache:

 

Menu