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

---

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.

---

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

---

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!

---

While your statement is running, take a look at the list of running tasks and locks taken out by the system. You can get this information from Activity Monitor, or you can look at running tasks in the view sys.dm_os_waiting_tasks and locks with exec sp_lock and sys.dm_tran_locks.

Also, in SQL Server Management Studio, enter this statement and look at the estimated execution plan. Maybe you'll be able to see what SQL Server is trying to do.

Take a look at foreign keys to this table. You may need to add some indexes on other tables to optimize foreign key assertions.

---

In addition to the other responses, perhaps your DELETE is being blocked by some other activity (see if there is any value in the BlkBy column of sp_who2 for your spid), or perhaps there is a trigger on the table that is doing something unhealthy. Most of the things mentioned will slow the delete down, but except in extremely elaborate situations, not significantly enough to cause a timeout.

---

My timeout was caused by a transaction that was not finished. I started with

BEGIN TRAN

but did not execute

COMMIT TRAN

or

ROLLBACK TRAN