how to troubleshoot slow running stored proc

Question

Post reply

how to troubleshoot slow running stored proc

bobrooney.81

SSC-Addicted

Points: 468
More actions
May 21, 2022 at 8:24 am

#4036628

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 analysis 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
June 8, 2022 at 3:14 am

This was removed by the editor as SPAM
June 9, 2022 at 4:04 am

This was removed by the editor as SPAM
June 10, 2022 at 2:23 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2175 More actions · Answer 2

Can't say anything without seeing the code

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 3

Checking on what columns should be of interest if we create some sort of trace.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Run it manually once with the execution plan turned on.

Also, look into Brent Ozar's "First Responder Kit". It's free and the code is open for you to use to learn from and modify if needed.

The other thing to remember is that your longest running queries are usually not your worst queries. It's usually the things that are executed thousands of times per hour. Brent's tools will help you there, as well.

Of course, you could go to the Object Explorer, right click on the instance, select reports, and the follow your nose in the performance reports that will show you the worst resource consuming queries that are in cache. These may actually be a part of a stored procedure which helps with your other question about how to find the parts of stored procedures that are the worst. Brent's code, Adam Machanic's sp_WhoIsActive, and most monitoring tools do the same thing except you can usually see the code they're using and capture the results over time.

Another tool that comes with SQL Server is "Query Store". I've not used it and probably won't but other's swear by it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 5

Thanks Jeff for those pointers. I am basically checking for an extended event trace definition file to track these numbers.

I never used sp_blitz and not sure how it will behave 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...)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

bobrooney.81 wrote:

Thanks Jeff for those pointers. I am basically checking for an extended event trace definition file to track these numbers.
I never used sp_blitz and not sure how it will behave 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...)

That's why I recommend Brent Ozar's stuff. It does a whole lot of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101126 More actions · Answer 7

Focus first on logical I/Os, as that's usually the culprit one way or another.

If you can run the code at will, then add this statement at the start of the proc/code:

SET STATISTICS IO ON;

Then run the code yourself. See which statement(s) are using the most logical I/Os and review those statements.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 8

I would take all the queries out of the stored procedure and run them individually in SSMS with SET STATISTICS IO, TIME ON and also showing the execution plan. It should then become pretty obvious which queries within the SP are causing the slow runtime.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 9

garkbeda43 wrote:

Focus first on logical I/Os, as that's usually the culprit one way or another.

You just re-stated exactly the first line of what I wrote in an earlier post. What is the point of that?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16167 More actions · Answer 10

ScottPletcher wrote:

garkbeda43 wrote:
Focus first on logical I/Os, as that's usually the culprit one way or another.
You just re-stated exactly the first line of what I wrote in an earlier post. What is the point of that?

its a spammer

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

frederico_fonseca wrote:

ScottPletcher wrote:
garkbeda43 wrote:
Focus first on logical I/Os, as that's usually the culprit one way or another.
You just re-stated exactly the first line of what I wrote in an earlier post. What is the point of that?
its a spammer

Usually a SPAM test to see if a connection worked and who will notice. For these kinds in particular, it's a bot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)