{"id":1603,"date":"2014-01-22T09:00:00","date_gmt":"2014-01-22T17:00:00","guid":{"rendered":""},"modified":"2024-01-22T22:49:03","modified_gmt":"2024-01-23T06:49:03","slug":"merge-operation-in-memory-optimized-tables","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/01\/22\/merge-operation-in-memory-optimized-tables\/","title":{"rendered":"Merge Operation in Memory-Optimized Tables"},"content":{"rendered":"

In the Storage Allocation and Management for Memory-Optimized Tables<\/a>, we had briefly touched upon the Merge operation. This blog describes Merge operation in detail. We also recommend you to refer to implementing durability for memory-optimized tables<\/a> for a good understanding of data\/delta files and CFPs<\/p>\n

\"<\/a>The metadata of all Checkpoint File Pairs (i.e. CFP) that exist in storage is stored in an internal array structure referred to as Storage Array<\/b>. It is a finitely sized (4096 entries in CTP2 and 8192 entries in RTM) array of CFPs to support a cumulative size of 256GB for durable memory-optimized tables in the database. The entries in the storage array ordered by transaction range. The CFPs in the storage array (along with the tail of the log) represent all the on-disk state required to recover a database with memory-optimized tables.<\/p>\n

Running an OLTP workload overtime can lead to storage array to fill up leading to many inefficiencies as described here<\/p>\n

    \n
  1. Deleted rows \u2013 The deleted rows are not actually removed from the data file but a reference to the deleted row is added to the corresponding delta file. This leads to wastage of storage which in turn impacts the recovery time negatively.<\/li>\n
  2. Storage Array Manipulation Overhead – Internal processes search the storage array for operations such as finding the delta file to delete a row. The cost of these operations increases with the number of entries<\/li>\n<\/ol>\n

    To alleviate these inefficiencies, the older closed CFPs are merged <\/i>based on a merge policy (described below) so the storage array is compacted to represent the same set of data, with reduced number of CFPs and the storage.<\/p>\n

    Merge Operation<\/h2>\n

    The Merge operation takes one or more closed CFPs, called MERGE SOURCE(s), based on an internally defined merge policy, described later, and produces one resultant CFP, called MERGE TARGET. The entries in each delta file of source CFP(s) are used to filter rows from the corresponding data file to remove the data rows that are not needed anymore. The remaining rows in the source CFPs are consolidated into one target CFP.\u00a0 After the merge is complete, the resultant CFP (i.e. the merge-target) replaces the source CFPs (i.e. the merge sources). The merge-source CFPs go through a transition phase before they are eventually removed from the storage.<\/p>\n

    In the example below, the memory-optimized table file group has four data and delta file pairs at timestamp 500 containing data from previous transactions. For example, the rows in the first data file correspond to transactions with timestamp > 100 and <=200 or alternatively represented as (100, 200]. \u00a0The second and third data files are shown to be less than 50% full after accounting for the rows marked deleted. The merge operation combines these two CFPs and creates a new CFP containing transactions with timestamp > 200 and <=400, which is the combined range of these two CFPs. \u00a0You see another CFP with range (500, 600] and non-empty delta file for transaction range (200, 400] shows that Merge operation can be done concurrently with transactional activity including deleting more rows from the source CFPs.<\/p>\n

    \"<\/a><\/p>\n

    \u00a0In-memory OLTP engine, a background thread evaluates all closed CFPs using a merge policy and then initiates one or more merge requests for the qualifying CFPs. These merge requests are processed by the offline checkpoint thread. The evaluation of merge policy is done periodically and also when a checkpoint is closed.<\/p>\n

    Merge Policy<\/h2>\n

    SQL Server 2014 implements the following merge policy<\/p>\n