In this new series you’ll get a guide to the essential features in Microsoft’s SQL Server 2014 release. This week let’s dig into what is undoubtedly the most important new feature in SQL Server 2014 – In-Memory OLTP. Originally code named Hekaton (Greek for 100 times), the new In-Memory OLTP engine was designed to potentially increase the performance of OLTP application by an ambitious 100 times. While the actual implementation doesn’t quite reach those lofty goals SQL Server 2014’s new In-Memory OLTP can provide drastic performance improvements for many applications. Let’s have a closer look at SQL Server 2014 In-Memory OLTP.
Overview
Like its name suggests SQL Server 2014’s new In-Memory OLTP works by moving select tables and stored procedures into memory – or as the documentation puts it memory optimizing the tables and stored procedure. SQL Server 2014’s In-Memory OLTP can be implemented using standard x64 servers – including VMs. Naturally, you do need enough memory in the server to be able to move your selected tables and stored procedures into memory. While it’s possible to memory optimize your tables and stored procedures with no schema change I would expect that in most cases some changes will be required. The actual changes depend on the database. I cover this more in the Considerations section.
Architecture
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 lock free design. It doesn’t use any locks and there are no internal latches that can potentially slow down database performance. This is one of the key points that differentiates the new In-Memory OLTP engine from the older DBCC PINTABLE or other older memory resident technologies. The old DBCC PINNTABLE was designed to hold a table in SQL Server’s Buffer Pool but it still used the same relational engine with all its locks and latches so it didn’t offer the scalability found in SQL Server 2014’s In-Memory OLTP. The new In-Memory OLTP engine uses a new optimistic multi-version concurrency control mechanism in conjunction with algorithms that have been optimized for memory-resident data. When a row in a shared buffer is modified the In-Memory OLTP engine makes an entirely new version of that row and timestamps the row – it doesn’t hold a lock or latch like the traditional relational database engine would. This process is very fast because it’s done entirely in memory. The engine then analyzes and validates any updated rows before committing them.
Considerations
Like you can image there are a number of considerations about using the new In-Memory OLTP. First, it’s only found in the SQL Server 2014 Enterprise edition. Next not all database features are supported. For instance there’s no support for database mirroring. You can get a complete list of database features that are not compatible with In-Memory OLTP at Transact-SQL Constructs Not Supported by In-Memory OLTP. In addition, not all data types are supported. Like you might expect there’s no support for image, text or xml data types. The list of supported data types is at Supported Data Types
Testing
In my lab tests of In-Memory OLTP on a TPC-C benchmarking applications I experienced significant performance gains with minimal application changes. While this type of application was listed as one of the ones that will receive the least benefit from the new lock-free latch-free design of the In-Memory OLTP engine I still saw a four-fold increase in TPM after memory optimizing two tables and stored procedures in the TPC-C database. What DBA wouldn’t like that? That said, while the new In-Memory OLTP can provide impressive application performance improvements it’s still not a silver bullet for all application performance issues. It works best for queries that don’t use a lot of tables and where the applications are experiencing waits dues to locks or latches.
More information
For more information on SQL Server 2014’s new In-Memory OLTP you can check out my SQL Server Pro article at Rev Up Application Performance with the In-Memory OLTP Engine. There’s an even more in-depth Microsoft reference at In-Memory OLTP (In-Memory Optimization). Plus, Kalen Delany has a great book out called SQL Server Internals: In-Memory OLTP that you can find at Amazon.
Next up
In the next SQL Server Essentials I’ll take you a step further down the In-Memory OLTP path and show you how you can get started using the SQL Server 2014 Analysis Report and Migrate (AMR) tool to memory optimize your databases.
