Spy your Store Procedures

Whenever you execute the Store Procedure, SQL server stores its execution in cache in sys.dm_exec_cached_plans. This can be helpful to have performance enhancements for your DB, if you can query this data of all store procedures with some sort of statistics like execution plan, last call, average time etc.

Here is another quick query to trace the times taken by your all store procedures using this

SELECT TOP 100 PERCENT
    OBJECT_NAME(s.objectid, s.dbid) AS SP_Name,
    MAX(st.last_execution_time) AS last_execution_time,
    SUM(CAST(( st.total_elapsed_time * 1.0 / 100000 ) / st.execution_count AS MONEY)) AS avg_elapsed_time_sec
FROM
    master.sys.dm_exec_cached_plans AS c
    CROSS APPLY master.sys.dm_exec_query_plan(c.plan_handle) AS q
    INNER JOIN master.sys.dm_exec_query_stats AS st
        ON c.plan_handle = st.plan_handle
    CROSS APPLY master.sys.dm_exec_sql_text(sql_handle) AS s
WHERE
    c.cacheobjtype = 'Compiled Plan'
    AND c.objtype = 'Proc'
    AND q.dbid = DB_ID()
GROUP BY
    DB_NAME(q.dbid),
    OBJECT_NAME(s.objectid, s.dbid)
ORDER BY
    avg_elapsed_time_sec DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: