Delete a huge amount of data from a SQL Server table

Data amounts  are growing , causing a faster processing requirement for DELETE operation . How is it possible to make the DELETE functionality faster  in table with a very large amount of data?

The DELETE dml operation does not support bulk logging  , so using the following  pattern  will exploit the bulk logging of  SELECT … INTO and INSERT INTO … SELECT.

Step 1 – Identify the data which won’t be deleted and isolate with SELECT … INTO

Step 2 – Use TRUNCATE to prepare table. Truncate removes all records from the table without logging each delete separately

Step 3 – Complete operation by INSERT INTO... SELECT and the TABLOCK table hint

For further tips on optimising DELETE in different scenarios – read SQL Server – Optimize delete from table - delete in sql

Read More

SQL Server Query Optimizer and Statistics - SQL Server DBA

Find queries causing Lock Timeouts per - SQL Server DBA

SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

Data Loading Performance Guide - SQL Server DBA