Delete statement in SQL is very slow

Question

I have statements like this that are timing out:

DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')

I tried doing one at a time like this:

DELETE FROM [table] WHERE [COL] IN ( '1' )

and so far it's at 22 minutes and still going.

The table has 260,000 rows in it and is four columns.

Does anyone have any ideas why this would be so slow and how to speed it up? I do have a non-unique, non-clustered index on the [COL] that i'm doing the WHERE on. I'm using SQL Server 2008 R2

update: I have no triggers on the table.

Answer 1

Things that can cause a delete to be slow:

• deleting a lot of records
• many indexes
• missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
• deadlocks and blocking
• triggers
• cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

Answer 2

1. Disable CONSTRAINT

   ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

2. Disable Index

   ALTER INDEX ALL ON [TableName] DISABLE;

3. Rebuild Index

   ALTER INDEX ALL ON [TableName] REBUILD;

4. Enable CONSTRAINT

   ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

5. Delete again

Answer 3

Deleting a lot of rows can be very slow. Try to delete a few at a time, like:

delete top (10) YourTable where col in ('1','2','3','4')
while @@rowcount > 0
    begin
    delete top (10) YourTable where col in ('1','2','3','4')
    end

Answer 4

Preventive Action

Check with the help of SQL Profiler for the root cause of this issue. There may be Triggers causing the delay in Execution. It can be anything. Don't forget to Select the Database Name and Object Name while Starting the Trace to exclude scanning unnecessary queries...

Database Name Filtering

Table/Stored Procedure/Trigger Name Filtering

Corrective Action

As you said your table contains 260,000 records...and IN Predicate contains six values. Now, each record is being search 260,000 times for each value in IN Predicate. Instead it should be the Inner Join like below...

Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id

Insert the IN Predicate values into a Temporary Table or Local Variable

Answer 5

In my case the database statistics had become corrupt. The statement

delete from tablename where col1 = 'v1' 

was taking 30 seconds even though there were no matching records but

delete from tablename where col1 = 'rubbish'

ran instantly

running

update statistics tablename

fixed the issue

Answer 6

If the table you are deleting from has BEFORE/AFTER DELETE triggers, something in there could be causing your delay.

Additionally, if you have foreign keys referencing that table, additional UPDATEs or DELETEs may be occurring.

Answer 7

It's possible that other tables have FK constraint to your [table]. So the DB needs to check these tables to maintain the referential integrity. Even if you have all needed indexes corresponding these FKs, check their amount.

I had the situation when NHibernate incorrectly created duplicated FKs on the same columns, but with different names (which is allowed by SQL Server). It has drastically slowed down running of the DELETE statement.

Answer 8

Check execution plan of this delete statement. Have a look if index seek is used. Also what is data type of col?

If you are using wrong data type, change update statement (like from '1' to 1 or N'1').

If index scan is used consider using some query hint..

Answer 9

Is [COL] really a character field that's holding numbers, or can you get rid of the single-quotes around the values? @Alex is right that IN is slower than =, so if you can do this, you'll be better off:

DELETE FROM [table] WHERE [COL] = '1'

But better still is using numbers rather than strings to find the rows (sql likes numbers):

 DELETE FROM [table] WHERE [COL] = 1

Maybe try:

 DELETE FROM [table] WHERE CAST([COL] AS INT) = 1

In either event, make sure you have an index on column [COL] to speed up the table scan.

Answer 10

I read this article it was really helpful for troubleshooting any kind of inconveniences

https://support.microsoft.com/en-us/kb/224453

this is a case of waitresource KEY: 16:72057595075231744 (ab74b4daaf17)

-- First SQL Provider to find the SPID (Session ID)

-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57

select * from sys.databases where database_id=16

-- with Waitresource check this to obtain object id 
select * from sys.partitions where hobt_id=72057595075231744

select * from sys.objects where object_id=2105058535

Answer 11

If you're deleting all the records in the table rather than a select few it may be much faster to just drop and recreate the table.

Answer 12

After inspecting an SSIS Package(due to a SQL Server executing commands really slow), that was set up in a client of ours about 5-4 years before the time of me writing this, I found out that there were the below tasks: 1) insert data from an XML file into a table called [Importbarcdes].

2) merge command on an another target table, using as source the above mentioned table.

3) "delete from [Importbarcodes]", to clear the table of the row that was inserted after the XML file was read by the task of the SSIS Package.

After a quick inspection all statements(SELECT, UPDATE, DELETE etc.) on the table ImportBarcodes that had only 1 row, took about 2 minutes to execute.

Extended Events showed a whole lot PAGEIOLATCH_EX wait notifications.

No indexes were present of the table and no triggers were registered.

Upon close inspection of the properties of the table, in the Storage Tab and under general section, the Data Space field showed more than 6 GIGABYTES of space allocated in pages.

What happened:

The query ran for a good portion of time each day for the last 4 years, inserting and deleting data in the table, leaving unused pagefiles behind with out freeing them up.

So, that was the main reason of the wait events that were captured by the Extended Events Session and the slowly executed commands upon the table.

Running ALTER TABLE ImportBarcodes REBUILD fixed the issue freeing up all the unused space. TRUNCATE TABLE ImportBarcodes did a similar thing, with the only difference of deleting all pagefiles and data.

Answer 13

Older topic but one still relevant. Another issue occurs when an index has become fragmented to the extent of becoming more of a problem than a help. In such a case, the answer would be to rebuild or drop and recreate the index and issuing the delete statement again.

Answer 14

As an extension to Andomar's answer, above, I had a scenario where the first 700,000,000 records (of ~1.2 billion) processed very quickly, with chunks of 25,000 records processing per second (roughly). But, then it starting taking 15 minutes to do a batch of 25,000. I reduced the chunk size down to 5,000 records and it went back to its previous speed. I'm not certain what internal threshold I hit, but the fix was to reduce the number of records, further, to regain the speed.

Answer 15

open CMD and run this commands

NET STOP MSSQLSERVER
NET START MSSQLSERVER

this will restart the SQL Server instance. try to run again after your delete command

I have this command in a batch script and run it from time to time if I'm encountering problems like this. A normal PC restart will not be the same so restarting the instance is the most effective way if you are encountering some issues with your sql server.