September 10, 2015 by Kenneth Fisher
The other day I was asked to tune a stored procedure. Not exactly an uncommon task, but I worked something out in the process. I typically want to start by determining what the slowest part of the SP is. Normally I use sys.dm_exec_query_stats and run a query something like this:
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE st.objectid = object_id('Test.dbo.Sneezy');
There is a bit of a problem with this though. sys.dm_exec_sql_text is slow, particularly when running against a large cache. I have one system where this can take 5-10 minutes to run. So this time I decided to use sys.dm_exec_procedure_stats instead. It’s fast and has an object_id column.
SELECT * FROM sys.dm_exec_procedure_stats WHERE object_id = object_id('Test.dbo.Sneezy');
It doesn’t, however, break the code down. It’s just the stats for the procedure as a whole. So for my first pass I grabbed the sql_handle (or plan_handle) out and then ran this query:
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE sql_handle = 0x0300060016B804056B1D2101FCA4000001000000000000000000000000000000000000000000000000000000
And I’m good to go! Each of the queries in the SP broken down. I still need more information though.
Well to make a long story, well, not quite as long, here is what I ended up with.
SELECT CAST(qp.query_plan AS XML) AS XML_Plan, SUBSTRING(st.text,qs.statement_start_offset/2+1, ((CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END) - qs.statement_start_offset)/2 + 1) AS SqlText, qs.* FROM sys.dm_exec_query_stats qs JOIN sys.dm_exec_procedure_stats ps ON qs.sql_handle = ps.sql_handle CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, statement_start_offset, statement_end_offset) qp WHERE PS.object_id = object_id('Test.dbo.Sneezy');
I ended up with the query plan and text for each individual query within the SP and the associated stats. For example min/max/total duration/cpu time, number of executions, etc. All of which really helps you pin down the parts of the SP you really need to work on.