Hello Geeks and welcome to the Day 72 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.
Till yesterday we have seen the hash index stats, checkpoint files, merge related DMVs. Today I will start with three more DMVs in In-Memory OLTP related to Garbage collection. As in when I talk about a DMV I will explain some concept around how it works. This way I cover both the DMV and the concepts of In-Memory OLTP. After this we will be left with two more Index related DMVs in In-Memory OLTP. And that my friends, will bring this One DMV a Day series to an end. Today I will be talking about sys.dm_xtp_gc_stats.
Before going into details of sys.dm_xtp_gc_stats let us understand the overview of garbage collection. Simply put, it is a process of clearing out the garbage. The garbage collection checks if the oldest active transaction has changed since its last run. If this has changed it checks for updates, deletes and inserts and queues chunks of 16 rows to the garbage collection queues. I will cover queues in the next DMV. This way the garbage is collected. In other words cleared from the memory and disks in case of durable tables.
Garbage collection is done by main garbage collection thread which is nothing but the idle thread. Idle thread also does other operations which is not in the scope of this blog. But garbage collection has its drawbacks too. To overcome them and reduce the load on this thread the Microsoft Product team has done something really intelligent. They made the transaction processes more cooperative. This means any transaction after performing its task does some part of garbage collection on its scheduler and then on its memory node. This makes things easier for the garbage collection thread. A real team work. 🙂
So let us see the output from sys.dm_xtp_gc_stats.
SELECT * FROM sys.dm_xtp_gc_stats
The columns are mostly self-explanatory once you understand the above concept. I will give the brief description of each column of sys.dm_xtp_gc_stats to make it easier.
rows_examined – Total number of rows checked by the garbage collector since the instance startup.
rows_no_sweep_needed – The rows which are less accessed are only cleaned by idle worker. This is also termed as dusty corner scan.
rows_first_in_bucket – Number of rows that are the first row in the hash bucket which are scanned.
rows_first_in_bucket_removed – Number of such first rows in hash bucket which are removed.
rows_marked_for_unlink – Number of rows which are marked as unlinked in their indexes. This is determined by checking refcount having 0.
parallel_assist_count – Number of garbage rows which are processed by the user transaction.
idle_worker_count – Number of garbage rows which are processed by the user transaction.
sweep_scans_started – Number of dusty corner scans that the idle worker has performed.
sweep_scans_retries – Retries of the dusty corner scans in case of any issues. High number for this may indicate problems for garbage collection subsystem.
sweep_rows_touched – Rows which are read by the idle thread during dusty corner scans.
sweep_rows_expiring – Number of expiring rows read by dusty corner processing.
sweep_rows_expired – Number of expired rows read by dusty corner processing.
sweep_rows_expired_removed – Number of expired rows removed by dusty corner processing.
Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
