Performance related question

Question

Performance related question

LearningDBA

SSCertifiable

Points: 6609
More actions
September 11, 2019 at 2:03 pm

#3679126

This (performance) is the area time to time I struggle with. Couple of days ago, we had an issue where all of a sudden, an application started running slow. I was told to look into it from a SQL Server side and I couldn't find anything (atleast from what I know) because all the available resources looked good, no locking, stats were updated, indexes were rebuilt a night a before so I wanted to rule out that it wasn't the SQL Server issue but the application server issue. Front end developers restarted the web server which resolved the issue. This has happened couple of times and we can't just seem to find the issue. Before I can be sure that this is not a SQL Server related issue, how can I be certain? What other things I should look for before I send the team an email saying 'It isn't the sql server'.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

The fact that restarting the web server fixed the problem leads me to believe that the web server is the problem. It could be connection leaks or memory leaks (the usual suspects) or just about anything else. My suggestion is that they need to start monitoring connections and memory usage on the Web Server because this sure doesn't sound like an SQL Server problem nor a network problem.

And make no doubt about it... if it does turn out to be a connection leak or memory leak, it's going to take some good effort to find it and fix it. And don't let someone talk you into scheduled reboots of the Web Server. That's not a fix... that's kowtowing to a problem that can be fixed.

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 2

Was there any long running query? I see some bad queries sent from the web server that tie up the SQL Server. Maybe not peg things, but they just take too long to run. Then you kill the web server and the next time the query is slightly different and runs fast.