query plans multiple

Question

some of the queries we are running are generating multiple plans, how do i identify such queries

Answer 1

Your question is not crystal clear, but here is a simple query for the task.

SELECT est.text, qs.sql_handle, qs.cnt
FROM   (SELECT sql_handle, COUNT(*) AS cnt
        FROM   sys.dm_exec_query_stats 
        GROUP  BY sql_handle
        HAVING COUNT(*) > 1) qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle)est

When I tested. I got a lot of this that I suspect comes from the Telemetry service. If you you can refine your specification, I may be able to refine the query.

Answer 2

Hi @NeophyteSQL ,

You can view Actual execution plan by this:

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.
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  

Please refer to this blog: https://www.brentozar.com/blitzcache/multiple-plans/

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.