how to troubleshoot a slow stored procedure

Question

Hi All,

Assuming I have an problematic stored procedure in production which is taking 45 mins to execute, I want to trace or track which stmt is taking up more resources i/o,cpu,memory,or getting blocked and so long running(duration). Assume that within my stored proc I have 10 sql stmts and wanted to track down those 2-3 statements causing the slowness.(may be blocking . may be reading a whole of data , long running, high reads,high cpu or something else ..)

I also, want to track down compile time and run time parameter values to find out if there is any issue with parameters being passed or wrong plan is being picked up.
Now suppose, I want to setup some kind of trace to gather information , in such case, what columns do I need to choose while setting a trace for troubleshooting a specific stored procedure by putting a filter on dbname and stored proc name.
Second part is, after collecting the trace , how to do the analysis (reporting query) whether we need to check by total duration or reads or cpu time?
Can anyone provide your inputs on how to do multi-dimensional anlysis and why to do that slicing dicing ? Please provide some ideas.

If anyone has already implemented such trace, it would be a great help if it can be shared. I am using SQL 2016 EE.

Thanks,
Bob

Answer 1

Hi @Bob sql ,

You should try to use DMV(Dynamic Management Views), and retrieve from sys.dm_exec_query_stats view, it can return aggregate performance statistics(CPU time, Physical/Logical reads etc…) for each query plans in SQL Server, you also can use Data Collection, and create a custom collection set that gather snapshots from any DMVs and upload them to a performance data warehouse

If you want to trace or record excessively long time running queries, then you can consider use an SQL Server profiler to create a trace

It is recommended that 'server side' trace are used in a production environment, Profiler will allow you to export the SQL statements to create the same thing server-side
https://serverfault.com/questions/72118/create-an-sql-trace-without-using-sql-profiler

https://stackoverflow.com/questions/3456629/how-to-check-which-stored-procedure-is-taking-maximum-time-in-sql-server

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I suggest that you take a look at Lee Tudor's sp_sqltrace, which I am very happy to host on my web site. It collects quite of the information you are looking for.

Given that your procedure runs for 45 minutes, I like to raise your attention to the @Trace _timeout parameter, as by default the traces created by sp_sqltrace stops after five minutes. I would also recommend that you specify @koaaa _waits = 0, at least for the first round, as the aggregation of waitstats can take quite some time.

Answer 3

I would start with an actual query plan for the proc and see if there is anything you can easily solve in the query plan, add an index, etc.