Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Logging, Checkpoint, and Recovery for In-Memory OLTP

Any data modifications on durable, memory-optimized tables (those created with durability = schema_and_data) are logged in the database transaction log to guarantee recovery of the tables to a known state after a system shutdown or failure. However, logging for memory-optimized tables is done differently than for disk-based tables.

In addition to writing log records for durable memory-optimized tables to disk, In-Memory OTLP also invokes a checkpoint process to write data for the tables to durable storage as well. SQL Server writes these pieces of information using log streams and checkpoint streams.

Log streams contain the changes made by committed transactions logged as insertion and deletion of row versions and are stored in the SQL Server transaction log.

Checkpoint streams come in two varieties, data streams and delta streams. Data streams contain all versions inserted during a timestamp interval. Delta streams are associated with a particular data stream and contain a list of integers indicating which versions in its corresponding data stream have been deleted. Checkpoint streams are stored in SQL Server filestream files which in essence are sequential files fully managed by SQL Server.

Transaction Logging

In-Memory OLTP’s transaction logging is optimized for scalability and high performance through the use of reduced logging. Given the same workload, In-Memory OLTP will write significantly fewer log records for a memory-optimized table than for an equivalent disk-based table.

As described in the previous section, In-Memory OLTP does not use write-ahead logging as it does for disk-based tables. It only generates and saves log records at the time of the transaction commit rather than during each row operation. Since no ”dirty data” from uncommitted transactions is ever written to disk, there’s also no need to write any undo records to the transaction log since In-Memory OLTP won’t ever need to rollback any uncommitted changes.

In addition, if you remember from the previous description of memory-optimized indexes, the indexes on memory-optimized tables are memory resident only. In-Memory OLTP doesn’t write any index modifications to the transaction log either.

A third mechanism to reduce logging overhead for memory-optimized tables is to group multiple changes into one large log record (the max log records size in SQL Server 2014 is 24KB). Combining multiple log records into larger log records results in a fewer number of log records being written which minimizes the log-header overhead and helps reduce the contention for inserting into the log-buffer.


If the transaction log was never truncated, all the changes that happened to your memory-optimized tables could be reconstructed from the transactions recorded in the log. However, this is not a feasible approach as the recovery time would be unacceptably long. Just as for operations on disk-based tables, one of the main reasons for checkpoint operations is to reduce recovery time. For In-Memory OLTP, the checkpoint process for memory-optimized tables is designed to satisfy two important requirements.

  • Continuous Checkpointing—Checkpoint operations occur incrementally and continuously as transactional activity accumulates. A background process continuously scans transaction log records and writes to the data and delta files on disk.
  • Streaming I/O—Writing to the data and delta files is done in an append-only manner by appending newly created rows to the end of the current data file and by appending the deleted rows to the corresponding delta file.

Checkpointing is the continuous background process of constructing data files and delta files and writing to them from the transaction log.

A checkpoint data file contains rows from one or more memory-optimized tables inserted by multiple transactions as part of INSERT or UPDATE operations. Multiple data files are created with each file covering a specific timestamp range. Each data file can be up to a maximum of 128MB in size (16MB for systems with 16GB or less of memory).

Once a data file is full, the rows inserted by new transactions are stored in another data file. Over time, the rows from durable memory-optimized tables are stored across one or more data files with each data file containing rows with a commit timestamp within the range of transaction timestamps contained in the file. For example a data file with transaction commit timestamps in the range of (100, 200) has all the rows inserted by transactions that have commit timestamps in this range.

Data files are append-only while they are open and are written to using sequential filestream I/O. Once data files are closed, they are strictly read-only. At recovery time the valid versions stored in the data files are reloaded into memory and reindexed.

When a row is deleted or updated by a future transaction, the row is not removed or changed in-place in the data file. Instead, the deleted rows are tracked in another type of ‘delta’ file. This eliminates random I/O on the data file. Each data file is paired with a corresponding delta file.

A delta file stores information about which rows contained in a data file have been subsequently deleted. There is a 1:1 correspondence between delta files and data files and the delta file has the same transaction range as its corresponding data file. Like data files, the delta file is written to using sequential filestream I/O. Delta files are append-only for the lifetime of the data file they correspond to. At recovery time, the delta file is used as a filter to avoid reloading deleted versions into memory. Because each data file is paired with exactly one delta file, the smallest unit of work for recovery is a single data/delta file pair. This allows the recovery process to be highly parallelizable.

A data and delta file pair is referred to as a Checkpoint File Pair (CFP). A maximum of 8192 CFPs are supported for each database.

Checkpoint Events

The data and delta files are written to continuously by the checkpoint background process. While the file is open, it has a state of UNDER CONSTRUCTION. When a checkpoint event occurs, the files UNDER CONSTRUCTION are closed and the state is set to ACTIVE. From this point, the new INSERTs will no longer be written to, but they are still ACTIVE since they are still subject to DELETE and UPDATE operations which will mark any corresponding rows in the data file as deleted in the associated delta file.

Checkpoint events for memory-optimized tables are independent of checkpoints for disk-based tables. Checkpoints for disk-based tables are generated based upon the configured recovery interval and involve flushing all ”dirty pages” from the buffer pool to disk. A complete checkpoint of memory-optimized tables consists of multiple data and delta files, plus a checkpoint file inventory that contains references to all the data and delta files that make up a complete checkpoint.

The completion of a checkpoint involves flushing the latest content of data and delta files to disk and constructing the checkpoint inventory which is written to the transaction log. Checkpoint events are generated either automatically or manually.

An automatic checkpoint occurs when the overall size of the transaction log has grown by 512MB since the last checkpoint. This includes operations logged for disk-based tables as well. It is not dependent on the amount of work done on memory-optimized tables. It’s conceivable that there may not have been any transactions on memory-optimized tables when an automatic checkpoint event occurs.

Manual checkpoints occur whenever an explicit CHECKPOINT command is initiated in the database and includes checkpoint operations for both disk-based tables and memory-optimized tables.

Merging Checkpoint Files

The number of checkpoint files can continue to accumulate. As data modifications proceed, a ”deleted” row remains in the data file but the delta file records the fact that it was deleted. Over time, the percentage of meaningful content in older data files falls, due to DELETEs and UPDATEs.

Since the recovery process reads the content of all data and delta files, recovery times will increase as it has to scan through a large number of files containing few relevant rows. To reduce the number of CFPs, SQL Server will eventually merge adjacent data files, so that rows marked as deleted actually get deleted from the checkpoint data file, and create a new CFP from the merged data files.

A background task runs periodically to examine all ACTIVE CFPs to determine if any adjacent sets of CFPs qualify to be merged. Files can be merged when the percentage of undeleted rows falls below a threshold. The main qualification must be that the merged files will result in a file of undeleted records that is 128MB or less in size (or 16MB for systems with 16GB of memory or less). Merging can also occur if two adjacent files are both less them 50% full (possibly as the result of a manual checkpoint having been run).

In most cases, automatic merging of checkpoint files will be sufficient to keep the number of files manageable. However, in rare situations or for testing purposes, you can manually initiate a checkpoint merge using the sp_xtp_merge_checkpoint_files stored procedure. To identify files that might be eligible for merge, you can examine the information returned by a query against the sys.dm_db_xtp_checkpoint_files DMV:

select file_type_desc,
 from sys.dm_db_xtp_checkpoint_files
 WHERE state_desc = 'ACTIVE'

Checkpoint Garbage Collection

At a certain point when the rows in a CFP are no longer needed (e.g., the oldest transaction still required by SQL Server is more recent than the time range covered by the CFP), the CFP will transition into a non-active state. This usually occurs when the log truncation point required for recovery is more recent than the largest transaction ID in the CFP transaction ID range.

Assuming a log backup has occurred, these CFPs are no longer required and can be removed by the checkpoint file garbage collection process which is a background process that runs automatically.


The basic mechanism to recover or restore a database with memory-optimized tables is similar to the recovery process of databases with only disk-based tables. However, recovery of memory-optimized tables also includes the step of loading the memory-optimized tables into memory before the database is available for user access.

When SQL Server restarts, each database goes through a recovery process that consists of three phases:

  1. The analysis phase.
  2. The redo phase.
  3. The undo phase.

During the analysis phase, the In-Memory OLTP engine identifies the checkpoint inventory to load and preloads its system table log entries. It will also process some file allocation log records.

During the redo phase, for memory-optimized tables, data from the data and delta file pairs are loaded into memory and then the data is updated from the active transaction log based on the last durable checkpoint and the in-memory tables are populated and indexes rebuilt. During the redo phase, disk-based and memory-optimized table recovery runs concurrently.

The undo phase is not needed for memory-optimized tables since In-Memory OLTP doesn’t record any uncommitted transactions for memory-optimized tables.

When the above operations are completed for both disk-based and memory-optimized tables, the database is available for access.

Since loading memory-optimized tables into memory can increase recovery time, the In-Memory OLTP engine attempts to improve the load time of memory-optimized data from data and delta files by loading the data/delta files in parallel. It does this by first creating a Delta Map Filter. One thread per container reads the delta files and creates a delta map filter. Once the delta-map filter is created, data files are read using as many threads as there are logical CPUs. Each thread reading the data file reads the data rows and checks it against the associated delta map and only inserts the row into table if this row has not been marked deleted.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020