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.

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

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:

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)

, , ,
Previous Post
When was Table Accessed Last By User? – Interview Question of the Week #226
Next Post
How Much Memory is in Locked Pages? – Interview Question of the Week #228

Related Posts

1 Comment. Leave new

  • tomwickerath
    June 4, 2019 5:09 am

    Hi Pinal,

    You wrote: “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.”

    Can you provide some basic guidelines as to what constitutes a “good situation” in your mind?

    Thank You

    Reply

Leave a Reply

Menu