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.
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.
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
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:
Version Store space requirements
Further Version Store space requirements
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.
The version store details after cleanup
This new DMV makes it possible to store version store size information into a table, for tracking and forecasting in an easy manner.