Query to find plan details for a specific stored procedure, searching by name

by Radu Gheorghiu   Last Updated July 06, 2016 08:02 AM

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 sys.procedures).

Any information is greatly appreciated.



Related Questions


Parameter Sniffing Question

Updated May 28, 2015 22:02 PM

Stored procedure

Updated April 18, 2015 21:02 PM

Insert/Update SQL Server Stored Procedure

Updated July 06, 2015 14:02 PM

stored procedure not showing a result

Updated July 06, 2015 18:02 PM