sys.dm_db_xtp_merge_requests – Day 71 – One DMV a Day

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

Today I will be covering sys.dm_db_xtp_merge_requests. To understand the output of this DMV you need to understand what a merge request is. Whenever a DML operation occurs on a memory optimized table the data is not updated on original records. If you remember I have covered Checkpoint File Pairs in sys.dm_db_xtp_checkpoint_files.

The files for storing data on disks for memory optimized tables is in the form of Data and Delta file pairs. So all inserts go into Data file and deletes go into Delta files. If a row is updated the rows information of old record is kept in Delta file and a new row is inserted with latest data. Overtime these needs to be cleared to save space. This operation is called garbage collection. After garbage collection the files are merged to save space. These stats for merge requests is recorded in sys.dm_db_xtp_merge_requests.

sys.dm_db_xtp_merge_requests CFP

So let us see the output from sys.dm_db_xtp_merge_requests.

SELECT request_state_desc,
	destination_file_id,
	lower_bound_tsn,
	upper_bound_tsn,
	collection_tsn,
	checkpoint_tsn,
	source0_file_id,
	source1_file_id
FROM sys.dm_db_xtp_merge_requests

sys.dm_db_xtp_merge_requests

The columns from sys.dm_db_xtp_merge_requests are as follows.

request_state_desc – The status of the merge request. Possible values are
    REQUESTED – The merge request exists but not handled yet.
    PENDING – The merge request is being processed.
    INSTALLED – The merge request is completed.
    ABANDONED – The merge request is not completed due to any issue like storage space, etc.
destination_file_id – File id of the destination file. This relates to the checkpoint_file_id in sys.dm_db_xtp_checkpoint_files after INSTALLED.
lower_bound_tsn – The lowest transaction timestamp of all the source files to be merged.
upper_bound_tsn – The highest transaction timestamp of all the source files to be merged.
collection_tsn – The timestamp at which the row from this DMV can be collected by the garbage collection. So installed and abandoned rows are collected based on this timestamp.
checkpoint_tsn – The timestamp when the checkpoint started. This will halp any transactions to use the target or source files for existing transactions.
sourcenumber_file_id – From the above output you can see I selected only source file number 0 and 1. The merge can happen up to 16 source files and there is a column for each source file in the DMV. These relates to the checkpoint_file_id in sys.dm_db_xtp_checkpoint_files before collected.

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

Avatar

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. Required fields are marked *