sys.dm_xtp_gc_queue_stats – Day 73 – One DMV a Day

Hello Geeks and welcome to the Day 73 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.

In garbage collection related DMVs of In-Memory OLTP we have seen sys.dm_xtp_gc_stats. Today I am going to cover sys.dm_xtp_gc_queue_stats. Till now you would have a good idea on how the garbage collection works from my last post. Sys.dm_xtp_gc_queue_stats shows the queues for each scheduler where the collected work items are enqueued.

In my demonstration machine I have four cores. So as mentioned yesterday sys.dm_xtp_gc_queue_stats should show four queues. Let’s confirm that quickly.

FROM sys.dm_xtp_gc_queue_stats

sys.dm_xtp_gc_queue_stats Queues

And yes I was right. We have four queues. Now to see the actual information from sys.dm_xtp_gc_queue_stats let us do some DML on the memory optimized tables. I will run a delete from one table and insert the rows back from the other table. The deletes will fill my delta file and the inserts will create new checkpoint file pairs. This way I ensure to have enough rows for garbage collection.

DELETE FROM inMem2_tbl
WHERE col2 > 5000

INSERT INTO inMem2_tbl
SELECT col1, col1, col3 
FROM inMem_tbl
WHERE col1 > 5000

I had some tea and tease Cruizer while I ensure the idle thread has run. For those who are not familiar with who Cruizer is, here is a glimpse. And don’t get fooled by its looks. It really took me some time to make it calm for this pic.


sys.dm_xtp_gc_queue_stats Cruizer

Now let us see how refreshed is my queues.

FROM sys.dm_xtp_gc_queue_stats

sys.dm_xtp_gc_queue_stats Enqueued

I see there are some work items enqueued. They sum up to 323. We know that each work item will have 16 rows and in above DML we have deleted 5000 rows. So the formula is simple.

323 * 16 = 5168 ~ 5000

The additional 168 may be some other operations running on my memory optimized tables. But the result is still close enough. So now you are well aware of using sys.dm_xtp_gc_queue_stats and a step closer to the end of the series.

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.