Getting Started with SQL Server In-Memory OLTP – Part 1

Getting Started with SQL Server In-Memory OLTP – Part 1

In-Memory OLTP in SQL Server 2014 or later is a powerful engine integrated into the SQL Server Engine, optimized for Online Transaction Processing (OLTP).

The SQL Server and .NET Hub

In-Memory OLTP (codenamed “Hekaton”) is a powerful engine which is fully integrated into SQL Server’s Database Engine and introduces new data structures for optimizing the performance of OLTP workloads. So, you can have memory-optimized tables, natively-compiled stored procedures, memory-optimized table variables, etc.

In-Memory OLTP offers a significant performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency.

In this series of articles, we will see in plain words, how we can take advantage of this technology and start using it for boosting performance of heavy workloads.

In this first article, we are going to see how easy is to create an In-Memory OLTP-enabled database and memory-optimized tables.

OK, enough talking, let’s see some code!

–create database
CREATE DATABASE InMemOLTPDB;
GO  

–use the database

USE InMemOLTPDB;
GO

–add memory-optimized file group to the database

ALTER DATABASE InMemOLTPDB ADD FILEGROUP InMemOLTPDB_mofg CONTAINS MEMORY_OPTIMIZED_DATA;

–add file for memory-optimized objects

–you can set any path you like – in this example: c:memoptdata
–if the directory does not exist you will need to create it
ALTER DATABASE InMemOLTPDB ADD FILE (name=’InMemOLTPDB_mofg1′, filename=’C:memoptdataInMemOLTPDB_mofg1′) TO FILEGROUP InMemOLTPDB_mofg;

–set the isolation level for memory-optimized tables to SNAPSHOT 

ALTER DATABASE InMemOLTPDB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Now you can create memory-optimized tables.

When creating memory-optimized tables you have two options:

  • Option 1: You can create a durable memory-optimized table which means that in the case of server crash or failover the data will be available as they will be recovered from transaction logs.
  • Option 2: You can create a non-durable table which means that in the case of a server crash or failover the data in the table will be lost.

The below script creates a sample durable memory-optimized table:

–Memory-Optimized Table: Durable
CREATE TABLE [dbo].[Person_Durable]
  (
     ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL,
 Remarks VARCHAR(50) NOT NULL,  
  )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

The below script creates a sample non-durable memory-optimized table:

–Memory-Optimized Table: Non-Durable
CREATE TABLE [dbo].[Person_Non_Durable]
  (
     ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL, 
 Remarks VARCHAR(50) NOT NULL, 
  )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

Stay tuned as in the next article we are going to run some simple experiments with In-Memory OLTP and get an indication on the performance benefits.

Check out Part 2 of this series – Now Available.

See also:


References
The SQL Server and .NET Hub
Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

What are your views on the subject? Feel free to comment!


Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.