sys.dm_db_xtp_gc_cycle_stats – Day 74 – One DMV a Day

Hello Geeks and welcome to the Day 74 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 we have seen how to check the queues using sys.dm_xtp_gc_queue_stats. Today I will be covering how the transactions are kept track of using sys.dm_db_xtp_gc_cycle_stats. This DMV shows the current state of committed transactions which have deleted at least one row. These transactions are grouped into unit of 16 transactions across 16 generations.

The generations hold a specific set of transactions. Below is the grouping description of generations shown in sys.dm_db_xtp_gc_cycle_stats for each cycle.

Generation 0: The transactions committed before the oldest active transaction. The row versions of these transactions can be immediately collected.

Generation 1-14: The transactions greater than the oldest active transaction. These cannot be collected. Also each generation can hold up to 16 transactions. This makes it possible to hold 16*14 = 224 transactions.

Generation 15: The remaining transactions are accounted here. This has no limit but very high growing value may indicate huge open transactions.

Let us see the truncated version of sys.dm_db_xtp_gc_cycle_stats. I am running less number of transactions on my In-Memory tables and the transactions never go beyond Generation 1. In most of OLTP loads you will see this going into multiple Generations and even into Generation 15.

SELECT cycle_id,
FROM sys.dm_db_xtp_gc_cycle_stats


Two more columns which we should know from sys.dm_db_xtp_gc_cycle_stats are the base_generation and xacts_copied_to_local. Base_generation is the timestamp of oldest active transaction. This always increments with 16. The first value we see is 113. The next value it is updated to 113 + 16 = 129 + 16 = 145. Xacts_copied_to_local is the number of transactions which are copied into the generation array.

Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then

Happy Learning,

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


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 *