In-Memory OLTP is a powerful feature introduced in SQL Server 2014 that allows you to store and process data in memory for improved performance. In this article, we will discuss the concepts and benefits of In-Memory OLTP.

One of the main reasons for using In-Memory OLTP is the decreasing cost of RAM and the performance advantage of reading data from memory compared to disk. With the evolution of hardware, software needs to adapt and take advantage of these advancements. SQL Server 2014 introduced In-Memory OLTP to address these hardware trends and durability requirements.

There are several reasons why you would want to use high-performance memory-optimized OLTP operations:

• It naturally integrates with SQL Server relational database.
• It supports Full ACID properties.
• It helps with non-blocking multi-version optimistic concurrency control, eliminating the need for locks or latches.

Let’s dive into a working example to understand how In-Memory OLTP works.

Working Example

In this example, we will create a database with a file group that contains memory-optimized data. We will also create a table with memory_optimized set to enabled and a stored procedure that is natively compiled. We will compare the performance of this stored procedure with a regular stored procedure.

Step 1: Create the Database

CREATE DATABASE InMemory ON PRIMARY (
  NAME = InMemoryData,
  FILENAME = 'd:\data\InMemoryData.mdf',
  size = 200MB
),
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA (
  NAME = [InMemory_InMem_dir],
  FILENAME = 'd:\data\InMemory_InMem_dir'
)
LOG ON (
  name = [InMem_demo_log],
  Filename = 'd:\data\InMemory.ldf',
  size = 100MB
)
GO

Step 2: Create the Tables

USE InMemory
GO

-- Create a Simple Table
CREATE TABLE DummyTable (
  ID INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
)
GO

-- Create a Memory Optimized Table
CREATE TABLE DummyTable_Mem (
  ID INT NOT NULL,
  Name VARCHAR(100) NOT NULL CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 3: Create the Stored Procedures

-- Regular Stored Procedure - Simple Insert
CREATE PROCEDURE Simple_Insert_test AS
BEGIN
  SET NOCOUNT ON
  DECLARE @counter AS INT = 1
  DECLARE @start DATETIME
  SELECT @start = GETDATE()

  WHILE (@counter <= 100000)
  BEGIN
    INSERT INTO DummyTable VALUES (@counter, 'SQLAuthority')
    SET @counter = @counter + 1
  END

  SELECT DATEDIFF(SECOND, @start, GETDATE()) AS [Simple_Insert in sec]
END
GO

-- Natively Compiled Stored Procedure - InMemory Insert
CREATE PROCEDURE ImMemory_Insert_test
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
  ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
  DECLARE @counter AS INT = 1
  DECLARE @start DATETIME
  SELECT @start = GETDATE()

  WHILE (@counter <= 100000)
  BEGIN
    INSERT INTO dbo.DummyTable_Mem VALUES (@counter, 'SQLAuthority')
    SET @counter = @counter + 1
  END

  SELECT DATEDIFF(SECOND, @start, GETDATE()) AS [InMemory_Insert in sec]
END
GO

Step 4: Compare the Performance

Now, let’s execute the stored procedures and measure the time taken:

-- Running the test for Insert
EXEC Simple_Insert_test
GO

EXEC ImMemory_Insert_test
GO

The results will show the time taken by each stored procedure. You will notice a significant improvement in performance with the InMemory Insert stored procedure, taking nearly 0 seconds compared to the Simple Insert stored procedure which takes around 12 seconds.

Step 5: Clean up

-- Clean up
USE MASTER
GO

DROP DATABASE InMemory
GO

Analysis of Results

From the test results, it is clear that In-Memory OLTP significantly improves the performance of queries and stored procedures. By implementing In-Memory OLTP, you can take advantage of the benefits of storing and processing data in memory, resulting in faster and more efficient operations.

Implementing In-Memory OLTP involves following specific steps for filegroup and table creation. However, the end result is a much better performance compared to traditional disk-based operations.

Start exploring In-Memory OLTP in SQL Server and experience the performance boost it can bring to your applications!