question

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 asked Yufeishao-msft commented

how to troubleshoot a slow stored procedure

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

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wonderful

0 Votes 0 ·


Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
Yufeishao-msft avatar image
1 Vote"
Yufeishao-msft answered

Hi @Bobsql-8788,

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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 @show_waits = 0, at least for the first round, as the aggregation of waitstats can take quite some time.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Sommarskog. I am used to sp_sqltrace and I am not comfortable running it on prod.
Looking for a simple straight forward trace or extended event trace definition file where I can start the trace and stop it manually and executing a reporting query for analysis based on different columns.

The other thing I was looking for is analysis part.. sample queries which can used for slice and dicing analysis. ( like by reads, by cpu etc...)

0 Votes 0 ·

When it comes to Trace, you cannot meet your requirements in leaner way than you do with sp_sqltrace (assuming that you run with @show_waits = 0). You say that you want to trace on statement level. That can certainly cause problems on a server where you have a workload with many short statements.

They say that Extended Events is leaner in overhead, but the same thing applies: with a workload with many short statements, capturing sp_statement_completed can be detrimental. In any case, I don't have anything similar to sp_sqltrace to offer for extended events.

To summarise: if you have a workload that has a lot of short statements, you cannot really do direct statement tracing with any method.

Alternatives are using sys.dm_exec_query_stats to look at the aggregated statistics, but if there are recompilations, numbers may not be wholly accurate.

Query Store is a better option, although again workloads with many ad-hoc statements can suffer badly from enabling Query Store. Using capture mode AUTO can alleviate the pain, though.

From Query Store you can run queries to get totals for all statement in a stored procedure. Or, well, you may also have to include stored procedures it calls. If there is dynamic SQL involved, it can start to get difficult. On the other hand, with sp_sqltrace that is not an issue...

1 Vote 1 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.