May 21, 2022 at 8:24 am
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
May 22, 2022 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 22, 2022 at 12:11 pm
Can't say anything without seeing the code
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 22, 2022 at 12:35 pm
Checking on what columns should be of interest if we create some sort of trace.
May 22, 2022 at 6:43 pm
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
Change is inevitable... Change for the better is not.
May 23, 2022 at 10:51 am
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...)
June 8, 2022 at 3:14 am
This was removed by the editor as SPAM
June 8, 2022 at 3:33 pm
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
Change is inevitable... Change for the better is not.
June 8, 2022 at 5:25 pm
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".
June 8, 2022 at 5:44 pm
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.
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
June 10, 2022 at 7:48 pm
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".
June 10, 2022 at 9:03 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply