Transaction batching in SQL server Update and Delete queries

SQL server being pessimistic writes to the transaction log details of each record involved in  a DML  transaction until the transaction completes. If the SQL server is in Simple Recovery mode, it frees transaction log space once the transaction is completed with an automatic checkpoint.  

Problem 

 In case of some DML operations especially in data warehouse workloads, we might need to update or delete a BIG chunk of data which might require big transaction log space. If we go out of transaction log space, entire transaction will fail and it will take hell lot of time to roll it back. Also, entire database might go in In Recovery mode.

That is SAD :(.

Better we try to optimally utilize the resources than big transaction rollbacks.

Solution

Here comes the need of batching in DML operations.  It means, we would need to perform the DML operation in some equal sized batches which will suit our disk space needs.

What is Transaction Batching?

Dividing a bigger transaction into equal sized small transactions in order to optimally utilize the resources.

Below example illustrates the use of batching update & delete. Here, we have used set rowcount to limit the row size in a batch and @@ROWCOUNT function to check records deleted in a particular batch.

Batching in update

create table #temp ( id int identity(1,1) , name varchar(10), flag int ) --Table created insert into #temp (name) values ('abc'),('abc'),('abc'),                                   ('xyz'),('xyz'),('xyz') (6 row(s) affected) --Below script will update flag column to 1 when name = 'abc'. --This will happen in a batch of 2 records in one transaction. So, below script is --suppose to delete 3 records in 2 batches. declare @uppdatecount int = 1 --This variable will act as a terminating condition while @uppdatecount <> 0 begin        begin tran               set rowcount 2  --Records in a batch               update #temp               set flag = 1               where name = 'abc' and flag is null               set @uppdatecount = @@ROWCOUNT               set rowcount 0        commit tran        checkpoint  --Frees the transaction log space. End (2 row(s) affected) (1 row(s) affected) (0 row(s) affected)

Batching in delete

Similar to above example, let’s now try to delete data from #temp where flag = 1 in batches.

declare @deletecount int = 1  --This variable will act as a terminating condition while @deletecount <> 0 begin        begin tran               set rowcount 2  --Records in a batch               delete from #temp where flag = 1               set @deletecount = @@ROWCOUNT               set rowcount 0        commit tran        checkpoint  --Frees the transaction log space. end (2 row(s) affected) (1 row(s) affected) (0 row(s) affected)

Thank you for reading the post, please feel free to provide your feedback or suggestions. 

Happy reading :)