Timeouts Popping Up for Production Database

Question

Timeouts Popping Up for Production Database

Seggerman-675349

Hall of Fame

Points: 3563
More actions
July 28, 2017 at 11:29 am

#343780

Exceptions - timeout and connection pool errors are starting to appear in a database for a web application which accesses its data using Entity Framework. This is only happening recently and as I only took over the application no change has been made except a few changes to a few tables. The server ran out of space a while back - backups were deleted to free up space but should the database be reorganized? I'm not a DBA but I've had to backup / restore databases before. Its a free game so going offline won't be a catastrophe. Any guru assistance would be appreciated. Thanks in advanced. Peace.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

with no known changes having occurred, i would update statistics, first, other than burning some CPU cycles, it might fix things that have out of date stats, which could be causing your time outs, do to a poor plan being used.
For me, that's an immediate quick fix without any significant side affects.
do you know if there is a job in place, that for example uses ola hollengren's Maintenance Solution[/url] to do a smart update statistics?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 2

Seggerman-675349

Hall of Fame

Points: 3563

July 28, 2017 at 11:51 am

#1953232

I'll try that - thanks bunches

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 3

I did - it only took half a minute but what a difference it made

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

I absolutely agree that keeping stats up to date is uber important but there's one other thing to check... make sure that the connection strings used by anything and everything that connects through a connection string forcibly turn off MARS (Multiple Active Result Sets = OFF). We just went through that nightmare and it occurred for no apparent reason other than the data got bigger. The change was remarkable and all timeouts (which eventually led to KILLED/ROLLED BACK) simply stopped happening.

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

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 5

I'll have to check with the previous developer - it may be necessary for some reason - I think I recall seeing it explicitly turned on
but thanks for the tip

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

Seggerman-675349 - Saturday, July 29, 2017 6:01 AM
I'll have to check with the previous developer - it may be necessary for some reason - I think I recall seeing it explicitly turned on
but thanks for the tip

I have to tell you that I checked very carefully with all 4 of my seasoned developers and the only reason why they had it turned on was because that's the way it was when we all got here. There were no adverse effects when we turned it off and CPU, Reads, and Writes all decreased substantially. CPU dropped from 22% across 48 CPUs to 10% on our heavy hit OLTP server. Even if they say no, it's worth you doing the test and worth you remembering because, just like us, someday it's going to bite you. This is one time that the Microsoft default setting of OFF is a good thing. It should be used only to solve very specific problems and those problems usually are a bad idea to begin with.

In our case, it was the cause of 32 core going to 90%, connections going nuts, wait queues going off the chart and no one being able to use the system for 20-30 minutes at a time. They threw in another 16 core CPU and the only difference it made was that 48 core went to 90%.

MARS uses extra resources that are usually not necessary to get the job done and, when it goes bad, it goes very bad.

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

Arsh SSCertifiable Points: 6354 More actions · Answer 7

Most of the time outs occurs as a result of statistics becoming stale too early due to increasing in data volume . Even if the auto update stats is enabled , the stats become too stale to proceed further with query processing until the stats are updated when the sampling threshold approaches. The query is held long enough to be flagged with Time Out expired error. More so bcos of the increase in data volume which keeps on pushing the sampling threshold farther and farther. I advise to set Asynchronous stats update ON and test it first ; Or dig deeper into the modifications data for each potential object and then consider a manual stats update job at non-peak hours. Hope it helps.

..Arshad