Introducing In-Memory Optimization in SQL Server

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 or even more.

 

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.

 

Video: What are Natively Compiled Stored Procedures in SQL Server Database?

Video: Frequently Asked Questions about SQL Server In Memory OLTP

Frequently Asked Questions

1. Can the In-Memory OLTP Engine be effectively used for workloads beyond typical OLTP scenarios, such as data warehousing or reporting applications?

The In-Memory OLTP Engine is primarily optimized for OLTP workloads, but its effectiveness for other scenarios like data warehousing or reporting applications may vary. While it excels in scenarios with high concurrency and low latency requirements, its suitability for data warehousing tasks, which often involve complex analytics and reporting, might not be as straightforward. Organizations considering its use for such purposes should evaluate its performance against their specific workload requirements.

 

2. How does the In-Memory OLTP Engine handle data durability and recovery in comparison to traditional disk-based storage methods?

The In-Memory OLTP Engine offers both durable and non-durable options for memory-optimized tables. Data in memory-optimized tables is typically stored in memory and only read from disk during database recovery, which can result in faster recovery times compared to disk-based storage methods. However, organizations need to carefully consider the trade-offs between performance and durability, especially in scenarios where data integrity and reliability are critical.

 

3. Are there any limitations or trade-offs associated with using memory-optimized tables and natively compiled stored procedures in SQL Server databases?

While memory-optimized tables and natively compiled stored procedures offer significant performance benefits, they also come with certain limitations and trade-offs. For example, memory-optimized tables consume memory resources, which can be a concern in environments with limited memory capacity. Additionally, not all features and functionalities supported by traditional disk-based tables are available for memory-optimized tables. Similarly, natively compiled stored procedures are restricted to interacting with memory-optimized data structures, which may limit their applicability in certain scenarios.

 

4. What considerations should organizations take into account when deciding whether to adopt the In-Memory OLTP technology, particularly in terms of hardware requirements, compatibility with existing applications, and potential migration challenges?

When evaluating the adoption of In-Memory OLTP technology, organizations should consider various factors, including hardware requirements, compatibility with existing applications, and potential migration challenges. Assessing the hardware resources needed to support memory-optimized tables and natively compiled stored procedures is crucial for ensuring optimal performance. Additionally, organizations should evaluate the compatibility of their existing applications with the new technology and plan for any necessary modifications or updates. Migration challenges, such as data migration and application refactoring, should also be carefully considered to minimize disruption to existing business operations.

 

Get Started with 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.

Boost SQL Server Database Performance with In-Memory OLTP - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Check our other related SQL Server Performance articles.

Subscribe to our newsletter and stay up to date!

Check out our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub