Sometimes when I am working with clients I need to take a look at execution plans for different stored procedures.  Rather than digging around with SQL Profiler or executing the stored procedures with what I think might be the parameters I like to ‘”return to the source”.

Now I feel like I’m making a lame Matrix reference by mentioning the source.  Heck just the mere mention made a neat little picture show up on the blog.

But references aside, going to the execution plan cache is often the best place to go to determine why a stored procedure is performing in a sub-optimal mode.  To accomplish this search, I typically use the T-SQL script below.

All that is needed is to replace the parameter values with the name of the database, schema, and/or stored procedure that needs to be found and it will be returned.  Leaving one or more of the parameters blank will increase the range of the search.

DECLARE @DatabaseName sysname

    ,@SchemaName sysname

    ,@ObjectName sysname

SELECT @DatabaseName = ''

    ,@SchemaName = ''

    ,@ObjectName = ''

;WITH cteExecInfo (database_name, object_name, execution_count, total_cpu_time, total_physical_reads, total_elapsed_time, sql_text, plan_handle) 

AS 

( 

SELECT  DB_NAME(st.dbid) as database_name 

    ,OBJECT_NAME(st.objectid, st.dbid) as object_name 

    ,qs.execution_count 

    ,SUM(qs.total_worker_time) 

    ,SUM(qs.total_physical_reads) 

    ,SUM(qs.total_elapsed_time) 

    ,st.text 

    ,qs.plan_handle 

FROM    sys.dm_exec_query_stats qs 

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 

WHERE (st.dbid = DB_ID(@DatabaseName) OR DB_ID(@DatabaseName) IS NULL)

AND (st.objectid = OBJECT_ID(QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName))

    OR OBJECT_ID(QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)) IS NULL)

GROUP BY st.dbid, st.objectid, qs.execution_count, st.text, qs.plan_handle 

) 

SELECT cte.database_name 

    ,cte.object_name 

    ,cte.execution_count 

    ,cte.total_cpu_time 

    ,cte.total_physical_reads 

    ,cte.total_elapsed_time  

    ,CAST(cte.total_cpu_time/(cte.execution_count*1.) as decimal(12,2)) as avg_cpu_time

    ,CAST(cte.total_physical_reads/(cte.execution_count*1.) as decimal(12,2)) as avg_elapsed_time

    ,CAST(cte.total_elapsed_time/(cte.execution_count*1.) as decimal(12,2)) as avg_elapsed_time

    ,qp.query_plan 

    ,cte.sql_text

FROM cteExecInfo cte 

    OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp 

The script isn’t exactly perfect, since sys.dm_exec_query_stats aggregates at the statement level there is a good likelihood with some stored procedures will aggregate incorrectly.  If a stored procedure splits between multiple T-SQL statements due to conditional logic (IF statements) each previously executed possible T-SQL statement will be in the DMV.  But I wanted aggregations so that I could make rough estimates of which plans were performing less optimally than others so they are there.  (I feel like I just said nanner, nanner).  Anyways – don’t use this aggregations for reporting.

If anyone finds this useful or has any suggestions, let me know.  For more information on DMVs, take a look at Troubleshooting Performance Problems in SQL Server 2005.  This is the source document that I’ve used over the last year or so to learn about DMVs.