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
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
+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
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.
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
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
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.
