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

Question: How to List All Memory Optimized Tables in SQL Server?

Answer: When I go to Comprehensive Database Performance Health Check engagement, besides looking for the opportunity to drop the indexes, I also look for the opportunity to implement In-Memory OLTP. In my experience, I have found an opportunity to implement In-Memory OTLP Tables is not as much as I really would love to but whenever I find a good situation, I always recommend my clients to go for it.

Fro the databases where we have enabled In-Memory OLTP, we can run the following query to know which table is a memory-optimized table.

SELECT SCHEMA_NAME(Schema_id) SchemaName,
name TableName,
is_memory_optimized,
durability_desc,
create_date, modify_date
FROM sys.tables
GO

Once you run above DMV, it will give us all the necessary information about what tables are memory optimized and what is their durability.

Here are a few additional blog posts related to this topic:

• SQL SERVER – Identity and Filter In-Mem Optimized Tables – SQL in Sixty Seconds #079
• SQL SERVER – Beginning In-Memory OLTP with Sample Example
• SQL SERVER – Filter In-Memory OLTP Tables in SSMS
• SQL SERVER 2016 – InMemory OLTP LOB Datatype Enhancement
• SQL SERVER – SSMS: Memory Usage By Memory Optimized Objects Report
• SQL SERVER – Creating Clustered ColumnStore with InMemory OLTP Tables

Here is the paragraph from Balmukund Lakhani‘s blog.  Looking at the market trends of tumbling cost of RAM (USD/MB) and performance implication of reading data from memory vs disk, it’s evident that people would love to keep the data in memory. With this evolution in the hardware industry, the software has to be evolved and modified so that they can take advantage and scale as much as possible. On the other hand, businesses also don’t want to compromise the durability of data – restart would clear RAM, but data should be back in the same state as it was before the failure. To meet hardware trends and durability requirements, SQL Server  has introduced In-Memory OLTP which would solve them in a unique manner.

Reference: Pinal Dave (https://blog.sqlauthority.com)