Multiple Plans

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:

SELECT q.PlanCount,
q.DistinctPlanCount,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount

FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
AND qs.query_hash = PUTYOURHASHRIGHTHERE
ORDER BY q.PlanCount DESC

SELECT q.PlanCount,

q.DistinctPlanCount,

st.text AS QueryText,

qp.query_plan AS QueryPlan

FROM ( SELECT query_hash,

COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,

COUNT(query_hash) AS PlanCount

FROM sys.dm_exec_query_stats

GROUP BY query_hash

) AS q

JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE PlanCount > 1

AND qs.query_hash = PUTYOURHASHRIGHTHERE

ORDER BY q.PlanCount DESC

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:

SELECT q.PlanCount,
q.DistinctPlanCount,
qs.query_hash,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount

FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY q.PlanCount DESC, q.query_hash;