Database Reference
In-Depth Information
Unfortunately, code optimization is not enough by itself. Consider the situation where you need to update a row
in a table. Even when you know the clustered key value, that operation needs to traverse the clustered index tree,
obtaining latches and locks on the data pages and a row. In some cases, it needs to update nonclustered indexes,
obtaining the latches and locks there. All of that generates log records and requires writing them and the dirty data
pages to disk.
All of those actions can lead to a hundred thousand or even millions of CPU instructions to execute. Code
optimization can help reduce this number to some degree, however it is impossible to reduce it dramatically without
changing the system architecture and the way the system stores and works with data.
The in-memory OLTP engine addresses those challenges based on three design goals.
Optimize data storage for main memory
. Data in Hekaton is not stored on on-disk pages
nor does it mimic an on-disk storage structure when loaded into memory. This permits the
elimination of the complex buffer pool structure and the code that manages it. Moreover,
indexes are not persisted on disk, and they are recreated upon startup when
memory-resident tables' data is loaded into memory.
Eliminate latches and locks
. All in-memory OLTP internal data structures are latch- and
lock-free. Hekaton uses a new multiversion concurrency control to provide transaction
consistency. From a user standpoint, it looks similar to the regular
SNAPSHOT
transaction
isolation level; however, it does not use locking under the hood. This schema allows
multiple sessions to work with the same data without locking and blocking each other's
sessions and improves the scalability of the system.
Compile requests to native code
. T-SQL is an interpreted-based language that provides
great flexibility at the cost of CPU overhead. Even a simple statement requires hundreds of
thousands of CPU instructions to execute. The in-memory OLTP engine addresses this by
compiling statements and stored procedures into native machine code.
The in-memory OLTP engine is fully integrated in the SQL Server Engine, which is the key differentiator
of Hekaton as compared to other in-memory database solutions. You do not need to perform complex system
refactoring, splitting data between in-memory and conventional database servers, nor do you need to move all of
the data from the database into memory. You can separate in-memory and disk data on a table-by-table basis, which
allows you to move active operational data into memory, keeping other tables and historical data on disk. In some
cases, that conversion can be even done transparently to client applications.
It sounds too good to be true and, unfortunately, there are still plenty of roadblocks that you may encounter
during conversion. The first release of in-memory OLTP supports just a subset of the SQL Server data types and
features, which often requires you to perform code and schema refactoring. We will discuss those limitations later
in this and in the next chapter; however, you need to know that Microsoft is fully committed to the project. You can
expect that future versions of in-memory OLTP will have a bigger surface area and fewer restrictions when compared
to the initial release.
In-Memory OLTP Engine Architecture and Data Structures
In-memory OLTP is fully integrated into the SQL Server, and other SQL Server features and client applications can
access it transparently. Internally, however, it works and behaves very differently than the SQL Server storage engine.
It is important to define the terminology correctly before we discuss in-memory OLTP internals. I will use the
following terms and definitions.
Memory-optimized tables
refer to tables with the new data structure that is used by
in-memory OLTP.
On-disk tables
refer to regular SQL Server tables that are stored in database data files using
8KB data pages. All tables that we discussed previously in this topic were on-disk tables.
