Search Cache For Execution Plans

Updated content at – Really Search Cache For Execution Plans

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.