sys.dm_tran_top_version_generators – Day 53 – One DMV a Day

Hello Geeks and welcome to the Day 53 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.

A week of travel and still continuing with the series. Follow my action in last week while I kept this series going. Today I will be covering sys.dm_tran_top_version_generators. As the name of the DMV suggests, this gives the details of transaction which generated high version store records.

Sys.dm_tran_top_version_generators queries sys.dm_tran_version_store grouping on database id and rowset id. So be mindful when running this query if you have huge version store. This can be checked by using sys.dm_db_file_space_usage which I blogged on Day 8.

Let us run the three transactions on two different databases to generate three sets of version records.

Session 1:

--Session 1
USE AdventureWorks2014
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
BEGIN TRAN

UPDATE Person.Person
SET PersonType = PersonType

--COMMIT
--output: (19972 row(s) affected)

Session 2:

--Session 2
USE AdventureWorks2014
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
BEGIN TRAN

UPDATE Person.Address
SET city = city

--COMMIT
--output: (19614 row(s) affected)

Session 3:

--Session 3
USE Repl_Pub
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
BEGIN TRAN

UPDATE publogger_tbl
SET eName = eName

--COMMIT
--output: (435000 row(s) affected)

Now let us see the top version generators using sys.dm_tran_top_version_generators.

SELECT * FROM sys.dm_tran_top_version_generators

We can see from the output that the top version store generator in this case was for rowset_id 72057594047102976. Now to get the object to which these versions belong you can run the below query.

USE AdventureWorks2014
GO
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS TableName 
FROM sys.partitions p
INNER JOIN sys.objects o
ON o.object_id = p.object_id
WHERE hobt_id = 72057594047102976

So this belongs to Person.Person table. Evident enough. 🙂

Tomorrow I will cover another DMV.  Signing off from NYC…

Happy Learning,
Manu

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