Search code examples
sqlsql-serversql-server-2008timeout

Simple SQL Delete statement giving timeout

When executing this simple SQL Delete statement, I'm getting a timeout from SQL Server:

DELETE FROM dbo.[User] WHERE Id = 95146

There are about 95.000 records in the table.

I thought it might be because of indexes on the table, so I've deleted all except from the primary key (Id) which is clustered, but this didn't help.

I've deleted all the statistics I created as well, but also without any effect.

What else can I do to optimize for this?

Kind regards, David

Solution

  • How many foreign keys do you have referencing the Id column of Users, and are there indexes on these columns?

    If cascade is set as NO_ACTION, as you've indicated, the time may be being spent by SQL Server having to perform a full table scan on each of these tables to ensure that there's no reference to Id 95146 - I've seen this easily take minutes at a time before, if the other tables are large.