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.
Trackbacks
-
6/3/2009 9:35 PM
Strate SQL wrote:
Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago. -
6/4/2009 12:28 PM
Strate SQL wrote:
Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago. The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results. Some plans were being duplicated and the performance statistics weren’t accurate because of this. And why not fix the issues and make a DBADiagnostics post out of it. In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed. ... -
6/4/2009 12:31 PM
Strate SQL wrote:
Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months
Comments