In-Memory OLTP

Overview

Memory-optimized tables live entirely in memory. Their rows cannot be paged out, so a database that keeps growing its In-Memory OLTP data takes memory the buffer pool can never get back. Hash indexes add a second problem: a hash index sized with the wrong BUCKET_COUNT either walks long chains on every lookup or wastes memory on empty buckets. And checkpoint files waiting for log truncation keep holding space in the container.

The In-Memory OLTP report reads the sys.dm_db_xtp_* views for one database and shows:

  • cards with the database’s XTP memory, its share of max server memory, the resource pool it is bound to, the memory-optimized tables and the natively compiled procedures,
  • the findings for the database as a whole (resource pool binding, checkpoint files waiting for log truncation, and anything the login could not read),
  • the checkpoint files by state as horizontal bars,
  • one grid that switches between the tables (durability, rows, memory) and the hash indexes (buckets, empty percent, chain lengths, the finding and a suggested bucket count),
  • a script for each hash index finding and for binding the database to a resource pool. Nothing is run by the report.

A database with no MEMORY_OPTIMIZED_DATA filegroup and no memory-optimized tables gets one line: In-Memory OLTP is not in use, and the reason under it.

Where to find it

In the tree, expand a database, then Real Time → In-Memory OLTP.

Route How
Database tree Expand a database → Real Time → In-Memory OLTP
In-Memory OLTP by Database Double click a database (row or bar), or right-click it → Open In-Memory OLTP for <database>

The page title reads In-Memory OLTP for <database name>.

Requirements

Requirement Why
SQL Server 2014 or later In-Memory OLTP arrived in SQL Server 2014. The item is not added to the tree on older instances, and the page says so if it is opened on one.
Not tempdb tempdb cannot hold memory-optimized tables, so the item is not shown under it.
VIEW DATABASE STATE in the database sys.dm_db_xtp_table_memory_stats, sys.dm_db_xtp_memory_consumers, sys.dm_db_xtp_hash_index_stats and sys.dm_db_xtp_checkpoint_files. VIEW DATABASE PERFORMANCE STATE also works on SQL Server 2022 and later.
VIEW SERVER STATE The instance’s XTP memory (sys.dm_os_memory_clerks), physical memory (sys.dm_os_sys_info) and the pool details (sys.dm_resource_governor_resource_pools).

Each part is read on its own, so a login without a permission still gets the rest of the page and a note about what is missing. Whether the database is bound to a resource pool comes from sys.databases and needs no special permission.

The hash index view walks every bucket, so the read runs in the background on its own connection, with the progress bar, a Cancel button and a query timeout of 300 seconds.

The toolbar

Control What it does
Refresh Reads the database again. The selected row stays selected
Tables / Hash indexes Switches the grid between the two views, without reading again
Show Script Opens the script for the selected hash index when it has a finding; otherwise the resource pool binding script when the database has that finding. Disabled when there is neither
Cancel Stops a read in progress

Tables / Hash indexes and Show Script appear once there is a grid to show.

Reading the page

The heading counts the memory-optimized tables and sums up the findings, for example 5 memory-optimized tables in Sales: 2 hash indexes need a look, 1 database finding, or no findings. The note under it gives when the database was read and the SQL Server version, and says when instance memory needs VIEW SERVER STATE or the tables could not be listed.

The cards:

Card What it shows
XTP memory, this database Memory allocated by the database’s memory consumers (sys.dm_db_xtp_memory_consumers), or Not visible without VIEW DATABASE STATE. Under it, the instance total from the MEMORYCLERK_XTP clerks
Of max server memory The database’s XTP memory as a percent of max server memory. When max server memory was never set it reads Of server memory and measures against physical memory, marked (max server memory not set)
Resource pool The pool the database is bound to and its MAX_MEMORY_PERCENT, or Not bound (Uses the default pool)
Memory-optimized tables How many, and how many are SCHEMA_ONLY (or All durable)
Natively compiled procedures How many, and the MEMORY_OPTIMIZED_DATA filegroup name

The memory measured against is max server memory, or physical memory when max server memory was never set or is set above it. The Of max server memory and Resource pool cards are drawn in the warning color when the database has the No resource pool binding finding.

The findings for the database follow the cards, one line each, warnings first and in the warning color.

Checkpoint files by state draws one bar per state, in the order files move through them (PRECREATED, UNDER CONSTRUCTION, ACTIVE, MERGE TARGET, MERGED SOURCE, WAITING FOR LOG TRUNCATION, REQUIRED FOR BACKUP/HA, IN TRANSITION TO TOMBSTONE, TOMBSTONE). Each bar gives the file count, the size and the space used. The WAITING FOR LOG TRUNCATION bar is drawn in the warning color. Hover a bar for the files by type (DATA, DELTA and so on). The line under the bars is the sizing rule: set BUCKET_COUNT to 1 to 2 times the number of distinct key values, and put a key with many duplicates in a range (nonclustered) index.

The Tables view

One row per memory-optimized table, largest memory first.

Column What it is
Table Schema and table name
Durability SCHEMA_AND_DATA or SCHEMA_ONLY
Rows From sys.partitions
Table MB Memory allocated for the rows
Indexes MB Memory allocated for the indexes
Total MB Table plus indexes, allocated
Used MB Table plus indexes, used
Hash Indexes Count of hash indexes
Range Indexes Count of range (nonclustered) indexes
Findings Each hash index finding on the table as index: finding, or OK

The memory columns are empty without VIEW DATABASE STATE. Findings is dark orange when any of the table’s hash indexes has a warning, and gray for an informational finding. Hover a row for its durability, a reminder that SCHEMA_ONLY rows are lost when the instance restarts, and the detail of every hash index finding.

The Hash indexes view

One row per hash index, longest average chain first.

Column What it is
Table Schema and table name
Index The hash index
Rows The table’s rows
Buckets Total buckets
Empty Buckets Buckets with no rows
Empty % Empty buckets as a percent of all buckets
Avg Chain Average chain length
Max Chain Longest chain
Suggested Buckets The bucket count to rebuild with, or Range index for a low cardinality key. Empty when there is no finding
Finding The finding, OK, or Empty table

Finding is dark orange for a warning and gray for an informational finding. Hover a row for the full detail.

The hash index rules

An empty table has nothing to judge.

Finding Rule Level
Too few buckets Under 10% of the buckets are empty and the average chain is over 10 rows Warning
Low cardinality key Over 90% of the buckets are empty, the table has 1,000 rows or more, and the average chain is over 10 rows: many rows share a key value Warning
Too many buckets Over 90% of the buckets are empty, the table has 1,000 rows or more, and the chains are short. Each bucket takes 8 bytes whether it is used or not, and scans read every bucket Informational

Suggested Buckets is the next power of two at or above the estimated number of distinct key values (SQL Server rounds BUCKET_COUNT up to a power of two anyway). With too few buckets the row count is used as the estimate; otherwise each used bucket counts as one key. A low cardinality key gets no bucket count: more buckets would not shorten the chains, and a range index fits better.

Database findings

Finding When Level
No resource pool binding The database’s XTP memory is over 25% of max server memory (or of physical memory when max server memory is not set) and the database is not bound to a resource pool. Memory-optimized rows cannot be paged out, so they can starve the buffer pool Warning
Checkpoint files waiting for log truncation Checkpoint files are in the WAITING FOR LOG TRUNCATION state and still take space in the container. In SIMPLE recovery they are released at the next checkpoint once the log can be truncated; in FULL or BULK_LOGGED recovery after a log backup, so check that log backups run. The finding names the log reuse wait when there is one Informational
Permission needed Table memory, hash bucket health or the checkpoint files could not be read without VIEW DATABASE STATE, with the error Informational

The same unbound pool rule is used on In-Memory OLTP by Database, so the two pages agree.

Scripts

Scripts open in a window to read and copy. Nothing is run by Database Health Monitor.

Hash index script (a hash index with a finding):

  • Too few or too many buckets on SQL Server 2016 and later: ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = n) with the suggested count, and a note that the rebuild takes the table offline for its duration and needs memory for a second copy of the index. When the suggested count equals the current one, the script says the bucket count already fits.
  • Low cardinality key on SQL Server 2016 and later: an ALTER TABLE ... ADD INDEX [<index>_range] NONCLUSTERED (<same key columns>) to fill in, and the DROP INDEX of the hash index commented out, to run once queries have moved.
  • On SQL Server 2014, which cannot change the indexes of a memory-optimized table, the script says to script the table, change the index, then drop and re-create the table and reload its rows.

Resource pool binding script (the No resource pool binding finding): creates [Pool_InMemory] with MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT of 25, reconfigures Resource Governor, binds the database with sys.sp_xtp_bind_db_resource_pool, then takes the database offline and back online, because the binding takes effect only when the database is next brought online. Pick a percent that leaves the buffer pool room, and plan the offline step.

Right-click actions

Item When
Show Script for <index> A hash index with a finding is selected
Show Hash Indexes On the Tables view, for a table with hash indexes. Switches to the Hash indexes view with that table’s first hash index selected
Show Resource Pool Binding Script The database has the No resource pool binding finding
Copy the findings as text The heading, the database findings, every table and every hash index finding as plain text
Copy Chart to Clipboard, Save Chart as PNG… The chart as a picture

The grid also offers the usual Go to items for the selected table, and exports to CSV and Excel like every other grid.

Report Why you would go there
In-Memory OLTP by Database Every database’s XTP memory on the instance, side by side.
Memory How the rest of the instance’s memory is doing.
Memory Pressure Whether the instance ran short of memory.
Buffer Pool by Object Which objects hold the buffer pool that XTP memory competes with.
Backup Status Log backups release checkpoint files waiting for log truncation.
Files The database’s files, including the MEMORY_OPTIMIZED_DATA container.

Where the data comes from

One batch, run in the database, reads:

  • the MEMORY_OPTIMIZED_DATA filegroup from sys.filegroups (type FX),
  • the memory-optimized tables from sys.tables, with rows from sys.partitions and hash and range index counts from sys.indexes,
  • natively compiled procedures from sys.sql_modules (uses_native_compilation),
  • table and index memory from sys.dm_db_xtp_table_memory_stats,
  • the database’s XTP memory from sys.dm_db_xtp_memory_consumers,
  • bucket health from sys.dm_db_xtp_hash_index_stats (on SQL Server 2016 and later only the user table’s rows, through sys.memory_optimized_tables_internal_attributes),
  • the checkpoint files from sys.dm_db_xtp_checkpoint_files,
  • the instance’s MEMORYCLERK_XTP clerks, physical memory and max server memory,
  • the resource pool binding from sys.databases and the pool from sys.dm_resource_governor_resource_pools,
  • the recovery model and log reuse wait from sys.databases.

Each part runs inside its own TRY/CATCH, so one refused view does not stop the rest.

Frequently asked questions

Why does the page say In-Memory OLTP is not in use? The database has no MEMORY_OPTIMIZED_DATA filegroup and no memory-optimized tables. There is nothing to list and nothing is wrong.

Why is XTP memory “Not visible”? Without VIEW DATABASE STATE the memory views return no rows instead of an error. The page leaves the figure unknown rather than showing 0 MB.

Why does a SCHEMA_ONLY table matter? Its rows are lost when the instance restarts. The Memory-optimized tables card counts them, and the row tooltip on the Tables view says so for each one.

Can the report change a bucket count? No. It shows a script to review and run yourself. A rebuild takes the table offline for its duration and needs memory for a second copy of the index.