October 7, 2024 at 12:00 am
Comments posted to this topic are about the item How to Delete Large Amounts of Data in Microsoft SQL Server
October 7, 2024 at 9:13 am
For partitioned tables, we can TRUNCATE a partition without use partition switching.
TRUNCATE TABLE YourTable WITH ( PARTITIONS 2, 4 to 7)
October 7, 2024 at 12:58 pm
".. 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
October 7, 2024 at 3:39 pm
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...
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy