I’ve been reviewing Azure SQL Database performance guidance recently and came across Microsoft’s documentation on How to Use Batching to Improve Application Performance. While the article focuses on Azure SQL Database, the same principles of batching transactions for better performance apply equally well to SQL Server and Azure SQL Managed Instance.

The reason this topic caught my attention is that batching doesn’t just improve performance. It can reduce blocking, minimize rollback pain, improve transaction log efficiency, and potentially lower costs in cloud environments. That’s a pretty good return on investment for a relatively simple coding change. Here is the SQL Script that I use for this demonstration. Feel free to test for yourself. (It is a text file, so you will have to save it as a .sql file.)

Every Data Modification Is a Transaction

In previous blog posts I wrote about a dozen years ago, we discussed Working with Batches, Transactions and Errors, and Writing a Stored Procedure in SQL Server. But let’s revisit the concept of transactions.

Consider a bank transfer:

BEGIN TRAN
UPDATE Accounting.BankAccounts
SET Balance -= 200
WHERE AcctID = 1

UPDATE Accounting.BankAccounts
SET Balance += 200
WHERE AcctID = 2
COMMIT

Both statements succeed or neither does, and $200 doesn’t go missing! 

This is exactly what transactions are designed to do. But even a simple INSERT statement operates within a transaction. If you’re inserting 100,000 rows and row number 99,999 violates a constraint, SQL Server rolls back the entire operation. That’s great for consistency. It’s not always great for performance.

Why Transaction Size Matters

When you’re modifying large volumes of data, transaction size directly affects:

• Execution time
• Rollback duration
• Blocking and concurrency
• Transaction log growth
• Log write efficiency

In general, large data modifications tend to fall into one of three categories:

• Monolithic operations: One statement updates everything.
• RBAR (Row By Agonizing Row): Updates occur one row at a time using cursors or loops.
• Batched operations: Data is processed in manageable chunks.

Let’s look at each approach.

Option 1: The Monolithic Update

This is the easiest code to write:

UPDATE Sales.SalesDetail SET OrderQty += 1;

Simple. Unfortunately, simplicity isn’t always your friend. For smaller tables, this may be perfectly acceptable. For large tables, however, a single massive transaction can create several problems:

• Long-running locks
• Increased blocking
• Risk of lock escalation
• Large transaction log growth
• Painful rollback times if the operation must be canceled

The rollback issue is often the one that catches people by surprise. If a query runs for thirty minutes and someone decides to cancel it, don’t expect an immediate stop. SQL Server must undo all completed work before returning control, and that rollback can take as long as the original transaction. Nothing says “bad maintenance window” quite like watching a rollback run longer than the update you were trying to stop. If this is happening, considering checking out Accelerated Database Recovery.

Option 2: Row By Agonizing Row (RBAR)

At the opposite extreme is the cursor-based approach.

-- Update each row individually using a cursor
DECLARE RowsToUpdate 
CURSOR FOR 
SELECT DemoDetailId FROM Sales.SalesDetail;
       DECLARE @Id INT;
       OPEN RowsToUpdate;
       FETCH RowsToUpdate INTO @Id;
       WHILE (@@fetch_status = 0) 
       BEGIN
              UPDATE Sales.SalesDetail SET OrderQty += 1 WHERE DemoDetailId = @Id;
              FETCH RowsToUpdate INTO @Id;
       END;
CLOSE RowsToUpdate;
DEALLOCATE RowsToUpdate;

You know where this is going. The cursor fetches a row, updates a row, fetches another row, updates another row, and repeats thousands or millions of times. This approach typically generates:

• Excessive I/O
• Excessive logging
• Excessive CPU overhead
• Excessive waiting

Basically, excessive everything. You’re effectively running thousands of individual queries; each with a separate I/O cost, and each logged as a separate transaction that writes three rows to the transaction log for every time through the loop.

LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT

And to make matters worse, every COMMIT forces SQL Server to ensure the associated log records are safely persisted to disk. Although log buffers can hold up to 60 KB of transaction log data, thousands of tiny transactions rarely fill those buffers efficiently.

The result is a large number of small log writes, which is exactly what you see reflected in the log_writes and avg_write_kb values in the table below. That’s a lot of extra I/O for very little work per transaction, and it can have a significant impact on performance. The upside is that execution can be stopped at any point without rolling back work that has already been committed.

The one advantage RBAR provides is flexibility. You can stop processing immediately and retain work already completed. Blocking is often reduced because locks are short-lived and narrowly scoped. Unfortunately, the performance cost is usually far too high.

Option 3: Batching

This is where things get interesting. A well-designed batching strategy often delivers the best balance between performance, manageability, and recoverability. Instead of one huge transaction or thousands of tiny transactions, data is processed in reasonably sized chunks. A common starting point is 10,000 rows per batch:

DECLARE @min_id BIGINT = 1, @rows_updated INT = 1, @batch_size INT = 10000;
WHILE (@rows_updated > 0) 
BEGIN
    BEGIN TRAN;
              UPDATE Sales.SalesDetail
              SET OrderQty += 1
              WHERE DemoDetailId >= @min_id AND DemoDetailId < (@min_id + @batch_size);
              SET @rows_updated = @@ROWCOUNT;
              SET @min_id += @batch_size;
    COMMIT
END;

The ideal batch size varies based on workload, hardware, indexes, and data distribution, so testing is essential. However, the benefits are substantial:

• Faster overall execution
• Reduced blocking
• More efficient transaction logging
• Faster rollback when necessary
• Improved log truncation and reuse
• Lower risk of excessive log growth

Instead of generating thousands of separate transactions, a batch performs many modifications within a single transaction:

LOP_BEGIN_XACT
LOP_MODIFY_ROW
… another 9998 row modification records
LOP_MODIFY_ROW
LOP_COMMIT_XACT

The transaction-boundary overhead is reduced while still avoiding the risks that come with one massive transaction.

Don’t Forget About DELETE vs. TRUNCATE

While exploring transaction logging, it’s also worth remembering the difference between DELETE and TRUNCATE.

• A DELETE operation logs the row changes needed to remove qualifying data.
• TRUNCATE records page deallocations rather than processing rows individually.

When business requirements allow it, TRUNCATE can be dramatically faster and generate significantly less transaction log activity. It’s one of those simple changes that can have an outsized impact on performance.

The Takeaway

The rule of thumb is simple: If you are updating, deleting, or inserting a significant amount of data, test a batched approach before deploying your solution. You might be surprised how much faster, safer, and more scalable it becomes. And as always, measure everything. SQL Server has a way of reminding us that assuming is not tuning!

• A monolithic update may be perfectly fine for a small table.
• RBAR may occasionally be necessary for complex business logic.
• But for large-scale data modifications, batching is often the sweet spot.

This table is a great illustration of why batching is usually the sweet spot for large data modifications. These values were determined by using the SQL Script that I use for this demonstration of this post. (It is a text file, so save it as a .sql file).