How to Delete Large Amounts of Data in Microsoft SQL Server

Question

Post reply

How to Delete Large Amounts of Data in Microsoft SQL Server

AR

SSC Veteran

Points: 227
More actions
October 7, 2024 at 12:00 am

#4456650
Comments posted to this topic are about the item How to Delete Large Amounts of Data in Microsoft SQL Server
Ankush Rastogi
"Turning data into insights, one query at a time."
LinkedIn | GitHub
Got a SQL question? Feel free to ask!SELECT 'Have a great day!' AS Greeting;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

carsten.saastamoinen Old Hand Points: 341 More actions · Answer 1

For partitioned tables, we can TRUNCATE a partition without use partition switching.

TRUNCATE TABLE YourTable WITH ( PARTITIONS 2, 4 to 7)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

".. For very large deletions, consider disabling indexes and constraints before deleting, then rebuilding them after: .."

Dropping non-clustered indexes and constraints can improve performance of bulk deletes - but you still want to keep any index that supports the predicates in your WHERE clause. This is especially true when deleting multiple batches within a loop.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 3

Good idea for an article but there are a few issues... here's one of them...

Here's your "Disable the Indexes" example...

-- Disable indexes
ALTER INDEX ALL ON YourTable DISABLE
-- Perform deletion
DELETE FROM YourTable WHERE [your condition]
-- Rebuild indexes
ALTER INDEX ALL ON YourTable REBUILD

To add what Eric M Russell said above, if you disable the Clustered Index, there will be no access to the data.

I have a large table that I was testing with and used your code (with table name changes, of course) to test the code. Here's that code....

 ALTER INDEX ALL ON dbo.BigTable DISABLE;
DELETE top (10000) from dbo.BigTable;

Here's the result of that code...

Msg 8655, Level 16, State 1, Line 3

The query processor is unable to produce a plan because the index 'CI_ByTransDTProductID' on table or view 'BigTable' is disabled.

In the Batch Delete code, two changes will help...

Changing the value of when to BREAK will prevent the very expensive last pass where nothing is deleted (except in 1 instance).
Adding a delay at the bottom of the loop will guarantee that other things can use the table, which was the whole purpose of deleting in batches to begin with. I used 5 seconds in the code below but a second would probably do for letting things in.
As a sidebar (not included in the code below), if the deletes are being done for temporal reasons, doing them in temporal order would allow other parts of the table to be used while the beginning of the table is slowly eroded away.
Another sidebar might be to use OPTION(RECOMPILE) for every 10th or so batch because things will gradually slow down because the data is being deleted and the stats need to occasionally be updated.

WHILE 1 = 1
BEGIN
    DELETE TOP (10000) FROM YourTable
    WHERE [your condition];
    
    IF @@ROWCOUNT < 10000 BREAK; --If <10000 rows were deleted, then we're done.
    WAITFOR DELAY '00:00:05'; --<<< Allows other things to happen.
END;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)