skreebydba

In my last post, I wrote about SQL Server reserving space for open transactions to account for potential rollback. This reserved space is released once the transaction commits. You can track the amount of space reserved using the sys.dm_tran_database_transactions DMO. In this post, I will demonstrate how expensive this space reservation can be for long-running transactions.

I created two databases to allow me to track log space reservation for two processes. The first will update all values in a 100,000-row tables 10 times, running all updates within single transaction. The second will run each update in an explicit transaction. Here is the script to create the database:

CREATE DATABASE [DUDLOOPTRAN]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DUDLOOPTRAN_Data', FILENAME = N'D:\Data\DUDLOOPTRAN_01.mdf' , SIZE = 2097152KB , FILEGROWTH = 1048576KB )
 LOG ON 
( NAME = N'DUDLOOPTRAN_log', FILENAME = N'D:\Data\DUDLOOPTRAN_01.ldf' , SIZE = 8388608KB , FILEGROWTH = 2097152KB )
GO
ALTER DATABASE [DUDLOOPTRAN] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [DUDLOOPTRAN] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET ARITHABORT OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DUDLOOPTRAN] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DUDLOOPTRAN] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DUDLOOPTRAN] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET  DISABLE_BROKER 
GO
ALTER DATABASE [DUDLOOPTRAN] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DUDLOOPTRAN] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [DUDLOOPTRAN] SET  READ_WRITE 
GO
ALTER DATABASE [DUDLOOPTRAN] SET RECOVERY FULL 
GO
ALTER DATABASE [DUDLOOPTRAN] SET  MULTI_USER 
GO
ALTER DATABASE [DUDLOOPTRAN] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DUDLOOPTRAN] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [DUDLOOPTRAN]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [DUDLOOPTRAN] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

CREATE DATABASE [DUDONETRAN]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DUDONETRAN_Data', FILENAME = N'D:\Data\DUDONETRAN_01.mdf' , SIZE = 2097152KB , FILEGROWTH = 1048576KB )
 LOG ON 
( NAME = N'DUDONETRAN_log', FILENAME = N'D:\Data\DUDONETRAN_01.ldf' , SIZE = 8388608KB , FILEGROWTH = 2097152KB )
GO
ALTER DATABASE [DUDONETRAN] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [DUDONETRAN] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DUDONETRAN] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DUDONETRAN] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DUDONETRAN] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DUDONETRAN] SET ARITHABORT OFF 
GO
ALTER DATABASE [DUDONETRAN] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DUDONETRAN] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DUDONETRAN] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DUDONETRAN] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DUDONETRAN] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DUDONETRAN] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DUDONETRAN] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DUDONETRAN] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DUDONETRAN] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DUDONETRAN] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DUDONETRAN] SET  DISABLE_BROKER 
GO
ALTER DATABASE [DUDONETRAN] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DUDONETRAN] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DUDONETRAN] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DUDONETRAN] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [DUDONETRAN] SET  READ_WRITE 
GO
ALTER DATABASE [DUDONETRAN] SET RECOVERY FULL 
GO
ALTER DATABASE [DUDONETRAN] SET  MULTI_USER 
GO
ALTER DATABASE [DUDONETRAN] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DUDONETRAN] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [DUDONETRAN]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [DUDONETRAN] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

And this is the code to create the tables:

USE DUDLOOPTRAN
GO

-- Create an populate a table in each database to use in the update loops

CREATE TABLE looptranu
(runnumber INT
,rundate DATETIME
,vartext VARCHAR(10)
,chartext CHAR(10))

USE DUDONETRAN
GO

CREATE TABLE onetranu
(runnumber INT
,rundate DATETIME
,vartext VARCHAR(10)
,chartext CHAR(10))

USE DUDLOOPTRAN
GO

INSERT INTO looptranu
VALUES
(1
,GETDATE()
,REPLICATE('a',10)
,REPLICATE('b',10))
GO 100000

USE DUDONETRAN
GO

INSERT INTO onetranu
VALUES
(1
,GETDATE()
,REPLICATE('a',10)
,REPLICATE('b',10))
GO 100000

After creating the databases and tables, I ran the following scripts. First, this script runs the updates in a single transaction:

USE DUDONETRAN
GO

-- Declare @loopcount variable to run the loop
-- Declare @textlen to vary the length of the text fields

DECLARE @loopcount INT
DECLARE @textlen TINYINT
SET @loopcount = 1

-- Begin an explicit transaction that will remain open for the duration of the loop
BEGIN TRAN

WHILE @loopcount <= 10
BEGIN

	-- Use the modulus operator to set text length to the remainder of @loopcount / 10

	SET @textlen = (@loopcount % 10)
	
	-- Update onetranu using the values described below
	UPDATE onetranu
	-- Set runnumber equal to @loopcount
	SET runnumber = @loopcount, 
	-- Set rundate equal the current datetime
	rundate = GETDATE(), 
	-- Set vartext to a string of a's, with the length determined by the @textlen variable
	vartext = REPLICATE('a',@textlen), 
	-- Set chartext to a string of b's, with the length determined by the @textlen variable
	chartext = REPLICATE('b',@textlen)

	-- Increment @loopcount
	SET @loopcount = @loopcount + 1

END

COMMIT

This code does each update in an explicit transaction:

USE DUDLOOPTRAN
GO

-- Declare @loopcount variable to run the loop
-- Declare @textlen to vary the length of the text fields

DECLARE @loopcount INT
DECLARE @textlen TINYINT
SET @loopcount = 1

-- Begin an explicit transaction that will remain open for the duration of the loop


WHILE @loopcount <= 10
BEGIN
	
	BEGIN TRAN
	-- Use the modulus operator to set text length to the remainder of @loopcount / 10

	SET @textlen = (@loopcount % 10)
	
	-- Update onetranu using the values described below
	UPDATE looptranu
	-- Set runnumber equal to @loopcount
	SET runnumber = @loopcount, 
	-- Set rundate equal the current datetime
	rundate = GETDATE(), 
	-- Set vartext to a string of a's, with the length determined by the @textlen variable
	vartext = REPLICATE('a',@textlen), 
	-- Set chartext to a string of b's, with the length determined by the @textlen variable
	chartext = REPLICATE('b',@textlen)

	-- Increment @loopcount
	SET @loopcount = @loopcount + 1
	
	COMMIT
	
END

Finally, this code will insert the contents of sys.dm_tran_database_transactions into a temp table for analysis. Run this while the update scripts are running:

USE master
GO

-- Create a temp table to hold the result set from sys.dm_tran_database_transactions

CREATE TABLE #transactions
(tranid BIGINT
,databaseid INT
,trantype INT
,transtate INT
,recordcount INT
,bytesused BIGINT
,bytesreserved BIGINT)

-- Run an infinite loop for the duration of each update script
-- stopping it when the script completes

WHILE 1 = 1
BEGIN

	INSERT INTO #transactions
	SELECT transaction_id AS [Tran ID], 
	database_id AS [Database ID], 
	database_transaction_type AS [Tran Type], 
	database_transaction_state AS [Tran State], 
	database_transaction_log_record_count AS [Log Record Count],
	database_transaction_log_bytes_used AS [Log Bytes Used],
	database_transaction_log_bytes_reserved AS [Log Bytes Reserved]
	FROM sys.dm_tran_database_transactions

END

-- Use the select statement below to see the results
-- This query will return a single row per transaction
-- for read/write activity (trantype = 1)
-- that has generated log records (transtate = 4)

SELECT tranid, 
MAX(recordcount) AS [Record Count], 
(MAX(bytesused)/1045876) AS [MB Used], 
(MAX(bytesreserved)/1045876) AS [MB Reserved] 
FROM #transactions
WHERE databaseid = DB_ID('DUDONETRAN')  -- Make sure to use the correct database name
AND trantype = 1
AND transtate = 4
GROUP BY tranid

-- Clean up the temp table after the run

--DROP TABLE #transactions

Here are the results of the single transaction:

And here are the results of the explicit transactions in the loop:


 
As you can see, the total space used and reserved is approximately the same. The difference is the updates running in explicit transactions release the reserved space when each transaction commits. While the total reserved space for the explicit transaction is 344mb, 36mb is the most space reserved at any one time. However, the 342mb reserved for the single transaction is held for the duration of the transaction.

The examples I've shown here update 100,000 narrow rows 10 times. The cost of running updates or inserts to much wider rows millions of times would be exponentially greater. I hope these examples have demonstrated this.