Finding Problem Code

Question

Finding Problem Code

Viewing 5 posts - 46 through 49 (of 49 total)

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

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 1

Eric,

If your users claim the server is slow, I would do several things:

> Ask them, "Compared to what?" This is not a smart *** question. It's a realistic question about their expectations and where they got them.

> Ask them, "What reports are slow?", or "What queries are slow?" and focus on evaluating the query plans for those reports/queries for efficiency. Pay close attention to all scans.

> Look at the Performance Dashboard list of the queries with the highest duration of execution times. Often, these are trouble spots.

> Configure the Windows Server PERFMON program to track blocking. Blocking can be a subtle problem that makes otherwise efficient queries appear to execute slowly.

Hope this helps.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Gail Wanabee (5/15/2015)
Eric,
If your users claim the server is slow, I would do several things:
> Ask them, "Compared to what?" This is not a smart *** question. It's a realistic question about their expectations and where they got them.
> Ask them, "What reports are slow?", or "What queries are slow?" and focus on evaluating the query plans for those reports/queries for efficiency. Pay close attention to all scans.
> Look at the Performance Dashboard list of the queries with the highest duration of execution times. Often, these are trouble spots.
> Configure the Windows Server PERFMON program to track blocking. Blocking can be a subtle problem that makes otherwise efficient queries appear to execute slowly.
Hope this helps.

I don't ask such questions. My reply is "Nope. The server is fine. Here's the junk code that's actually the problem on a normal basis that we work on as a part of the Continuous Improvement Program and the user login, machine name, and long-winded, broke-back code that a user was running (a poorly written 10 year ad hoc report) that was blocking 126 other SPIDs in the first 5 seconds that it ran that brought the server to it's knees before I killed it 45 seconds later. By the way, it was your code that caused the problem and so I've disabled your login until I talk with your boss because I've told you not to run that code 3 times now."

😀

--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)

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 3

After reading Jeff's post, I think I'll revise my strategy for dealing with problem queries.

It's a lot more efficient than mine.

:hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (5/8/2015)
I've found that it's usually not the long running queries that are a problem. It's usually the most frequently run queries that are the problem.

This. The queries that run a long time are not necessarily the problem. If you're tuning for overall system performance it's not the place to look.

I recently made a change to a system that dropped the CPU usage by 30%. That change? Taking a query that ran in under 50ms and caching its results in the application tier.

Oh, did I mention the query ran about 100 times a second on average?

If you look for long running queries only you'd never see that query, you'd never consider it to be the problem, however it's aggregate CPU usage in an hour period was an order of magnitude higher than any other query in the system. You need to look at aggregate resource usage over a period of time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

A lot of good experience has been written for finding problem queries on this thread. The experiences can be summed up by saying that for a person to identify potential problem queries, one should focus on queries that are consuming one or more server resources at a distinguishing level.

Gila Monster's post regarding high overall CPU utilization for a short executing query was a particularly useful and pertinent example.

But, before we move on to another post and another day, I am going to revisit long executing queries.

Long executing queries “occupy” a server resource: Time. Unless a query is executing a "WAITFOR DELAY.." command, if it's executing, it's either doing something or having something done to it. It's either consuming a server resource(s) or it's being blocked. In either case, long executing queries are candidates for investigation, to learn what resources they are consuming and how they are doing it, or, if they are being blocked, what is extending their execution time? That is, “What is (are) the blocking point(s) and how can they be eliminated or minimized?”