Troubleshoot a Stored Procedure

Hello SQL Folks – I observed multiple times when a stored procedure in your SQL Server instance is running slow and creating typical performance problems. Sometimes it feels a bit hard to troubleshoot it and situation become worse when it’s a case of nested stored procedure OR SP itself contains thousand lines of code.

To simplify this problem, I wrote a below query which will help you to identify, only time consuming part OR problematic part of that SP with its execution plan, moreover this is the only execution plan for time consuming part of that SP and not a full execution plan of SP. In the below query output column name as text filter is a column which shows different T-SQL\batches that runs inside a Stored Procedure. Also it shows Avg. run time, Last run time and min. run time of each T-SQL\batch.

By using below script it will make your life easier to concentrate only on problematic part instead to troubleshoot full SP.

 

Select top 50

qs.plan_handle

, st.text

,substring (st.text, (qs.statement_start_offset/2)+1,

((case qs.statement_end_offset

when 1 then datalength(st.text)

else qs.statement_end_offset

end qs.statement_start_offset)/2) + 1) as text_filters

,(qs.total_worker_time)/1000.0 as [Total CPU (ms)]

,(qs.total_worker_time/qs.execution_count)/1000.0 as [Avg CPU Time (ms)]

,(qs.total_elapsed_time)/1000.0 as [Total Duration (ms)]

,(qs.total_elapsed_time/qs.execution_count)/1000.0 as [Avg Duration (ms)]

,(qs.last_elapsed_time)/1000.0 as [Last Duration (ms)]

,(qs.min_elapsed_time)/1000.0 as [Min Duration (ms)]

,(qs.max_elapsed_time)/1000.0 as [Max Duration (ms)]

,qs.last_execution_time as [Last Execution]

,execution_count as [#Executions]

,cast (qp.query_plan as xml) as Query_Plan

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text (qs.sql_handle) as st

cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset,

qs.statement_end_offset) as qp

where st.text like ‘% <Stored_Procedure_Name> %’

order by [Total CPU (ms)] desc

 

Hope it will help you. Stay tune for more upcoming interesting solutions on complex SQL performance issues.

Cheers – Gaurav