SQL Server 2008 - Update Query Timeout

Question

SQL Server 2008 - Update Query Timeout

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 ?

.net

sql-server

tsql

sql-server-2008

asked on Stack Overflow Sep 20, 2010 by

RuSh • edited Sep 20, 2010 by

marc_s

3 Answers

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'. alt text

What does SQL Profiler show you as being executed?

answered on Stack Overflow Sep 20, 2010 by

p.campbell • edited Sep 20, 2010 by

p.campbell

Updating 600,000 rows is a lot of work for SQL.

WHERE IN is a fairly inefficient way to do this clause, I would use:

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

Make sure that @Count is fairly small, between 2000 and 10,000

answered on Stack Overflow Mar 5, 2021 by

Steven Poole

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.

answered on Stack Overflow Sep 20, 2010 by

Philip Kelley

User contributions licensed under CC BY-SA 3.0