Another unique thing about SQL Server is Memory-Optimized Tables. Many people think that the concept of Memory-Optimized Tables is just about storing the data in memory as opposed to storage, therefore making everything faster. Well, this is not completely true.
What do ordinary tables look like?
In an ordinary SQL Server table, data is stored persistently on storage. However, when the SQL Server is going to read or alter any piece of data, it has to load it into RAM (Buffer Pool) first. An INSERT or an UPDATE operation in SQL Server will typically involve alteration of data in memory and subsequent writing to storage.
So even the ordinary tables take advantage of RAM heavily.
What then is special about Memory-Optimized Tables?
Well, it has more to do with how SQL Server handles the data rather than where it is actually stored.
The conventional tables structure data into pages that are 8 KB. Each time any change is made to the data, SQL Server needs to handle the pages. In case there are several users accessing the same data at the same time, SQL Server uses features like Locks and Latches to keep everything under control.
The Memory-Optimized Tables have a completely different internal structure altogether. They do not use pages for structuring data and eliminate most internal overheads. As such, they perform better in highly concurrent workloads.
What are Locks and Latches?
Imagine you have a notebook.
If two people try to edit the same page at the same time, one of them has to wait until the other finishes writing. That's similar to a Lock—it protects the data itself.
Now imagine someone is turning the pages of the notebook. If another person tries to grab the same page while it's being moved, the notebook could be damaged. To prevent this, SQL Server briefly blocks access while it manipulates its internal structures. This is called a Latch. Unlike a Lock, a Latch protects SQL Server's internal memory structures rather than the user data.
Memory-Optimized Tables eliminate much of this waiting.
Versioning
In the case of traditional tables, any changes are usually made directly to the row itself.
However, with Memory-Optimized Tables, changes are not made to the existing row; rather, a new version of this row is generated by SQL Server. The previous version stays there as long as there are no transactions using it.
This enables multiple users to read/write data at once without blocking one another.
What is MEMORY_OPTIMIZED_DATA?
Great and important question. In case if your data is residing in memory, then what will happen if the server goes down?
This is where the MEMORY_OPTIMIZED_DATA filegroup plays an important role.
The MEMORY_OPTIMIZED_DATA filegroup in SQL Server is used to hold all the necessary details required for the recovery of durable Memory-Optimized tables after a restart. This will give persistence to your data.
In other words, this filegroup is not a substitute for RAM, but rather the persistent place where SQL Server can retrieve the data after a restart.
Is it not what the Index does as well?
No.
Indexing just increases the speed at which you locate the data.
In case SQL Server requires locating a user record with the ID equal to 100, an Index will help locate it very quickly.
But once located and when it should be updated, SQL Server will have to work with pages, locks, latches, and all other common methods.
Memory-Optimized Tables enhance a totally different area of the system and its architecture for read/write processes.
That is why in spite of good indexing, Memory-Optimized Tables give considerable performance boosts in systems handling thousands of simultaneous operations.
Do all tables need to be Memory-Optimized?
Absolutely not.
In case when a table holds several thousand rows but is modified only a few hundreds times a day, there will be no advantages to using Memory-Optimized Tables.
On the other hand, they are very useful for applications like:
User sessions storage
Chat programs
Gaming websites
Processing queues
Large-scale financial applications that deal with a huge number of parallel transactions
When there are thousands of actions happening every second, Memory-Optimized Tables may be very beneficial.
Summary
The Memory-Optimized Tables do not represent just another way to "store data in RAM". These are radically new storage structures that have been created with high-concurrency use-cases in mind. Memory-Optimized tables achieve their increased speed through optimization of internal structures and reduction of waiting time between concurrent actions through optimization of data handling in SQL Server's memory. The increased complexity of Memory-Optimized tables, however, makes them suitable for specific use-cases only.
Top comments (2)
Great article! Your explanation of memory-optimized tables really highlights how much thought goes into SQL Server's architecture.
When a client sends a query, it goes through: Parsing → Binding → Optimization → Execution Plan → Execution Engine → Results**. The execution engine handles both the storage layer (Buffer Pool, Index operations, I/O) and query processing.
What I find most fascinating—and often underestimated—is the Query Optimizer. It's arguably the most important step because it decides how to execute your query. But sometimes it doesn't choose the best plan—outdated statistics, poor cardinality estimates, or badly structured queries can throw it off. That's why understanding SQL internals is so valuable; it helps you write better queries and troubleshoot performance issues effectively.
Knowing the engineering behind the scenes empowers you to think like the database engine, not just a user of it. Thanks again for this insightful post!
good