SQL Server simple Insert statement times out

Question

Hi,

I have a simple table with 6 columns. Most of the time any insert statements to it works just fine, but once in a while I'm getting a DB Timeout exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

Timeout is set to 10 seconds.

I should mention that I'm using NHibernate and that the statement also include a "select SCOPE_IDENTITY()" right after the insert itself.

My thought was that the table was locked or something, but there were no other statements running on that table at that time.

All the inserts are very simple, everything looks normal in sql profiler, the table has no indices but the PK (Page fullness: 98.57 %).

Any ideas on what should I look for?

Thanks.

Answer 1

look at fragmentation of the table, you could be getting page splits because of that

Answer 2

no other statements running on that table at that time.

What about statements running against other tables as part of a transaction? That could leave locks on the problem table.

Also check for log file or datafile growth happening at the time, if you're running SQL2005 it would show in the SQL error logs.

Answer 3

I think your most likely culprit is a blocking lock from another transaction (or maybe from a trigger or something else behind the scenes). The easiest way tot tell is to kick off the INSERT, and while it's hung, run "EXEC SP_WHO2" in another window on the same server. This will list all of the current database activity, and has a column called "BLK" that will show you if any processes are currently blocked. Check the SPID of your hung connection to see if it has anything in the BLK column, and if it does, that's the process that's blocking you.

Even if you don't think there are any other statements running, the only way to know for sure is to list the current transactions using an SP like that one.

Answer 4

Could be that the table is taking a long time to grow.

If you have the table set to grow by a large amount, and don't have instant file initialization enabled, then the query could certainly timeout every once in a while.

Check this mess out: MSDN

Answer 5

Our QA had some Excel connections that returned big result sets, those queries got suspended with WaitType of ASYNC_NETWORK_IO for some time. During this time all other queries timed out, so that specific insert had nothing to do with it.