How to List All Memory Optimized Tables in SQL Server? – Interview Question of the Week #227

Question: How do you list memory-optimized tables? Query sys.tables in the relevant database and filter is_memory_optimized = 1.

During performance health checks, I look for suitable opportunities to use In-Memory OLTP as well as opportunities to improve existing indexes. I’ve found fewer suitable workloads than I would like. When the fit is good, it can be worth evaluating, but the inventory below doesn’t establish that fit by itself.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,
       name AS TableName, is_memory_optimized,
       durability_desc, create_date, modify_date
FROM sys.tables
WHERE is_memory_optimized = 1
ORDER BY SchemaName, TableName;

The original query listed every table and displayed the memory-optimized flag. The filter makes this answer specific to memory-optimized tables. Remove it if you want both kinds in one inventory. sys.tables is a catalog view, and metadata visibility can limit which tables your account sees.

SCHEMA_AND_DATA means the memory-optimized table’s data is durable. SCHEMA_ONLY preserves the schema but not its rows across database restart. Memory-optimized tables are a specific In-Memory OLTP feature introduced in SQL Server 2014. Ordinary disk-based tables can also have data pages cached in memory; that doesn’t make them memory-optimized tables.

Balmukund Lakhani made a useful point in the explanation I originally shared: faster, cheaper RAM creates an opportunity, but durable business data still has to survive failure. The durability column is worth reading, rather than treating every in-memory table as the same thing.

Related examples

• Identify and filter memory-optimized tables
• Beginning In-Memory OLTP with a sample
• Filter In-Memory OLTP tables in SSMS
• SQL Server 2016 LOB enhancement
• Memory usage by memory-optimized objects
• Columnstore with In-Memory OLTP tables

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.