close

Articles Home » Database Development » How to Use SQL Server’s In-Memory OLTP for Performance Boost

How to Use SQL Server’s In-Memory OLTP for Performance Boost

Introduction

SQL Server’s In-Memory OLTP (Online Transaction Processing) feature, also known as Hekaton, is designed to improve the performance of transaction-heavy applications by storing tables and indexes in-memory rather than on disk. This tutorial aims to guide you through leveraging In-Memory OLTP to boost your application’s performance. We’ll cover the fundamentals, setup, key features, and best practices. This tutorial assumes you have a working knowledge of SQL Server and general database management concepts.

1. Introduction to In-Memory OLTP

What is In-Memory OLTP?

In-Memory OLTP is a memory-optimized database engine integrated into SQL Server, designed to significantly improve the performance of OLTP workloads. It uses a new data storage format that stores tables and indexes entirely in memory, leading to faster data access and processing.

Benefits of In-Memory OLTP

  • Performance Improvement: By storing data in memory and using optimized algorithms, In-Memory OLTP can significantly reduce latency and improve transaction throughput.
  • Reduced Contention: In-Memory OLTP uses latch-free and lock-free structures, reducing contention and increasing concurrency.
  • Enhanced Scalability: The optimized engine allows for better scalability, handling more transactions per second.

When to Use In-Memory OLTP

  • High Transaction Volume: Applications with high transaction volumes can benefit the most.
  • Low-Latency Requirements: Applications requiring low-latency data access and processing.
  • Contention Bottlenecks: Situations where traditional disk-based tables face contention bottlenecks.

2. Setting Up In-Memory OLTP

Prerequisites

Before setting up In-Memory OLTP, ensure your system meets the following prerequisites:

  • SQL Server 2014 or later (Enterprise or Standard edition)
  • Sufficient memory to accommodate memory-optimized tables and indexes
  • Database compatibility level set to 110 or higher

Enabling In-Memory OLTP

  1. Enable Filegroup for In-Memory OLTP:
   ALTER DATABASE YourDatabase
   ADD FILEGROUP InMemory_Data CONTAINS MEMORY_OPTIMIZED_DATA;

   ALTER DATABASE YourDatabase
   ADD FILE (name='YourDatabase_mod', filename='path_to_file') TO FILEGROUP InMemory_Data;Code language: SQL (Structured Query Language) (sql)
  1. Ensure Compatibility Level:
   ALTER DATABASE YourDatabase
   SET COMPATIBILITY_LEVEL = 130; -- Example for SQL Server 2016Code language: SQL (Structured Query Language) (sql)

Creating Memory-Optimized Tables

Memory-optimized tables are created using the MEMORY_OPTIMIZED = ON option.

CREATE TABLE dbo.MemoryOptimizedTable
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(100) NOT NULL,
    CreationDate DATETIME2 NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);Code language: SQL (Structured Query Language) (sql)

Creating Natively Compiled Stored Procedures

Natively compiled stored procedures are optimized for in-memory tables and offer significant performance improvements.

CREATE PROCEDURE dbo.usp_InsertMemoryOptimizedTable
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
    LANGUAGE = N'us_english'
)
    DECLARE @ID INT = 1;
    DECLARE @Name NVARCHAR(100) = 'Example';
    DECLARE @CreationDate DATETIME2 = SYSDATETIME();

    INSERT INTO dbo.MemoryOptimizedTable (ID, Name, CreationDate)
    VALUES (@ID, @Name, @CreationDate);
ENDCode language: SQL (Structured Query Language) (sql)

3. Key Features of In-Memory OLTP

Memory-Optimized Tables

Memory-optimized tables are fully stored in memory, offering faster data access and manipulation. They use a new structure that avoids the traditional locking mechanisms, reducing contention and improving concurrency.

Natively Compiled Stored Procedures

Natively compiled stored procedures are precompiled to machine code, allowing faster execution compared to interpreted T-SQL procedures. They are ideal for complex logic and repetitive tasks.

Transaction Durability Options

In-Memory OLTP supports different durability options:

  • SCHEMA_AND_DATA: Both schema and data are durable. This is the default setting.
  • SCHEMA_ONLY: Only schema changes are durable, not the data. This can be useful for transient data.

Integration with SQL Server

In-Memory OLTP integrates seamlessly with SQL Server, allowing you to use standard SQL Server tools and features. You can mix disk-based and memory-optimized tables in the same database and leverage features like Always On Availability Groups and backup/restore operations.

4. Migrating to In-Memory OLTP

Identifying Candidate Tables and Procedures

Not all tables and procedures are suitable for In-Memory OLTP. Identify candidates by analyzing:

  • Transaction Volume: Tables with high transaction rates.
  • Contention Issues: Tables frequently experiencing lock contention.
  • Performance Bottlenecks: Procedures that are performance-critical and executed frequently.

Converting Disk-Based Tables to Memory-Optimized Tables

  1. Analyze Existing Schema: Identify tables that can benefit from memory optimization.
  2. Create Memory-Optimized Tables: Use CREATE TABLE with MEMORY_OPTIMIZED = ON.
  3. Migrate Data: Use INSERT INTO or BCP to migrate existing data to memory-optimized tables.
  4. Test Performance: Evaluate the performance improvements and make necessary adjustments.

Converting Stored Procedures to Natively Compiled Stored Procedures

  1. Identify Critical Procedures: Focus on performance-critical stored procedures.
  2. Rewrite Using Native Compilation: Use WITH NATIVE_COMPILATION, SCHEMABINDING.
  3. Test and Optimize: Test the natively compiled procedures and optimize them for best performance.

5. Best Practices and Considerations

Memory Management

  • Sufficient Memory: Ensure there is enough memory to accommodate the memory-optimized tables and indexes.
  • Monitor Memory Usage: Regularly monitor memory usage to prevent memory pressure and ensure optimal performance.

Indexing Strategies

  • Primary Keys: Memory-optimized tables require a primary key.
  • Nonclustered Indexes: Use nonclustered indexes to improve query performance, but balance the number of indexes to avoid excessive memory usage.

Transaction Management

  • Appropriate Isolation Levels: Use appropriate isolation levels like SNAPSHOT to maintain consistency and performance.
  • Durability Options: Choose the right durability option (SCHEMA_AND_DATA or SCHEMA_ONLY) based on your application’s requirements.

Monitoring and Troubleshooting

  • Extended Events: Use Extended Events to monitor and troubleshoot performance issues.
  • DMVs: Leverage Dynamic Management Views (DMVs) to gain insights into memory usage, transaction performance, and bottlenecks.

6. Case Study: Performance Improvement with In-Memory OLTP

Scenario Description

Let’s consider a scenario where a retail application experiences performance bottlenecks due to high transaction volumes during peak hours. The application’s order processing system is identified as the critical component causing the delays.

Implementation

  1. Identify Candidate Tables: The Orders and OrderDetails tables are identified as candidates for memory optimization.
  2. Create Memory-Optimized Tables:
   CREATE TABLE dbo.Orders
   (
       OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
       CustomerID INT NOT NULL,
       OrderDate DATETIME2 NOT NULL
   ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

   CREATE TABLE dbo.OrderDetails
   (
       OrderDetailID INT NOT NULL PRIMARY KEY NONCLUSTERED,
       OrderID INT NOT NULL,
       ProductID INT NOT NULL,
       Quantity INT NOT NULL
   ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);Code language: SQL (Structured Query Language) (sql)
  1. Create Natively Compiled Stored Procedure:
   CREATE PROCEDURE dbo.usp_ProcessOrder
   WITH NATIVE_COMPILATION, SCHEMABINDING
   AS
   BEGIN ATOMIC WITH
   (
       TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
       LANGUAGE = N'us_english'
   )
       DECLARE @OrderID INT = NEWID();
       DECLARE @CustomerID INT = 123;
       DECLARE @OrderDate DATETIME2 = SYSDATETIME();

 INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate)
       VALUES (@OrderID, @CustomerID, @OrderDate);

       DECLARE @OrderDetailID INT = NEWID();
       DECLARE @ProductID INT = 456;
       DECLARE @Quantity INT = 2;

       INSERT INTO dbo.OrderDetails (OrderDetailID, OrderID, ProductID, Quantity)
       VALUES (@OrderDetailID, @OrderID, @ProductID, @Quantity);
   ENDCode language: SQL (Structured Query Language) (sql)

Performance Comparison

Before and after the implementation of In-Memory OLTP, the order processing time was measured.

  • Before: Average order processing time was 200 milliseconds.
  • After: Average order processing time reduced to 50 milliseconds.

This significant improvement demonstrates the potential performance gains achievable with In-Memory OLTP.

7. Conclusion

SQL Server’s In-Memory OLTP feature offers substantial performance improvements for transaction-heavy applications. By storing tables and indexes in memory, using natively compiled stored procedures, and leveraging optimized algorithms, you can reduce latency, improve transaction throughput, and enhance scalability.

While implementing In-Memory OLTP, it is crucial to identify suitable candidates, manage memory effectively, and follow best practices for indexing and transaction management. Regular monitoring and performance testing are essential to ensure the optimal functioning of your in-memory database components.

Related posts: