Simple SQL Delete statement giving timeout

Question

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

Answer 1

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.

Answer 2

I was struggling with a DELETE statement which was causing our ERP (Epicor 10) system to timeout on its nightly run of MRP. It was a delete with a few joins in it, and one of the tables involved in the join is quite large. The odd thing was that if I transformed the delete to a SELECT * for the same joins / where clause, then the query would complete instantly, and had ZERO results (i.e. the delete statement was obviously doing some long, large table scanning but it didn't actually find anything to delete).

What finally helped was I ran the select statment version of it with Show actual execution plan turned on, and this had a suggested Non-Unique Index to add to one of the tables. After I added this index, the select statement still ran instantly, but now the delete statement did as well!

Answer 3

that's very odd. my guess is you have a ON DELETE CASCADE foreign key referencing your Users table elsewhere in your schema. Check your constraints:

SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName,
    f.delete_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id