Edit

Share via

sys.dm_xtp_gc_stats (Transact-SQL)

  • Article

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

Provides information (the overall statistics) about the current behavior of the In-Memory OLTP garbage-collection process.

Rows are garbage collected as part of regular transaction processing, or by the main garbage collection thread, which is referred to as the idle worker. When a user transaction commits, it dequeues one work item from the garbage collection queue (sys.dm_xtp_gc_queue_stats (Transact-SQL)). Any rows that could be garbage collected but were not accessed by main user transaction are garbage collected by the idle worker, as part of the dusty corner scan (a scan for areas of the index that are less accessed).

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

Column name Type Description
rows_examined bigint The number of rows examined by the garbage collection subsystem since the server was started.
rows_no_sweep_needed bigint The number of rows that were removed without a dusty corner scan.
rows_first_in_bucket bigint The number of rows examined by garbage collection that were the first row in the hash bucket.
rows_first_in_bucket_removed bigint The number of rows examined by garbage collection that were the first row in the hash bucket that have been removed.
rows_marked_for_unlink bigint The number of rows examined by garbage collection that were already marked as unlinked in their indexes with ref count =0.
parallel_assist_count bigint The number of rows processed by user transactions.
idle_worker_count bigint The number of garbage rows processed by the idle worker.
sweep_scans_started bigint The number of dusty corner scans performed by garbage collection subsystem.
sweep_scan_retries bigint The number of dusty corner scans performed by the garbage collection subsystem.
sweep_rows_touched bigint Rows read by dusty corner processing.
sweep_rows_expiring bigint Expiring rows read by dusty corner processing.
sweep_rows_expired bigint Expired rows read by dusty corner processing.
sweep_rows_expired_removed bigint Expired rows removed by dusty corner processing.

Permissions

Requires VIEW SERVER STATE permission on the instance.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Usage Scenario

The following is sample output:

rows_examined        rows_no_sweep_needed rows_first_in_bucket rows_first_in_bucket_removed  
280085               209512               69905  
rows_first_in_bucket_removed rows_marked_for_unlink parallel_assist_count idle_worker_count  
69905                        0                      8953  
  
idle_worker_count    sweep_scans_started  sweep_scan_retries   sweep_rows_touched  
10306473             670                  0                    1343  
  
sweep_rows_expiring  sweep_rows_expired   sweep_rows_expired_removed  
               0                 673673

See also

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