Edit

sys.dm_xtp_gc_queue_stats (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Outputs information about each garbage collection worker queue on the server, and various statistics about each. There is one queue per logical CPU.

The main garbage collection thread (the Idle thread) tracks updated, deleted, and inserted rows for all transactions completed since the last invocation of the main garbage collection thread. When the garbage collection thread wakes, it determines if the timestamp of the oldest active transaction has changed. If the oldest active transaction has changed, then the idle thread enqueues work items (in chunks of 16 rows) for transactions whose write sets are no longer needed. For example, if you delete 1,024 rows, you will eventually see 64 garbage collection work items queued, each containing 16 deleted rows. After a user transaction commits, it selects all enqueued items on its scheduler. If there are no enqueued items on its scheduler, the user transaction will search on any queue in the current NUMA node.

You can determine if garbage collection is freeing memory for deleted rows by executing sys.dm_xtp_gc_queue_stats to see if the enqueued work is being processed. If entries in the current_queue_depth are not being processed or if no new work items are being added to the current_queue_depth, this is an indication that garbage collection is not freeing memory. For example, garbage collection can't be done if there is a long running transaction.

For more information, see In-Memory OLTP (In-Memory Optimization).

Column name Type Description
queue_id int The unique identifier of the queue.
total_enqueues bigint The total number of garbage collection work items enqueued to this queue since the server started.
total_dequeues bigint The total number of garbage collection work items dequeued from this queue since the server started.
current_queue_depth bigint The current number of garbage collection work items present on this queue. This item may imply one or more to be garbage collected.
maximum_queue_depth bigint The maximum depth this queue has seen.
last_service_ticks bigint CPU ticks at the time the queue was last serviced.

Permissions

Requires VIEW SERVER STATE permission.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

User Scenario

This output shows that SQL Server is either running on 4 cores or SQL Server instance has been affinitized to 4 cores:

This output shows that there are no work items in the queues to process. For queue 0, the total work items de-queued since SQL Startup are 15625 and the max queue depth has been 15625.

queue_id total_enqueues total_dequeues current_queue_depth  maximum_queue_depth  last_service_ticks  
----------------------------------------------------------------------------------------------------  
0        15625                15625    0                    15625                1233573168347  
1        15625                15625    0                    15625                1234123295566  
2        15625                15625    0                    15625                1233569418146  
3        15625                15625    0                    15625                1233571605761

See also

Memory-Optimized Table Dynamic Management Views (Transact-SQL)