Delete millions of Records from very huge Table

Dear Friends, As you know, It’s the great option to delete millions or billions of records using batches transactions, in this the logs does not grow drastically and makes recovery faster in the event of failures.
Its my observation, some time if we use CHECKPOINT in each transaction, give very fruitful performance in production environment, because Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

Let see an example,
Please Note : Here one point have to keep in mind , if we are using any date comparisons to delete records, have to store that value in a variable, will be much faster than using in query’s WHERE directly. Suppose we have to delete 60 days old records, then setting the date initially would make a difference instead of calculating for each row.

DECLARE @DelDate DATETIME;
SET @DelDate = DATEADD(d, –60,GETDATE())
WHILE 1 = 1
    BEGIN
        — Choose records count as per your records size.
        DELETE TOP (1000) FROM MyTableName WHERE column_name<@DelDate
        IF @@ROWCOUNT< 1000 BREAK;
        CHECKPOINT;
    END

One more approach , it may be this operation can generate a lot of disk I/O, So its recommend that you put a “WAITFOR DELAY ‘time_to_pass'” statement in the loop to reduce the stress on your server. Waiting for even a quarter of a second between DELETE executions can reduce stress on your production environment, but its better in case of Production Server if records amount are large not so huge.

DECLARE @DelDate DATETIME;
SET @DelDate = DATEADD(d,-60,GETDATE());

WHILE 1 = 1
    BEGIN
     — Choose records count as per your records size.
        DELETE TOP (1000) FROM MyTableName WHERE column_name < @DelDate
        IF @@ROWCOUNT < 1000 BREAK;
        CHECKPOINT;
        WAITFOR DELAY ’00:00:00.250′ — wait for quarter second
    END

Hope you like this way to get better performance .