There’s no doubt that the most important new feature that Microsoft added to SQL Server 2014 is the new In-Memory OLTP engine. The In-Memory OLTP engine enables you to move select tables and stored procedures into memory for high-performance, low-latency data access. With CPU processing speeds plateauing and memory capacities expanding, Microsoft turned to utilizing RAM to get increased OLTP processing speeds.
Read this article to learn more, or watch this video for an overview of this important new SQL Server feature:
The new In-Memory OLTP engine is an entirely new query processing engine in SQL Server 2014. It has been designed from the ground up using an all new optimistic concurrency control design. This is one of the key points that differentiates the new In-Memory OLTP engine from the older DBCC PINTABLE technology or from putting your database on RAM disks or SSD drives. Those older technologies still used the same relational database engine with its pessimistic locking and latch design which prevents them from offering the scalability and performance found in the new In-Memory OLTP engine. They still needed to take locks on the data to insure data consistency. These locks slow down the query processing whether the data is in memory or not.
The optimistic currency control design utilized by the new In-Memory OLTP engine solves this problem. It doesn’t use locks or latches. Instead it utilizes a high performance row versioning mechanism where inserts and updates create new row versions and each row is timestamped. This process is very fast because it’s done entirely in memory. The engine then analyzes and validates any updated rows in the background before committing them. This design is faster and more scalable than the traditional locking mechanism used by SQL Server’s relational database engine because there are no locks or other wait states that prevent the processor from running at full speed.
In conjunction with this new lock-free design Microsoft also introduced a stored procedure compilation process which takes interpreted T-SQL code and compiles it into native x64 code. This reduces the number of instructions that the CPU must execute to process the stored procedure. The shorter code path results in faster executing code.
Performance Gains? It Depends!
Like you might expect, the actual performance improvements that you might see depend a lot on the application. If the application is lock bound then the results will be dramatic. If not then the performance gains will be more modest. In the SQL Server Blog the SQL Server Team stated that most organizations can expect a 5x – 20X performance improvement. Scenarios that will experience the most gains are things like web application session states and applications with periodic bulk updates and heavy random reads. These types of applications can expect anywhere from 10-25X improvement. Applications that benefit the least are things like TPC benchmark applications with lots of random writes and multiple table joins. These type of application can expect a 2-7X improvements. That said even a 2X improvement can be pretty great.
Requirements
So what are the requirements for using SQL Server 2014’s In-Memory OLTP? First, it requires the SQL Server 2014 Enterprise Edition. It’s not supported in the Standard edition. Next, it requires a 64-bit hardware platform. It really doesn’t have any other special requirements – most existing servers are capable of implementing the new In-Memory OLTP. In-Memory OLTP can be implemented on either physical or virtual SQL Server instances. However, you do need to make sure the server has adequate memory in order to take advantage of the new in-memory technologies. Microsoft recommends that the memory-optimized data should not exceed 80% of the available server memory.
Hash and Nonclustered Indexes
Tables used by the In-Memory OLTP Engine table can have two types index: hash indexes and nonclustered indexes. These indexes only exist in memory and they are rebuilt when the database starts up. Memory-optimized indexes do not contain any data. Instead, they contain memory pointers to data rows. Hash indexes are used for point lookups and nonclustered indexes are used for range lookups and ordered scans.
Other Limitations
There’s no doubt that In-Memory OLTP is a revolutionary technology. However, it does have are some limitations. Not all data types and database features are supported. For instance, there’s no support for xml, UDTs, text, ntext or image data types. Likewise there’s no support for database mirroring, TDE or snapshots. For more information on the features that both are and are not supported by In-Memory OLTP check out Supported SQL Server Features.
For an overview of the technology you can look into In-Memory OLTP (In-Memory Optimization). In the next article, I’ll take a look at using SQL Server 2014’s new Analysis Migrate Report tool to migrate your exiting databases to using the new In-Memory OLTP technology.
Leave a Reply