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.