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.