{"id":1623,"date":"2014-01-16T13:00:00","date_gmt":"2014-01-16T21:00:00","guid":{"rendered":""},"modified":"2024-01-22T22:49:04","modified_gmt":"2024-01-23T06:49:04","slug":"storage-allocation-and-management-for-memory-optimized-tables","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/sql-server\/blog\/2014\/01\/16\/storage-allocation-and-management-for-memory-optimized-tables\/","title":{"rendered":"Storage Allocation and Management for Memory-Optimized Tables"},"content":{"rendered":"

As described in implementing durability for memory optimized tables<\/a>, the storage requirements and its management for memory optimized tables is very different compared to the disk-based tables. If you are migrating a subset of your disk-based tables to memory-optimized tables or are just developing a new application with memory-optimized tables, you will notice these differences. Unlike disk-based tables in SQL Server that use 8k pages to store data in the same format both on disk and in memory, the \u00a0memory-optimized tables utilize a different size and\u00a0 format both in-memory and on disk. This has been a point of concern by many customers as they have observed, sometimes, disproportionate storage consumed by memory optimized tables. The goal of this blog is to help alleviate this confusion by describing how storage is managed over the lifetime of data rows.\u00a0<\/p>\n

The data for memory optimized tables is stored in one or more data\/delta file pairs (also referred to as checkpoint file pairs or CFP) with data file(s) storing inserted rows and delta file(s) referencing deleted rows. \u00a0During the execution of an OLTP workload, as the DML operations update, insert, and delete rows, new data\/delta files are created to persist the data. Also, existing delta files are updated to process delete of existing rows. \u00a0Over time, if the number of active rows, after accounting for deleted rows, in two or more consecutive CFPs falls below a threshold (usually < 50%) such that they can be merged into one CFP of 128 MB, they are merged automatically by a background merge process into a new CFP. \u00a0Once the merge operation is complete, the older CFPs go through a transition phase and are eventually removed (i.e. garbage collected) from the storage. \u00a0Note, SQL Server 2014 CTP2 supports up to 4096 CFPs within a database but this limit will be changed to 8192 CFPs in RTM<\/p>\n

At any given time, the data\/delta file pairs are in one of the following 4 categories<\/p>\n

    \n
  1. \nPre-allocated CFPs (data and delta):<\/span> A small set of CFPs are kept<\/i> pre-allocated to minimize or eliminate any waits to allocate new files as transactions are being executed. These are full sized with data file size of 128MB and delta file size of 8MB but contain no data. The number of CFPs is computed as the number of logical processors or schedulers with a minimum of 8. This is a fixed storage overhead in databases with memory-optimized tables<\/li>\n
  2. \nActive CFPs<\/span>: These contain the inserted\/deleted rows for the last \u2018durable\u2019 checkpoint. These CFPs contain all inserted\/deleted rows required before applying the active part of the transaction log at the database restart. We expect that the combined size of the ACTIVE CFPs to be at most 2x of the in-memory size of memory-optimized tables. However, there are cases, specifically with data files > 128MB which can happen due to large concurrent transactions or if the merge operation falls behind, when we may exceed this 2x limit. To simplify the discussion here, let us just assume all data files are 128MB and that the merge\/checkpoint operations are keeping up.<\/li>\n
  3. \nCFPs required for Operational Correctness<\/span>: These represents files that were a source of MERGE operations where rows from these files were moved to a new data\/delta files. These files (i.e. CFPs) are not needed for database restart.\u00a0 However, these CFPs can\u2019t be de-allocated or marked for deallocation (i.e. \u00a0Garbage collection) because they are needed if SQL Server needs to recover from a previous durable checkpoint. \u00a0A CFP can be marked for garbage collection once the log truncation point moves beyond its transaction range. This requires a combination of checkpoint operation and transaction log backups. Transaction log backups are not needed if the database has been configured in simple recovery model.<\/span>\n<\/li>\n
  4. \nCFPs that can be removed from storage:<\/span> These are garbage collected similar to how file-stream files are garbage collected. \u00a0Please refer to FS Garbage Collection<\/a> for details<\/li>\n<\/ol>\n

    Transitioning CFPs out of category-3 and category-4 can take up to 5 checkpoints and transaction log backup steps, if not running in simple recovery mode. For in-memory databases, the automatic checkpoint is taken when the size of transaction log exceeds 512MB since the last checkpoint.\u00a0 You can, of course, manually force the checkpoint followed by log backup to expedite the garbage collection but then this will add 5 empty CFPs (i.e. 5 data\/delta file pairs with data file of size 128MB each). Typically, in test environment, you may have to force transaction log backups and checkpoints to remove the CFPs that are not needed. In production scenarios, however, we don\u2019t expect customers to do manual checkpoint but to rely on the automatic checkpoints and log backups that are taken as part of backup strategy. The impact of this garbage collection process is that in-memory databases may have a disproportionate storage footprint compared to its size in memory.<\/p>\n

    To clarify the point further, let us walk through some examples of in-memory databases under various scenarios and compare their size in-memory and on storage.\u00a0 For these examples, we will assume we are running a SQL instance with 8 logical processor with 1 memory optimized table with following schema. Note, this has a row size of approximately 8KB.\u00a0<\/p>\n

    CREATE TABLE<\/span> dbo.t_memopt (<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c1 int<\/span> NOT NULL,<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c2 char<\/span>(40) NOT NULL,<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c3 char<\/span>(8000) NOT NULL,<\/span><\/p>\n

    \u00a0<\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT<\/span> [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH<\/span> (c1) WITH<\/span><\/span><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (BUCKET_COUNT<\/span> = 100000)<\/span><\/p>\n

    ) WITH<\/span> (MEMORY_OPTIMIZED<\/span> = ON<\/span>, DURABILITY<\/span> = SCHEMA_AND_DATA)<\/span><\/p>\n

    As indicated earlier, the fixed storage overhead for this configuration will be 1 GB (i.e. 8 data files of 128MB each).<\/p>\n