I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored procedure.
So far I have:
select qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset , qs.plan_handle , execution_count , st.text , substring(st.text, (qs.statement_start_offset/2)+1, ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2 + 1)) as [Filtered text] , qp.query_plan from sys.dm_exec_query_stats as qs 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 st.text like '%myProcedure%' order by qs.sql_handle , execution_count desc
But this query doesn't return any information. Ideally I would replace the
st.text like condition with something like
object_name(procedure_id) = 'myProcedure', but I can't find the right way to do so.
Is there a better way to do so?
I could replace the condition in the
WHERE clause with a
qs.sql_handle = 0x000004004040400..etc. but how can I find out the
sql_handle for the procedure? (this information isnt' found in
sys.objects or in
Any information is greatly appreciated.
I've executed the procedure (
exec myProcedure ...) in a test environment and now it is returned by the above query and so I can get the plan handle to use the
where sql_handle = 0x0034300.., but I'd like to run the same query from above in production, without having to run the procedure in production (just to find out its plan handle).
This is the reason why I'm trying to write the query in this way, to get the information based on name, not
sql_handle (just because in prod I don't know what is its