Notes on SQL

Introduction
A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.

Background
In my previous article, monitoring the size of the version store was performed by using the perfmon counters, via Windows. This does present a nice graphical trace of the version store size but is of little use if you want to store the details – for example, in performance metrics for forecasting storage requirements.

Test Environment
For this example I have created two databases (to demonstrate that the DMV shows the information per database) – ‘VersionStoreTest’ and ‘AnotherVersionStoreTest’. Within each database I’ve created a very simple table, with an ID and a sequential value. Each table has several million rows, so the transactions will take enough resources to be interesting.

Firstly, the databases need to have a suitable isolation level set:

Listing 1: Set the required isolation level

ALTER DATABASE VersionStoreTest
SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE AnotherVersionStoreTest
SET READ_COMMITTED_SNAPSHOT ON;
GO

Test Execution
Within SSMS two tabs execute an update against rows of each database, in a WHILE loop – four tabs in all:

listing 2: The first update script for database ‘VersionStoreTest’

USE VersionStoreTest;
GO

WHILE 1 = 1
	BEGIN
		UPDATE dbo.Test1
		SET	   TestValue += 1
		WHERE  ID % 10 = 0;

		WAITFOR DELAY '00:00:01';
	END;

listing 3: The second update script for database ‘VersionStoreTest’

USE VersionStoreTest;
GO

WHILE 1 = 1
	BEGIN
		UPDATE dbo.Test1
		SET	   TestValue += 1
		WHERE  ID % 3 = 0;

		WAITFOR DELAY '00:00:01';
	END;

listing 4: The first update script for database ‘AnotherVersionStoreTest’

USE AnotherVersionStoreTest;
GO

WHILE 1 = 1
	BEGIN
		UPDATE dbo.Test2
		SET	   TestValue += 1
		WHERE  id % 5 = 0;

		WAITFOR DELAY '00:00:01';
	END;

listing 5: The second update script for database ‘AnotherVersionStoreTest’

USE AnotherVersionStoreTest;
GO

WHILE 1 = 1
	BEGIN
		UPDATE dbo.Test2
		SET	   TestValue += 1
		WHERE  id % 8 = 0;

		WAITFOR DELAY '00:00:01';
	END;

These scripts are merely updating certain rows in a continual loop, with a delay of one second between execution.

Another script will execute the DMV in a continual loop, to show the changing information relating to the version store.

Listing 6: executing the version store DMV

USE master
GO

WHILE 1 = 1
	BEGIN
		SELECT DB_NAME(database_id) AS 'Database Name' ,
			   reserved_page_count ,
			   reserved_space_kb
		FROM   sys.dm_tran_version_store_space_usage;
	END;

All rather primitive but it will do the job.

Now execute the four update scripts together and the script to query the DMV.

The DMV will show the changes in the reserved page count and space, for the version store for each database as the queries progress:

Cancel the execution of all of the scripts and waiting a short while, which allows the version store cleanup to execute.

Executing the DMV again will show that the version store is now empty.

Conclusion

This new DMV makes it possible to store version store size information into a table, for tracking and forecasting in an easy manner.

References
sys-dm-tran-version-store-space-usage
Understanding Row Versioning-Based Isolation Levels