In-Memory OLTP by Database
Overview
Memory-optimized rows cannot be paged out, so every megabyte of In-Memory OLTP (XTP) data is memory the buffer pool cannot have. The database level In-Memory OLTP report shows one database’s XTP memory and the instance total, but not which database the rest of the total belongs to.
The In-Memory OLTP by Database report shows the whole instance:
- one row for each database with a MEMORY_OPTIMIZED_DATA filegroup, memory-optimized tables, or XTP memory in its memory clerk,
- each database’s XTP memory and its share of max server memory,
- its memory-optimized tables, how many are SCHEMA_ONLY, and its filegroup,
- the resource pool it is bound to and the pool’s MAX_MEMORY_PERCENT,
- a No resource pool binding warning for a database over 25% of max server memory that is not bound to a pool, by the same rule as the database page, so the two agree.
Double click a database to open its In-Memory OLTP page, with its tables, hash indexes, checkpoint files and scripts.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → In-Memory OLTP by Database |
| Instance reports navigator | Performance group |
| Related Links bar | From In-Memory OLTP |
| Report arrows | Previous is I/O by Drive, next is Index LOB Columns |
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads every database again |
| Cancel | Stops a read in progress |
The read opens every database, so it runs in the background on its own connection, with the progress bar and a query timeout of 300 seconds.
Reading the page
The heading counts the databases and the warnings, for example 3 databases use In-Memory OLTP: 1 needs a resource pool binding, or no findings. The note under it gives when the instance was read, the SQL Server version, how many databases were read and which could not be (up to four by name, then and N more).
The cards:
| Card | What it shows |
|---|---|
| XTP memory, instance | Every MEMORYCLERK_XTP clerk together, split into the databases’ clerks and other (XTP memory no database is charged for). Not visible without VIEW SERVER STATE |
| Of max server memory | The instance’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) |
| Databases using it | The databases listed, not counting tempdb, and their memory-optimized tables |
| Resource pools | How many databases are bound to a pool, and either how many are over 25% and not bound or how many use the default pool. Drawn in the warning color when any database has the warning |
| Largest | The database with the most XTP memory, and how much |
The memory measured against is max server memory, or physical memory when max server memory was never set or is set above it.
The lines under the cards give each No resource pool binding warning in the warning color, then what could not be read: the per database memory without VIEW SERVER STATE, the pool memory in use without VIEW SERVER STATE, and the databases that could not be read.
XTP memory by database draws one bar for each database, largest first, up to 12 (the title then says largest 12 of N). Each bar gives the XTP memory, its percent of max server memory and the resource pool. A database with the warning is drawn in the warning color. Hover a bar for the details; double click it to open that database’s In-Memory OLTP page.
The grid
One row per database, most XTP memory first.
| Column | What it is |
|---|---|
| Database | The database |
| XTP MB | The database’s XTP memory: the figure the In-Memory OLTP page shows (sys.dm_db_xtp_memory_consumers), or the clerk when that cannot be read |
| Clerk MB | The database’s MEMORYCLERK_XTP clerk (named DB_ID_n). Empty without VIEW SERVER STATE |
| % of Max Memory | XTP MB as a percent of max server memory (or of physical memory when it is not set) |
| Tables | Memory-optimized tables. Empty when the database could not be read |
| SCHEMA_ONLY | How many of them are SCHEMA_ONLY |
| Filegroup | The MEMORY_OPTIMIZED_DATA filegroup |
| Resource Pool | The pool the database is bound to, or Not bound |
| Pool Max Memory % | The bound pool’s MAX_MEMORY_PERCENT |
| Finding | See below |
The clerk counts pages and can run a few percent to a third above what the database page shows, which is why XTP MB uses the database page’s figure where it can: the percent and the warning then match that page.
| Finding | Meaning | Color |
|---|---|---|
| No resource pool binding | XTP memory is over 25% of max server memory (or of physical memory when it is not set) and the database is not bound to a resource pool | Dark orange |
| Not read: <reason> | The database is listed from its clerk but could not be opened: not online, no access for this login, or the error | Gray |
| Pool has no memory cap (MAX_MEMORY_PERCENT 100) | Bound to a pool that does not cap its memory | Gray |
| Memory-optimized tempdb metadata | tempdb, listed because memory-optimized tempdb metadata gives it an XTP clerk | Normal |
| No MEMORY_OPTIMIZED_DATA filegroup | Listed without the filegroup, for its tables or its clerk | Normal |
| OK | Nothing to report | Normal |
Hover a row for the XTP memory and its percent, the clerk, the tables, the filegroup, the pool with its MAX_MEMORY_PERCENT and the memory it has in use, and the finding.
When no database uses In-Memory OLTP the page says No database on this instance uses In-Memory OLTP., with how many databases were read and what could not be.
Right-click actions
| Item | When |
|---|---|
| Open In-Memory OLTP for <database> | The database was read and is not tempdb. Double clicking the row does the same |
| Show Resource Pool Binding Script | The database has the No resource pool binding warning |
| Copy Chart to Clipboard, Save Chart as PNG… | The chart as a picture |
The binding script 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. It is shown to review, never run by the report. The grid exports to CSV and Excel like every other grid.
Related reports
| Report | Why you would go there |
|---|---|
| In-Memory OLTP | One database’s tables, hash indexes, checkpoint files and scripts. |
| Memory | How the rest of the instance’s memory is doing. |
| Memory Pressure | Whether the instance ran short of memory. |
| Databases by Size | The databases’ size on disk. |
| Configuration Values | Max server memory, which every percent here is measured against. |
Permissions and versions
- In-Memory OLTP arrived in SQL Server 2014. The menu item is always there; on an older instance the page says In-Memory OLTP arrived in SQL Server 2014.
- The per database clerks and the instance total (
sys.dm_os_memory_clerks) and physical memory (sys.dm_os_sys_info) need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022). Without it the page still lists the databases and their bindings, and says what is missing. - The pool memory in use comes from
sys.dm_resource_governor_resource_pools, which also needs VIEW SERVER STATE. Without it the pool names and MAX_MEMORY_PERCENT come fromsys.resource_governor_resource_pools. - Each database is opened to read its filegroup (
sys.filegroups) and memory-optimized tables (sys.tables), and, where In-Memory OLTP is in use,sys.dm_db_xtp_memory_consumers, which needs VIEW DATABASE STATE (or VIEW DATABASE PERFORMANCE STATE) in that database; without it the clerk stands in. The binding comes fromsys.databasesand needs no special permission. - A database that is not online, that the login cannot open, or whose read fails is skipped with the reason; it is still listed when its clerk shows XTP memory. tempdb is never opened.