question

NeophyteSQL avatar image
0 Votes"
NeophyteSQL asked SeeyaXi-msft commented

query plans multiple

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NeophyteSQL,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @NeophyteSQL,

You can view Actual execution plan by this:
103278-ep.png
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.



ep.png (26.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.