Transaction Batching in SQL Server

SQL Server’s pessimistic approach to writing transaction log details during DML transactions can lead to challenges, especially in data warehouse workloads. Large-scale updates or deletions may require significant transaction log space, risking transaction failures and prolonged rollbacks. This post explores the concept of transaction batching in SQL Server as a solution to efficiently manage resources and avoid unnecessary rollbacks.

Problem

Performing extensive DML operations, particularly in data warehouse scenarios, may lead to transaction log space constraints, risking transaction failures and database downtime during rollbacks.

Solution

Transaction batching in SQL Server: Transaction batching, dividing larger transactions into smaller, equal-sized batches, helps optimize resources and minimize the impact of potential rollbacks.

Example

Consider a scenario where we batch updates and deletes in a temporary table named #temp. We use the SET ROWCOUNT to limit batch sizes and the @@ROWCOUNT function to track the number of records affected in each batch.

Batching Updates

CREATE TABLE #temp (
  id INT IDENTITY(1,1),
  name VARCHAR(10),
  flag INT
)

INSERT INTO #temp (name) VALUES 
  ('abc'), ('abc'), ('abc'),
  ('xyz'), ('xyz'), ('xyz')

DECLARE @updatecount INT = 1

WHILE @updatecount <> 0
BEGIN
  BEGIN TRAN
    SET ROWCOUNT 2
    UPDATE #temp
    SET flag = 1
    WHERE name = 'abc' AND flag IS NULL
    SET @updatecount = @@ROWCOUNT
    SET ROWCOUNT 0
  COMMIT TRAN
  CHECKPOINT -- Frees the transaction log space.
END

Batching Deletes

DECLARE @deletecount INT = 1

WHILE @deletecount <> 0
BEGIN
  BEGIN TRAN
    SET ROWCOUNT 2
    DELETE FROM #temp WHERE flag = 1
    SET @deletecount = @@ROWCOUNT
    SET ROWCOUNT 0
  COMMIT TRAN
  CHECKPOINT -- Frees the transaction log space.
END

Conclusion

By implementing transaction batching, we can efficiently manage transaction log space and mitigate the risks associated with large-scale DML operations. This approach optimizes resource utilization and reduces the likelihood of extensive rollbacks, ensuring smoother database operations.

Pro tips:
1. Learn how to find and manage deadlocks in SQL Server.

See more

Kunal Rathi

With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.