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.)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.