SQL Server 2008 - Update Query Timeout.

Question

I'm using SQL2008 and .NET 4.

The following UPDATE query causes timeout errors.

Table Pages has 600,000 records.

Error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Query:

UPDATE  Pages
SET Checked = 1  , LastChecked = GETDATE()
OUTPUT Inserted.ID, Inserted.Title INTO @Updated
WHERE ID in 
 (SELECT TOP (@Count) ID 
  FROM Pages 
  WHERE Checked = 0 and FileID = @FileID 
  ORDER BY ID)

SELECT * FROM @Updated  
END

On a different thread I Insert records to Pages table, this runs continually (a page is added every 1 sec or less).

INSERT INTO Pages (Title ,.......)
VALUES (@Title , .......)

Why do I get the timeout error and how can I solve it?

Doesn't SQL wait indefinitely on blocking situation ?

Answer 1

It's your command/client that's timing out, rather than SQL Server itself.

Perhaps adjust your SqlCommand.CommandTimeout to a higher value?

 myCmd.CommandTimeout = 360; //seconds

Updates:

Are you sure your 1 second UPDATE statement is the one being blocked/timing out? Perhaps there's something else executing just previous?

Have you looked at SSMS to determine what is being blocked/blocking? Right click your server, select 'Activity Monitor'.

What does SQL Profiler show you as being executed?

Answer 2

As (+1) p.campbell said, the timeout is coming from .NET, not from SQL.

What indexing do you have in place? Without suitable coverage of columns "Checked" and/or "ID", your subquery (SELECT TOP...) will have to read every page in the table, to first get all where Checked = 0 and then sort those by ID. If it's large and being regularly updated that could make it Delay Central.

If they don't have to be ordered by Id, taking out that ORDER BY might cause the query to only read rows until it gets @Count of them.

If you have SQL 2008, a filtered query (...WHERE Checked = 0) might speed things up.