In SQL Server 2014, Microsoft introduced a new database engine component called In-Memory OLTP, also known as project “Hekaton”. This component is fully integrated into the SQL Server Database Engine and is optimized for OLTP workloads accessing memory resident data. In-Memory OLTP allows us to create memory optimized tables, which offer significant performance improvements for typical OLTP workloads.

The main objective of memory optimized tables is to ensure that highly transactional tables can reside in memory and remain in memory forever without losing a single record. The In-Memory OLTP engine is designed to ensure higher concurrency and minimal blocking. It alleviates the issue of locking by using a new type of multi-version optimistic concurrency control. It also reduces waiting for log writes by generating less log data and needing fewer log writes.

To use In-Memory OLTP, it needs to be installed as part of the SQL Server setup application. However, it is important to note that the In-Memory OLTP components can only be installed with a 64-bit edition of SQL Server 2014 and are not available with 32-bit editions.

Creating Databases

Any database that will store memory-optimized tables must have a MEMORY_OPTIMIZED_DATA filegroup. This filegroup is specifically designed to store the checkpoint files needed by SQL Server to recover the memory-optimized tables. To create a database that can support memory-optimized tables, the CREATE DATABASE statement needs to include the FILEGROUP option CONTAINS MEMORY_OPTIMIZED_DATA.

Here is an example of a CREATE DATABASE statement for a database that can support memory-optimized tables:

CREATE DATABASE InMemoryDB
ON
PRIMARY(NAME = [InMemoryDB_data],
FILENAME = 'D:\data\InMemoryDB_data.mdf', size=500MB),
FILEGROUP [SampleDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = [InMemoryDB_mod_dir],
FILENAME = 'S:\data\InMemoryDB_mod_dir'),
(NAME = [InMemoryDB_mod_dir],
FILENAME = 'R:\data\InMemoryDB_mod_dir')
LOG ON (name = [SampleDB_log], Filename='L:\log\InMemoryDB_log.ldf', size=500MB)
COLLATE Latin1_General_100_BIN2;

It is also possible to add a MEMORY_OPTIMIZED_DATA filegroup to an existing database using the ALTER DATABASE statement.

Creating Tables

There is no major syntactical difference between creating a disk-based table and a memory-optimized table. However, there are a few restrictions and essential extensions for memory-optimized tables. Any memory-optimized table should use the MEMORY_OPTIMIZED = ON clause in the CREATE TABLE statement. Additionally, a memory-optimized table should always be defined with a DURABILITY value, which can be either SCHEMA_AND_DATA or SCHEMA_ONLY.

Here is an example of creating a memory-optimized table:

CREATE TABLE Mem_Table
(
[Name] VARCHAR(32) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
[City] VARCHAR(32) NULL,
[State_Province] VARCHAR(32) NULL,
[LastModified] DATETIME NOT NULL,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

A memory-optimized table must always have an index. For tables created with DURABILITY = SCHEMA_AND_DATA, a PRIMARY KEY constraint should be declared at the time of creating the table.

Indexing Memory Optimized Tables

A memory-optimized table must always have an index for tables created with DURABILITY = SCHEMA_AND_DATA. This can be achieved by declaring a PRIMARY KEY constraint at the time of creating the table.

Here is an example of creating a PRIMARY KEY index as a HASH index:

CREATE TABLE Mem_Table
(
[Name] VARCHAR(32) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
[City] VARCHAR(32) NULL,
[State_Province] VARCHAR(32) NULL,
[LastModified] DATETIME NOT NULL,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

It is important to note that at this point, the only collation supported for any indexes on memory-optimized tables is BIN2 collation.

Performance Gains with Memory-Optimized Tables

Memory-optimized tables can significantly improve performance for highly transactional business tables. The durability option SCHEMA_ONLY is even faster as it does not persist data to disk, making it supremely fast.

Here is an example of comparing the time taken to insert 100,000 records into different types of tables:

-- Creating a disk-based table
CREATE TABLE dbo.Disktable
(
Id INT IDENTITY,
Name CHAR(40)
)

-- Creating a memory-optimized table with durability SCHEMA_AND_DATA
CREATE TABLE dbo.Memorytable_durable
(
Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (bucket_count =1000000),
Name CHAR(40)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

-- Creating a memory-optimized table with durability SCHEMA_ONLY
CREATE TABLE dbo.Memorytable_nondurable
(
Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (bucket_count =1000000),
Name CHAR(40)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

-- Inserting 100,000 records into dbo.Disktable and observing the time taken

-- Inserting 100,000 records into dbo.Memorytable_durable and observing the time taken

-- Inserting 100,000 records into dbo.Memorytable_nondurable and observing the time taken

Based on the above example, memory-optimized tables can provide significant performance gains compared to disk-based tables.

In conclusion, SQL Server’s In-Memory OLTP, or project “Hekaton”, offers a powerful solution for improving performance in OLTP workloads. By utilizing memory-optimized tables and the In-Memory OLTP engine, businesses can achieve better performance for their highly transactional tables. It is important to consider the durability options and index requirements when creating memory-optimized tables. Overall, In-Memory OLTP is a valuable feature to explore for optimizing SQL Server performance.