liveBook · Manning

Memory-optimized table

As explained in 100 SQL Server Mistakes and How to Avoid Them

A memory-optimized table is a SQL Server table whose data and access structures are designed to reside and operate primarily in memory.

A memory-optimized table can make transaction-log maintenance behave differently from ordinary tables. A common mistake is assuming that automatic checkpoints will arrive often enough to permit log truncation. section 12.7

Watch for these operational signals:

  • XTP_CHECKPOINT waits can indicate problems flushing persistent memory-optimized table data streams to disk. section 12.7
  • On smaller systems, automatic checkpoints may wait for 1.5 GB of transaction-log activity, allowing log truncation to remain stalled. section 12.7
  • Older SQL Server 2014 and 2016 deployments may require the latest service pack and cumulative update because checkpoint bugs existed. section 12.7

A safer backup routine

If log truncation does not occur on a small system, schedule a manual CHECKPOINT before the transaction-log backup. Add that step to SQL Agent or coordinate it with the enterprise backup policy. section 12.7

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains checkpoint thresholds, wait types, version-specific bugs, diagnostics, and manual scheduling, but does not detail table creation.
Examples
Uses small systems, XTP_CHECKPOINT waits, SQL Server 2014 or 2016, and a pre-backup manual checkpoint.
What you can do after
Diagnose log-truncation delays and configure a manual checkpoint before transaction-log backups.
How it connects
Connects memory-optimized tables with transaction logs, checkpoints, log truncation, log backups, and SQL Agent.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.