Memory-Optimized Tables

Sunil Agarwal explains that memory-optimized tables are more than just “in memory” tables:

In my many conversations with customers during Microsoft events, people often confuse between the terms ‘In Memory’ and ‘Memory-Optimized’ and many think that they are one and the same. If you continue reading this blog, you will realize that they are somewhat related but can lead to very different performance/scalability.

To understand this, let us travel back in time few years when the size of OLTP databases were much larger than the memory available on the Server. For example, your OLTP database could be 500GB while your Server box has 128 GB of memory. We all know the familiar strategy to address it by storing data/indexes in pages. SQL Server supports 8k pages and brings pages in/out of memory as needed by deploying complex heuristics as implemented as part of Buffer Pool. When running a query, if the PAGE containing the requested row(s) in not in memory, an explicit physical IO is done to bring it into memory. This impacts query performance negatively. Today, you can buy a Server class machine with say 1 TB of physical memory that can keep your full 500GB database in memory. This will indeed improve the performance of your workload by removing  bottleneck due to IO path. This is what I refer to as ‘your database is in memory’. However, the more important question to be asked ‘Is your database optimized for memory?’.

Read on for more details.

Related Posts

Digging Into The In-Memory Columnstore Location

Niko Neugebauer does some investigation into where, exactly, memory-optimized columnstore data goes: This is a rather simple blog post that is dedicated to the theme of the In-Memory Columnstore Indexes location. This has been a constant topic of discussion over a long period of time, even during the public events – and there is a […]

Read More

Out Of Memory Conditions And In-Memory OLTP

Ned Otter explains how a memory-optimized database can run out of its most critical resource: What can cause a memory-optimized database to run out of memory? It could be that resource consumption (memory) exceeded: the relevant percentage of committed_target_kb from the sys.dm_os_sys_info DMV (explained in a moment) MAX_MEMORY_PERCENT value of a Resource Pool that the database is […]

Read More


November 2016
« Oct Dec »