0

Is there a way to determine the plan handle for the query plan of an executing query by that query's text?

Perhaps in any of the DMVs?

(I'm trying to find the query stats on an ad-hoc query.)

  • 1
    Check this link – Jacob H Nov 13 '19 at 20:37
  • 1
    Try to get QueryHash from an estimated plan and then use the query hash to find the query in sys.dm_exec_query_stats – Denis Rubashkin Nov 14 '19 at 7:10
1

Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

DECLARE @SearchForSql AS NVARCHAR(MAX) = 'SQL TEXT'

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%' ,@SearchForSql,'%')
    AND Text NOT LIKE '%-- Self Reference Marker --%'

Note: The "NOT LIKE" is there to ensure that you don't find the statement you are running, which is inherently self referencing.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.