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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: blogging, DMV, language sql, microsoft sql server, sql statements, system functions, T-SQL