Simple SQL Delete statement giving a timeout

Question

When I execute this simple SQL Delete statement, I get a timeout from SQL Server:

DELETE FROM dbo.[User] WHERE Id = 95146 

The table contains about 95,000 entries.

I thought this might be due to the indices in the table, so I deleted everything except the main (Id) cluster, but that didn't help.

I deleted all statistics that I created, but also without any effects.

What else can I do to optimize for this?

Regards, David

Answer 1

How many foreign keys do you reference to the Id Users column, and are there any indexes in these columns?

If the cascade is set to NO_ACTION , as you indicated, SQL Server can spend time, which needs to perform a full scan of the table in each of these tables to make sure that there is no reference to Id 95146 - I saw that it easily takes minutes at a time, if other tables are big.

Answer 2

It is very strange. I assume that you have an ON DELETE CASCADE foreign key referencing your users table elsewhere in your schema. Check your limitations:

 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 

Answer 3

I struggled with the DELETE , which caused our ERP (Epicor 10) system to time out on its MRP night course. This was a delete with several joins in it, and one of the tables involved in the join is quite large. The strange thing was that if I converted delete to SELECT * for the same clins / where clause, the query would end instantly and have ZERO results (i.e., the delete operator obviously performed some long large table scan, but it actually found nothing to delete).

What ultimately helped was that I ran the selected version of its statute, displaying the actual execution plan, and it was offered a non-unique index to be added to one of the tables. After I added this index, the select statement was still executed instantly, but now the delete statement is done!

Answer 4

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

Also, in SQL Server Management Studio, enter this expression and see a sample execution plan. You may be able to see what SQL Server is trying to do.

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

Answer 5

In addition to the other answers, maybe your DELETE is blocked by some other action (see if there is any value in the BlkBy sp_who2 column for your spid), or maybe there is a trigger in the table that does something unhealthy . Most of these things slow down deletion, but, except in extremely difficult situations, are not significant enough to cause a timeout.