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.
SELECT * FROM sys.dm_xtp_gc_queue_stats
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.
Now let us see how refreshed is my queues.
SELECT * FROM sys.dm_xtp_gc_queue_stats
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,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
