How to Design Memory Optimize Database using SQL Server In-Memory OLTP

‘In-Memory OLTP’ also known as ‘Hekaton’ and ‘In-Memory Optimization’ is in-memory processing technology that allows you to get most out of SQL Server for your transactional workloads. This feature was introduced with SQL Server 2014 release. This post demonstrates the usage of In-Memory OLTP feature with examples.

Create A Database
1
2
-- Create a Database
CREATE DATABASE TestInMemoryOLTPDB

 

Create The Memory Optimized Filegroup

To create memory-optimized tables, you must first create a memory-optimized filegroup to hold data for our database. In case of server crash, unexpected restarts this filegroup will ensure durability and data recovered from this filegroup to memory.

You can only create one memory-optimized filegroup per database. You need to explicitly mark the filegroup as containing MEMORY_OPTIMIZED_DATA.

1
2
ALTER DATABASE TestInMemoryOLTPDB
ADD Filegroup TestInMemoryOLTPDB_FileGroup CONTAINS MEMORY_OPTIMIZED_DATA

The key word “MEMORY_OPITIMIZED_DATA” tells SQL Server, this file group is in memory and will store memory-optimized objects.

NOTE:
It can raise an SQL error saying, ‘The operation ‘AUTO_CLOSE’ is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.’
To resolve that you need to set ‘AUTO_CLOSE’ OFF.  Read here.

 

You need to add one or more data container to the MEMORY_OPTIMIZED_DATA filegroup.

1
2
3
4
ALTER DATABASE TestInMemoryOLTPDB
ADD FILE (Name = 'TestInMemoryOLTPDB_P',
FileName = 'C:\Dev\SQL Server\TestInMemoryOLTPDB_P')
TO FileGroup TestInMemoryOLTPDB_FileGroup

NOTE:
Once you create a memory-optimized filegroup, you can’t remove it. To do that, you need to drop the database.

 

Create Memory-Optimized Tables

Now, we can create memory-optimized tables to hold our data. See the below code:

1
2
3
4
5
6
7
8
9
10
11
12
13
USE [TestInMemoryOLTPDB]
 
-- create a durable (data will be persisted) memory-optimized table
CREATE TABLE dbo.Employee
(
ID INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
Name VARCHAR(200)  NULL,
Address VARCHAR(200)  NULL,
CreatedDate DATETIME  NULL
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
 
GO

By mentioning “MEMORY_OPTIMIZED = ON” in WITH clause, we are telling SQL Server that this is a memory-optimized table.

Unlike disk-based tables, memory-optimized tables support two kind of indexes :

  1. Non-Clustered hash indexes
  2. Non-Clustered range indexes

Memory-optimized tables can be durable(data persistence) or non-durable in nature and that is specified using DURABILITY value.
To create a durable memory-optimized table, use DURABILITY = SCHEMA_AND_DATA and
for non-durable, use DURABILITY = SCHEMA_ONLY

Loading Data Into The Memory-Optimized Tables

 Let’s add some dummy records. See the below codes:

1
2
3
4
5
6
7
8
DECLARE @i INT = 1
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.Employee (ID,Name,Address,CreatedDate)
VALUES(@i, 'EMP'+@i, NULL,GETDATE())
 
SET @i = @i+1
END

 

Validate the Table Data

Try to retrieve the table data. If it returns added data, means its working.

1
SELECT * FROM Employee  WHERE ID = 2000

So, it was all about In-Memory OLTP feature, if you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.