sys.dm_tran_version_store – Day 52 – One DMV a Day

Hello Geeks and welcome to the Day 52 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have covered sys.dm_tran_current_transaction. Today I will be talking about sys.dm_tran_version_store. This DMV returns a row for each record in the version store. This DMV is inefficient as it returns all the records in the version store. So do not run it unless you know what and why you are running it.

Sys.dm_tran_version_store gives the information related to the version record. Let us run two transactions with snapshot isolation level. We will see the version records generated for them.

First session:

USE AdventureWorks2012
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

UPDATE Person.Address
	 SET AddressLine1 = AddressLine1
WHERE AddressID = 1

--COMMIT

Second session:

USE AdventureWorks2012
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

UPDATE PERSON.PERSON
	 SET PersonType = PersonType
WHERE BusinessEntityID = 1

--COMMIT

Now we will see the output from sys.dm_tran_version_store.

SELECT * 
FROM sys.dm_tran_version_store
WHERE database_id = 5

sys.dm_tran_version_store

Based on the record length the version record can be split into two pages. If the length is more than 8192 bytes the second part of record image is not null in the above output. You can also use sys.dm_tran_version_store to get the number of version records or size of version store.

SELECT COUNT(1) AS VerRcrds,
	 SUM(record_length_first_part_in_bytes + record_length_second_part_in_bytes)/1024 AS VerSizeKB
FROM sys.dm_tran_version_store

sys.dm_tran_version_store

Tomorrow I will cover another transaction related DMV.  So stay tuned. Till then.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *