SQL Server simple Insert statement times out

Question

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

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 to 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 2

This question seems like a good place for a code snippet which I used to see the actual SQL text of the blocked and blocking queries.

The snippet below employs the convention that SP_WHO2 returns " ." text for BlockedBy for the non-blocked queries, and so it filters them out and returns the SQL text of the remaining queries (both "victim" and "culprit" ones):

--prepare a table so that we can filter out sp_who2 results DECLARE @who TABLE(BlockedId INT,                     Status VARCHAR(MAX),                     LOGIN VARCHAR(MAX),                     HostName VARCHAR(MAX),                     BlockedById VARCHAR(MAX),                     DBName VARCHAR(MAX),                     Command VARCHAR(MAX),                     CPUTime INT,                     DiskIO INT,                     LastBatch VARCHAR(MAX),                     ProgramName VARCHAR(MAX),                     SPID_1 INT,                     REQUESTID INT) INSERT INTO @who EXEC sp_who2  --select the blocked and blocking queries (if any) as SQL text SELECT  (     SELECT TEXT      FROM sys.dm_exec_sql_text(        (SELECT handle          FROM (             SELECT CAST(sql_handle AS VARBINARY(128)) AS handle             FROM sys.sysprocesses WHERE spid = BlockedId         ) query)     ) ) AS 'Blocked Query (Victim)', (     SELECT TEXT      FROM sys.dm_exec_sql_text(        (SELECT handle          FROM (             SELECT CAST(sql_handle AS VARBINARY(128)) AS handle             FROM sys.sysprocesses WHERE spid = BlockedById         ) query)     ) ) AS 'Blocking Query (Culprit)' FROM @who  WHERE BlockedById != '  .'