question

Slick84 avatar image
Slick84 asked

Deleting millions of rows

I was wondering if I can get some pointers on the strategies involved in deleting large amounts of rows from a table. The table is a highly transactional table in a OLTP environment and we need to delete 34 million rows from it. I thought about writing out the keys to another table and then deleting during slow hours in batches. Any other thoughts, idea's would be appreciated. Thanks
administrationdeleteoltp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
If your table is not partitioned, then the deletion in batches in the easiest and probably preferable way. In case the table is partitioned then there could be basically 3 scenarios: 1. All data in a partition needs to be deleted - simply switch out the partition to a temp table and drop 2. Most of the data needs to be delete from partition - switch out the partition, move correct data to other temp table, drop original switched out partition and switch in partition with correct data. 3. Some of the data needs to be deleted from partition - delete data in batches. **EDIT:** An example of DELETE loop with 2 seconds delay between loops: DECLARE @cnt int; SET @cnt = 1; WHILE @cnt > 0 BEGIN DELETE TOP (1000) aTable WHERE condition SET @cnt = @@ROWCOUNT WAITFOR DELAY '00:00:02' END
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - I'd go for a DELETE based on a WHILE EXISTS with a delay of a few seconds to let other processes settle. This will prevent the TLog from growing too big. Consider altering the backup schedules to keep the file sizes in workable values
6 Likes 6 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Instead of WHILE EXISTS I would check the @@ROWCOUNT as this saves you a processing power and IO. As exists will do a table/index scan/seek withing each loop. Especially for multi-million or multi-billion rows tables. Added an example into the answer.
1 Like 1 ·
Slick84 avatar image Slick84 commented ·
Thank you guys. Fatherjack, thats for your comment. I have devised some code that I might try to paste it here but its also based on the WHILE EXISTS logic. I think that's a good way to go. As for my table, its not a partitioned table.
0 Likes 0 ·
Jason Crider avatar image
Jason Crider answered
SET ROWCOUNT 10000 --DECLARE @Text varchar(255) --SET @Text = 'Deleted 10000 rows' DELETE FROM [database].[dbo].[table] WHERE DateChecked < '2011-01-01' WHILE @@rowcount > 0 BEGIN DELETE FROM [database].[dbo].[table] WHERE DateChecked < '2011-01-01' --PRINT (@Text) END SET ROWCOUNT 0 GO
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
On SQL Server 2005+ it's better to use **`DELETE TOP 10000`** instead of `SET ROWCOUNT 10000`. Also you can read on MSDN [SET ROWCOUNT (Transact-SQL)][1] > Using SET ROWCOUNT will not affect > DELETE, INSERT, and UPDATE statements > in the next release of SQL Server. Do > not use SET ROWCOUNT with DELETE, > INSERT, and UPDATE statements in new > development work, and plan to modify > applications that currently use it. > Also, for DELETE, INSERT, and UPDATE > statements that currently use SET > ROWCOUNT, we recommend that you > rewrite them to use the TOP syntax. > For more information, see DELETE > (Transact-SQL), INSERT (Transact-SQL), > or UPDATE (Transact-SQL). [1]: http://msdn.microsoft.com/en-us/library/ms188774.aspx
4 Likes 4 ·
Jason Crider avatar image Jason Crider commented ·
Thanks for that info Pavel.
0 Likes 0 ·
Slick84 avatar image Slick84 commented ·
I am not sure what's going on here.. Im "upvoting" and the counter keeps going down!!!
0 Likes 0 ·
homebrew01 avatar image
homebrew01 answered
I do something similar to the earlier posts, but I also add t-log backups. I create a separate counter, for example every 50 batches I run a t-log backup. Experiment a bit to see how frequently you want to run it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.