Delete operation in SQL is very slow

Question

I have statements that fail:

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

I tried doing this at a time:

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

and still he's at 22 minutes and still walking.

The table contains 260,000 rows and four columns.

Does anyone have any ideas why this will be so slow and how to speed it up? I have a non-unique, non-clustered index in [COL] in which I am doing WHERE on. I am using SQL Server 2008 R2

update: I do not have triggers in the table.

Answer 1

Things that can slow down removal:

• delete a large number of records
• many indices
• There are no indexes for foreign keys in child tables. (thanks @CesarAlvaradoDiaz for mentioning this in the comments)
• locks and locks
• Triggers
• cascade deletion (the ten parent records you delete may mean millions of child records are deleted)
• Transaction log should grow
• Many foreign keys to verify

So, your choice is to find out what blocks and fix, or to start the removal after hours, when they will not interfere with the normal production load. You can start deletion in batches (useful if you have triggers, cascading deletions, or a large number of records). You can drop and recreate indexes (best if you can also do this after hours).

Answer 2

• Disable CONSTRAINT

  ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

• Disable index

  ALTER INDEX ALL ON [TableName] DISABLE;

• Adjustment Index

  ALTER INDEX ALL ON [TableName] REBUILD;

• Enable CONSTRAINT

  ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

• Delete again

Answer 3

Deleting multiple lines can be very slow. Try to delete several at a time, for example:

 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

Use SQL Profiler root cause of this problem. Maybe Triggers cause a delay in execution. It could be anything. Remember to select Database Name and Object Name when starting Trace to eliminate unnecessary scan requests ...

Database Name Filtering

Filter table / stored procedure / trigger names

Corrective action

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

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

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

Answer 5

If the table you are deleting has BEFORE / AFTER DELETE triggers, something there might cause a delay.

In addition, if you have foreign keys that reference this table, additional UPDATE or DELETE may occur.

Answer 6

It is possible that other tables have an FK restriction for your [table]. Therefore, to check the referential integrity of the database, you need to check these tables. Even if you have all the necessary indexes matching these FKs, check their number.

I had a situation where NHibernate incorrectly created duplicated FK s in the same columns, but with different names (which is allowed by SQL Server). This drastically reduced the work of the DELETE statement.

Answer 7

In my case, the database statistics got corrupted. Statement

 delete from tablename where col1 = 'v1' 

took 30 seconds, although there was no corresponding record, but

 delete from tablename where col1 = 'rubbish' 

ran instantly

works

 update statistics tablename 

fixed the problem

Answer 8

Is [COL] really a character field that stores 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 would be better off:

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

But it’s better to use numbers, not strings, to find strings (numbers similar to sql):

  DELETE FROM [table] WHERE [COL] = 1 

Perhaps try:

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

In any case, make sure you have an index in the [COL] column to speed up the table scan.

Answer 9

Check the execution plan of this delete statement. See if index search is used. Also, what is the col data type?

If you are using the wrong data type, change the update statement (for example, from "1" to "1" or "N1").

If index scanning is used, use the prompt.

Answer 10

I read this article, it was really useful for eliminating any inconvenience.

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

this is a waitresource case 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 delete all the records in the table, rather than selecting several, it can be much faster to just drop and recreate the table.

Answer 12

After checking the SSIS package (due to the fact that SQL Server was executing commands very slowly), which was installed on our client about 5-4 years before I wrote this, I found that the following tasks exist: 1) Insert data from an XML file into a table named [Importbarcdes].

2) combine the team with another target table using the above table as a source.

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

After quickly checking all the statements (SELECT, UPDATE, DELETE, etc.) in the ImportBarcodes table, which has only 1 row, the execution took about 2 minutes.

Extended events showed many PAGEIOLATCH_EX pending notifications.

There are no indexes in the table and no triggers are registered.

After a thorough study of the properties of the table on the "Storage" tab and in the general section in the "Data Space" field, more than 6 GBA of space allocated in the pages was shown.

What happened:

Over the past 4 years, the query has been executed for a considerable time every day, inserting and deleting data into the table, leaving unused page files without releasing them.

Thus, this was the main reason for the wait events that were captured by the extended event session and the slowly executing commands on the table.

Running ALTER TABLE ImportBarcodes REBUILD problem, freeing up all unused space. TRUNCATE TABLE ImportBarcodes did the same, with the only difference being that it deleted all the paging files and data.

Answer 13

open CMD and execute these commands

 NET STOP MSSQLSERVER NET START MSSQLSERVER 

this will restart the instance of SQL Server. try again after the delete command

I have this command in a batch script and run it from time to time if I encounter similar problems. A normal PC restart will not be the same, so restarting the instance is the most efficient way if you encounter some problems with your sql server.