In-Memory OLTP (codenamed “Hekaton”) is the most significant feature originally shipped with SQL Server 2014. It is Microsoft’s new technology which is fully integrated into SQL Server’s Database Engine. In-Memory OLTP introduces new data structures for optimizing the performance of OLTP workloads. These data structures are called “memory-optimized” tables and along with natively compiled stored procedures, they offer a huge performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency. In certain cases, the execution time can be up to 50 times faster.
Best-Suited Workload Types for SQL Server In-Memory OLTP
As the name of the technology implies, the In-Memory OLTP Engine is optimized for OLTP workloads. Some examples of best-suited workloads are:
- Staging tables (i.e. DW)
- High data insert rate (i.e. smart metering)
- Low latency execution (i.e. investment trading)
When used in such scenarios, the performance gain of using the In-Memory OLTP Engine can be huge.
Main Features of SQL Server Memory-Optimized Tables
Memory-optimized tables store their data into memory using multiple versions of each row’s data. This technique is characterized as “Non-blocking multi-version optimistic concurrency control” and eliminates both Locks and Latches thus achieving breakthrough performance.
The list of main features of memory-optimized tables are:
- Rows in the table are read from and written to memory
- The entire table resides in memory
- Non-blocking multi-version optimistic concurrency control
- Durable & non-durable
- A second copy maintained on disk for durability (if enabled)
- Data in memory-optimized tables is only read from disk during database recovery
- Interoperable with disk-based tables
Watch a Video About Memory-Optimized Tables
Natively-Compiled Stored Procedures
Another feature of the In-Memory OLTP Engine is “natively compiled stored procedures”. A natively-compiled stored procedure is a SQL Server object that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. The main features of a natively-compiled stored procedure are:
- It is compiled to native code (DLL) upon its creation (the interpreted stored procedures are compiled at first execution)
- Aggressive optimizations take time at compile time
- Can only interact with memory-optimized tables
The call to a natively compiled stored procedure is actually a DLL Entry Point which is something that offers very fast execution times!
When you properly combine memory-optimized tables with natively compiled stored procedures for heavy workloads like DW ETL processes, high data insert rate processes and processes that demand low latency execution you can expect some serious speedups.
Subsequent posts will dive into the In-Memory OLTP Engine of SQL Server 2014 and illustrate via different examples the benefits of using this technology.
Learn more about SQL Server In-Memory OLTP – Enroll to the Course!
We recommend enrolling to our online course titled “Boost SQL Server Database Performance with In-Memory OLTP” and learn how to harness the power of SQL Server’s In-Memory Optimization and boost the performance of your data processes!
In this course, you will learn all about SQL Server’s In-Memory Database Processing Engine also known as In-Memory OLTP. To this end, you will learn how to enable In-Memory OLTP in SQL Server, what memory-optimized tables and natively-compiled stored procedures are, as well as, how to boost the performance of your data processes using this powerful SQL Server feature.
Recommended Tool: In-Memory OLTP Simulator
Our solution, “In-Memory OLTP Simulator“, helps you to easily benchmark standard and custom scenarios of data workloads and processing against the powerful In-Memory OLTP Engine in SQL Server. Moreover, you get rich analytics and insights, and see how Memory Optimization in SQL Server can help you boost the performance of your data processes.Try In-Memory OLTP Simulator free for 14 days
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- In-Memory Optimization in SQL Server: A Simple Experiment
- Introducing In-Memory Optimization in SQL Server
- Getting Started with SQL Server In-Memory OLTP – Part 1
- Getting Started with SQL Server In-Memory OLTP – Part 2
- SQL Server 2016 SP1: Breakthrough Changes
- Where are temporary tables stored in SQL Server?
- SQL Server 2016: TempDB Enhancements
- tempdb growth
Check our other related SQL Server Performance articles.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.