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.
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.