Sporadic timeout expired error in DB updates

Question

Post reply

Sporadic timeout expired error in DB updates

lior-797577

SSC Rookie

Points: 25
More actions
June 27, 2008 at 10:56 am

#126248

Hello all,
Our website has been running for years, and we haven't had code changes in months. A few weeks ago we moved our DB from SQL Server 2000 to a new SQL 2005 server, and now we're starting to get this on updates/inserts/deletes error 3-4 times a day:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
Once it happens for the first time, it happens with any query that updates/inserts/deletes records. How can the same code work most times and then other times cause this error? Regular select queries don't cause this error.
The problem goes away by itself eventually, or it goes away after we restart the MSSQL service.
Any help would be GREATLY appreciated!

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Have you analyzed the execution plan of this query? Are your indexes healthy? Are your statistics up to date?

lior-797577 SSC Rookie Points: 25 More actions · Answer 2

Hey, thanks for the quick reply.

My indexes are fine I think, I don't have any indexes on non-PK columns, its a rather simple database, we're a news website, the updates on the tables aren't massive, one row each time.

Statistics, no they aren't updated, do you think that could be causing it?

I'm not sure how to check the execution plan but again I'm talking about simple stuff, the query that updates article hits which is the simplest in the world (update X set y=y+1 where id=z) gets a timeout...

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

You need to see the execution plan (it's right next to the stop sign in SSMS) to determine if you're seeking or scanning indexes or tables and then looking at the health of indexes as well as their statistics. If indexes are unhealthy or your stats are out of date, you can be running on a bad execution plan ... There are many aspects of this, but you need to start with the execution plan to see how it's running.

lior-797577 SSC Rookie Points: 25 More actions · Answer 4

I see.

I started to look at my indexes and stats, and I admit I never really took the time to do it before. The stats haven't been updated in a month, is that considered bad?

I have a trace file that I created yesterday, I can go over the queries and execution plans and see if there are tables that could use more indexes and if anything's running slowly.

Could things like that really cause this timeout problem? I mean our tables aren't very big, our biggest table has 11,550 rows.

Also, we only have 1 datafile in the database, should I create more and move tables? is that even possible? it's still gonna be on the same disk so will it even matter?

Thanks again for the help

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

lior (6/27/2008)
I see.
I started to look at my indexes and stats, and I admit I never really took the time to do it before. The stats haven't been updated in a month, is that considered bad?

Not if they did not need to be. Do you have auto update stats enabled?

I have a trace file that I created yesterday, I can go over the queries and execution plans and see if there are tables that could use more indexes and if anything's running slowly.
Could things like that really cause this timeout problem? I mean our tables aren't very big, our biggest table has 11,550 rows.

Those are quite small, but again, without seeing the queries, table schema and indexes, I can't say yes or no to that.

Also, we only have 1 datafile in the database, should I create more and move tables? is that even possible? it's still gonna be on the same disk so will it even matter?

It's considered by some a best practice to keep all your tables/indexes in a separate file, on a separate disk, but unless you have a LOT of traffic, I wouldn't think about that route as of yet.

Again, your best bet at this point is to find your problematic queries. Run them and find the execution. Analyze your indexes. Also, Utilize DTA (database tuning advisor) to assist you with some index advice. There are definitely more high level items to be concerned with in regards to performance, but those can come later after you've resolved your issue at hand. Baby steps ...