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 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 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 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…