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 2:

Session 3:

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

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.

sys.dm_tran_top_version_generators

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