Delete a Large Amount of Data in Batches

                There may be times when you need to delete a large amount of data and there isn’t the comfort of a large maintenance window, or you just simply can’t wait for some down time in production.  Issuing a basic DELETE FROMcould potentially cause contention and drastically minimize concurrency for the given time of the data delete.  With just a few rows of data, this is typically no big deal and the end users will be none-the-wiser.  But with a large amount of data to be deleted, it could have a great impact on the users, and performance in general.  One solution is to delete the data in batches.  As a set-based proponent, this is one of those times where we could really benefit from a loop.
                I want to create some test data to do our experimenting on (I know this goes without saying, but only run a variation of this code in production after extensive testing in your particular environment).  I’m using a database called TestDB, so feel free to create this database or use your own test database (it is not dependent on any objects, so it can be a newly created DB).
use TestDB
go
drop table LargeDeleteTest
go
create table LargeDeleteTest
(
       id int identity(1, 1) not null
              primary key clustered,
       SomeData nvarchar(128) not null
              default replicate(‘a’, 128)
)
go
insert into LargeDeleteTest
values(default)
go 1250
                So now we have a test table (LargeDeleteTest) with 1250 rows of data in it.  I am over-simplifying this scenario, as in the grand scheme of data this is negligible.  But as we go through this example, apply this thought process to millions upon millions of rows that need to be deleted.
                Let’s demonstrate what it’ll look like if you don’t delete data in batches.  By executing the below code, notice the return of the DMV sys.dm_tran_locks query:
begin tran
       delete
       from LargeDeleteTest
–commit tran
select
       resource_database_id,
       resource_associated_entity_id,
       request_session_id,
       request_mode,
       request_type,
       request_status,
       resource_type,
       count(*) as resource_count
from sys.dm_tran_locks
group by
       resource_database_id,
       resource_associated_entity_id,
       request_session_id,
       request_mode,
       request_type,
       request_status,
       resource_type
                We get an expected 1250 exclusive key locks on the clustered index of our LargeDeleteTest table.  This is 100% of the table.  In other words, until this DELETEtransaction is committed, there is an exclusive lock on all of that data.  (Note: run the commented out COMMIT TRANin order to commit that transaction and not hinder our experimentation further down this post)
                Here’s some code to show you how to delete data in batches to skirt around the above concurrency nightmare:
declare
       @batch_size int,
       @del_rowcount int = 1
set @batch_size = 100
set nocount on;
while @del_rowcount > 0
       begin
              begin tran
                     delete top (@batch_size)
                     from dbo.LargeDeleteTest
                     set @del_rowcount = @@rowcount
                     print ‘Delete row count: ‘+ cast(@del_rowcount as nvarchar(32))
              commit tran
       end
                The code is very simple.  All it does it loops through and deletes the data in batches of 100 (I chose batch size to illustrate the point on a smaller scale, but as explained above you should alter this variable for your specific environment).  To continue with the details used above to show locks, execute the below code, which is basically just one iteration of the WHILE loop:
insert into LargeDeleteTest
values(default)
go 1250
begin tran
       delete top (100)
       from LargeDeleteTest
–commit tran
select
       resource_database_id,
       resource_associated_entity_id,
       request_session_id,
       request_mode,
       request_type,
       request_status,
       resource_type,
       count(*) as resource_count
from sys.dm_tran_locks
group by
       resource_database_id,
       resource_associated_entity_id,
       request_session_id,
       request_mode,
       request_type,
       request_status,
       resource_type
                Now in the result set of querying sys.dm_tran_lockswe see that we only have 100 exclusive key locks.  We see that concurrency has now been optimized due to deleting the data in batches, as the whole clustered index will not have an exclusive lock on all keys, just the current ones in the batch.
                As we’ve seen here, there are going to be times that we will need to delete a large amount of data, and it may not be the best idea to just do it in a single DELETE FROM statement.  Through the use of a loop and batch deletes, we can minimize the impact this will have on our underlying data’s accessibility.
                The batch delete code can be found on my GitHub SQLSalt repository here:  DML_Delete_DeleteLargeDataByBatches.sql.  If there are any comments, questions, or issues please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s