Handling reports of slow-running procedures

Question

Handling reports of slow-running procedures

webrunner

SSC-Dedicated

Points: 31862
More actions
June 17, 2021 at 2:15 pm

#3897477

Hello experts,
Does anyone know of a page or document that outlines a process for user reports of "slow-running procedures"? This issue is pretty frustrating for me because SQL activity is so dynamic that it's not like easier problems of missing permissions, low drive space, etc. Each time I get a request to look into slow-running procedures, I feel anxious because I know the "it depends" reality of database administration - which procedures, what do they mean by "slow," and so on.
I admit that we don't have a robust baseline that might reveal a jump in procedure execution duration. But it would be great to have a way to either (1) find and fix the slowness if it is a code issue or at least (2) have a process to see this increase coming with some kind of warning so that the users aren't the first ones to tell us there is an issue.
Thanks for any advice.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 29, 2021 at 11:24 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

At my previous job, i set up a process to check for any blocking processes. It would run every minute, and write the information to a table, so I could refer back and see that "Process A" was blocking "Process B" at 10:23 am yesterday.

I also ran a trace that captured SQL activity and saved the trace files for a few weeks. If "queries are slow today" I could look at the trace files to see what ad-hoc queries and stored procedure were running within a specified date & time. Then I have a start of what to look at.

It also helps to get specific information about which procedures are slow. Do they have parameters that may look for small results 1 day, and large results the next day ? Or some parameters have a relevant index, but others do not ?

This reply was modified 3 years, 5 months ago by homebrew01.
This reply was modified 3 years, 5 months ago by homebrew01.

webrunner SSC-Dedicated Points: 31862 More actions · Answer 2

Thanks! That is a good starting point that I can work with.

One of my fellow DBAs also noticed stat update and procedure cache issues. I should be able to find or develop some checks for those items too so we can create a baseline of those metrics. The more baseline info we can collect, the better we can set up and tune a process to send early warnings when things start going off the rails.

And then I hope we can identify the specific procs and try to help the developers optimize them so they don't use so much memory/CPU in the first place.

Thanks again.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

I've found that it's not often a "slow running query" is the actual problem. I've found that it's normally one that could certainly run better/faster/fewer-resources that runs hundreds or thousands of times per hour.

You can get a pretty good hint as to what those are by right-clicking on the instance in the Object Explorer window of SSMS and then select {Reports}{Standard Reports} and the one of the {Performance - Top Queries by xxxxxx} reports with the understanding that anything that clears procedure cache will also change what looks like the current worst queries. Most people don't realize that happens more than you might expect but it's still a good method to get some really good hints about what your worst queries (which are usually NOT your longest running queries) are.

Some people scoff at that. I don't. It's free and it's as accurate for the moment (it has no point-in-time history) as anything your can buy.

--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: 101095 More actions · Answer 4

What almost always gives the best payback is to first do a review of all indexes on the table, particularly on the clustered indexes. Until you have the best clus index on every table, you're wasting resources, and at some point that could cause a performance issue, no matter what other tuning you do. Hint: if most every table is clus by $identity, the indexes are NOT correct.

Unfortunately, index tuning is not well understood and not many people can do it accurately. The advantage of getting it right, though, is that you've tuned all your processes at once. Then you can focus on any queries that still have a performance issue, knowing that it is related to that query and not to your overall index structure.

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".

tripleAxe SSCertifiable Points: 5706 More actions · Answer 5

Do you have query store enabled? This can be very useful in tracking slow running queries down. I'd also recommend taking a look at Extended Events and setting up traces to help you. Maybe start with anything taking longer than 30 seconds, unless you have reports of query time-outs (default client setting is normally 30 seconds), in which case you can look for aborted executions in the trace. Once you have captured a few slow-running queries you will want to run some traces to capture executions of that particular query to see if the slowness is a regular thing or an every so often kind of event.

Check out the following links for more information.....

https://www.red-gate.com/simple-talk/sql/database-administration/the-sql-server-2016-query-store-overview-and-architecture/

https://www.sqlservercentral.com/stairways/stairway-to-sql-server-extended-events

This reply was modified 3 years, 4 months ago by tripleAxe.