sys.dm_db_xtp_gc_cycle_stats (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Outputs the current state of committed transactions that deleted one or more rows. The idle garbage collection thread wakes every minute or when the number of committed DML transactions exceeds an internal threshold since the last garbage collection cycle. As part of the garbage collection cycle, committed transactions move into one or more queues associated with generations. The transactions that generated stale versions are grouped in a unit of 16 transactions across 16 generations as follows:
Generation-0: This stores all transactions that committed earlier than the oldest active transaction. Row versions generated by these transactions are immediately available for garbage collection.
Generations 1-14: Stores transactions with timestamp greater than the oldest active transaction. The row versions cannot be garbage collected. Each generation can hold up to 16 transactions. A total of 224 (14 * 16) transactions can exist in these generations.
Generation 15: The remaining transactions with timestamp greater than the oldest active transaction go to generation 15. Similar to generation-0, there is no limit of number of transactions in generation-15.
When there is memory pressure, the garbage collection thread updates the oldest active transaction hint aggressively, which forces garbage collection.
For more information, see In-Memory OLTP (In-Memory Optimization).
| Column name | Type | Description |
|---|---|---|
cycle_id |
bigint | A unique identifier for the garbage collection cycle. |
ticks_at_cycle_start |
bigint | Ticks at the time the cycle started. |
ticks_at_cycle_end |
bigint | Ticks at the time the cycle ended. |
base_generation |
bigint | The current base generation value in the database. This represents the timestamp of the oldest active transaction used to identify transactions for garbage collection. The oldest active transaction ID is updated in the increment of 16. For example, if you have transaction IDs as 124, 125, 126 ... 139, the value is 124. When you add another transaction, for example 140, the value is 140. |
xacts_copied_to_local |
bigint | The number of transactions copied from the transaction pipeline into the database's generation array. |
xacts_in_gen_0 - xacts_in_gen_15 |
bigint | Number of transactions in each generation. |
Permissions
Requires VIEW DATABASE STATE permission on the database.
Permissions for SQL Server 2022 and later
Requires VIEW DATABASE PERFORMANCE STATE permission on the database.
Usage Scenario
Here is a sample output with a subset of columns, showing 27 generations:
cycle_id ticks_at_cycle_start ticks_at_cycle_end base_generation xacts_in_gen_0 xacts_in_gen_1
1 123160509 123160509 1 0 0
2 123176822 123176822 1 0 1
3 123236826 123236826 1 0 1
4 123296829 123296829 1 0 1
5 123356832 123356941 129 0 0
6 123357473 123357473 129 0 0
7 123417486 123417486 129 0 0
8 123477489 123477489 129 0 0
9 123537492 123537492 129 0 0
10 123597500 123597500 129 0 0
11 123657504 123657504 129 0 0
12 123717507 123717507 129 0 0
13 123777510 123777510 129 0 0
14 123837513 123837513 129 0 0
15 123897516 123897516 129 0 0
16 123957516 123957516 129 0 0
17 124017516 124017516 129 0 0
18 124077517 124077517 129 0 0
19 124137517 124137517 129 0 0
20 124197518 124197518 129 0 0
21 124257518 124257518 129 0 0
22 124317523 124317523 129 0 0
23 124377526 124377526 129 0 0
24 124437529 124437529 129 0 0
25 124497533 124497533 129 0 0
26 124557536 124557536 129 0 0
27 124617539 124617539 129 0 0