In Memory OLTP in SQL Server 2014, SQL Server 2016 and SQL Server 2017

dbtut

6 years ago

In this article, we will explain what is In Memory OLTP and how to use this feature in SQL Server 2014 and what is limitations. Then, we’ll examine how this feature develops with SQL Server 2016 and SQL Server 2017 step by step.

What is In Memory OLTP

In Memory OLTP is a feature announced with SQL Server 2014. With this feature, we can now store our data in memory. When we say we can store the data in memory, a lot of people who are interested in sql server think it is buffer pool. As an important point, data is not stored in the buffer pool, but directly in memory. I mentioned the buffer pool in my article “What is Buffer Pool Extension“.

Of course, it is often not possible to store all of our data in memory. We can use this feature for our critical tables.

The most important feature of “In Memory OLTP” is that there is not lock and latch. So it increases performance very seriously. Microsoft specifies that there is a performance increase of up to 5-30 times.

Normal tables that we know are called as Disk Based Table. When you select Disk Based Tables, the data is transferred from disk to cache. Memory Optimized Tables are already stored in memory, so no such process is required.

Memory Optimized Tables uses the same transaction log file as Disk Based Tables. As you know, this file is stored on disk. You may want to read my article “What is SQL Server Transaction Log”.

Indexes are always stored in memory. They are automatically rebuild after restart. This increases the recovery time. Therefore, you need to be careful when creating an index. You may want to read the below article for learning more about recovery time.

“What is Database Checkpoint”

Access to Memory Optimized tables is the same as accessing normal tables. The same application can use both memory optimized tables and disk based tables in the same database.

Memory Optimized tables use optimistic concurrency control. You may want to read my article “Optimistic and Pessimistic Concurrency Control”

Is it Safe to Store Data in Memory?

Storing data in memory does not cause data loss when the server unexpectedly shuts down. Because each committed transaction is written to the transaction log. In addition, some checkpoint files (data and delta files) are stored on the memory optimized filegroup so that the data stored in memory is not lost and the database can be recovered in a healthy way. These data and delta files are generated asynchronously from the transaction log(when checkpoint triggered).

After the data and delta files are generated from the transaction log file, transaction log file will be truncated when checkpoint triggered. If data and delta files are not generated, even if checkpoint is triggered, transaction log will not truncate.

Data and Delta Files

All data and the new inserts are stored in the data files.

The pointers of the deleted records are stored in the delta files.

So we store the data in both memory and disk.

The most important “In Memory OLTP” concepts are as follows:

Memory Optimized Table
Natively Compiled Stored Procedure

The best performance in In Memory OLTP is achieved by using natively compiled stored procedures with memory optimized tables. You may want to read my article “What is Natively Compiled Stored Procedure and How to Use It”.

Memory Optimized Tables in SQL Server 2014

First, let’s create the memory optimized table and then explain these commands.

In order to create the table, we need to create the memory optimized filegroup with the help of the following script.

After creating the filegroup in the script below, we create at least one file. The files we create in Memory optimized filegroup are also called containers. Microsoft recommends that we create more than one container on different disks for more performance.

I recommend creating at least 4 containers on two disks. Because, SQL Server uses round robin algorithm to write “data files” and “delta files” to the containers. Thus, first “data file” and the first “delta file” will be in the first container and second data file and second delta file will be in the second container. Because data files and delta files are properly distributed to containers, performance will be better.

Add Memory Optimized File Group

ALTER DATABASE [Test]
ADD FILEGROUP [InMemoryFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE[Test]
ADD FILE(
NAME=N'InMemoryFile1',
FILENAME=N'C:\MSSQL\InMemoryFilegroup\InMem1'
) TO FILEGROUP [InMemoryFileGroup]
GO
ALTER DATABASE[Test]
ADD FILE(
NAME=N'InMemoryFile2',
FILENAME=N'C:\MSSQL\InMemoryFilegroup\InMem2'
) TO FILEGROUP [InMemoryFileGroup]
GO
ALTER DATABASE[Test]
ADD FILE(
NAME=N'InMemoryFile3',
FILENAME=N'D:\MSSQL\InMemoryFilegroup\InMem3'
) TO FILEGROUP [InMemoryFileGroup]
GO
ALTER DATABASE[Test]
ADD FILE(
NAME=N'InMemoryFile4',
FILENAME=N'D:\MSSQL\InMemoryFilegroup\InMem4'
) TO FILEGROUP [InMemoryFileGroup]

ALTER DATABASE [Test]

ADD FILEGROUP [InMemoryFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE[Test]

ADD FILE(

NAME=N'InMemoryFile1',

FILENAME=N'C:\MSSQL\InMemoryFilegroup\InMem1'

) TO FILEGROUP [InMemoryFileGroup]

ALTER DATABASE[Test]

ADD FILE(

NAME=N'InMemoryFile2',

FILENAME=N'C:\MSSQL\InMemoryFilegroup\InMem2'

) TO FILEGROUP [InMemoryFileGroup]

ALTER DATABASE[Test]

ADD FILE(

NAME=N'InMemoryFile3',

FILENAME=N'D:\MSSQL\InMemoryFilegroup\InMem3'

) TO FILEGROUP [InMemoryFileGroup]

ALTER DATABASE[Test]

ADD FILE(

NAME=N'InMemoryFile4',

FILENAME=N'D:\MSSQL\InMemoryFilegroup\InMem4'

) TO FILEGROUP [InMemoryFileGroup]

Create Memory Optimized Table

CREATE TABLE  dbo.MemoryOptimizedTable_Example
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
[Name] varchar(250) NOT NULL
INDEX IDX_Name  HASH ([Name]) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

CREATE TABLE dbo.MemoryOptimizedTable_Example

(

ID int NOT NULL

PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT =1024),

[Name] varchar(250) NOT NULL

INDEX IDX_Name HASH ([Name]) WITH (BUCKET_COUNT =1024)

) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

You may receive an error as follows when you want to create the table.

Msg 12329, Level 16, State 103, Line 1

The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

This error is due to the fact that memory optimized tables do not support all collation types.

You can see the collation types supported by the following script.

SELECT NAME AS [Collation] ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] 
FROM sys.fn_helpcollations() WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252

1 2	SELECT NAME AS [Collation] ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] FROM sys.fn_helpcollations() WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252

In order to create the table, you can change the collation of the database with the following script.

USE master
GO
ALTER DATABASE Test COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE master

ALTER DATABASE Test COLLATE SQL_Latin1_General_CP1_CI_AS

Or you can create a table with the help of the following script without changing the database collation.

CREATE TABLE  dbo.MemoryOptimizedTable_Example
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
[Name] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
INDEX IDX_Name  HASH ([Name]) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

CREATE TABLE dbo.MemoryOptimizedTable_Example

(

ID int NOT NULL

PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT =1024),

[Name] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

INDEX IDX_Name HASH ([Name]) WITH (BUCKET_COUNT =1024)

) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

Oops. We receive the error again. I could share with you the script that runs directly without receiving these errors, but I wanted you to see these errors too. Now the error we received is as follows.

Msg 12328, Level 16, State 102, Line 1

Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint or index. See previous errors.

To create the index for a varchar column, the collation must be BIN2. With the help of the following query, you can list the collations of type BIN2 and choose the one that suits you.

SELECT NAME AS [Collation] ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] FROM sys.fn_helpcollations() 
WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252 and NAME like '%BIN2%'

1 2	SELECT NAME AS [Collation] ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] FROM sys.fn_helpcollations() WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252 and NAME like '%BIN2%'

I am recreating the table with the collation of Latin1_General_BIN2 as follows.

CREATE TABLE  dbo.MemoryOptimizedTable_Example
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
[Name] varchar(250) COLLATE Latin1_General_BIN2 NOT NULL
INDEX IDX_Name  HASH ([Name]) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

CREATE TABLE dbo.MemoryOptimizedTable_Example

(

ID int NOT NULL

PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT =1024),

[Name] varchar(250) COLLATE Latin1_General_BIN2 NOT NULL

INDEX IDX_Name HASH ([Name]) WITH (BUCKET_COUNT =1024)

) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

This time we were able to create the table without error.

If we examine the script a little, there are a few things that are different.

MEMORY_OPTIMIZED = ON

Indicates that the table is a memory optimized table.

HASH INDEX

Memory optimized tables do not support normal btree index. Each memory optimized table must have at least one index. When Hash Index is created, the data is placed in the cells we specify with bucket_count by passing through the hash algorithm. If you use “=” in the where clause of the query, this index type is useful.

BUCKET_COUNT

Specifies the number of unique rows in that column when creating the hash index in a column. If you do not specify a correct number for bucket count, the performance decreases and the recovery time of the database is increased. If you can not specify bucket_count, you can use the range index instead.

RANGE INDEX

The structure is similar to B-tree indexes and is called as Bw-tree. If you do not specify the index as hash, the range index will be created by default. If you want to query a specific range in a Query, the range index will work more efficiently according to the Hash Index.

DURABILITY

It can be SCHEMA_AND_DATA or SCHEMA_ONLY.

If set to SCHEMA_AND_DATA, both the schema structure and the data are retained in the event of an unexpected crash or always on failover.

If set to SCHEMA_ONLY, only the schema structure is retained in the event of an unexpected crash or always on failover. So you lose your data.

LIMITATIONS

In Memory OLTP has a number of limitations that you won’t like very much in SQL Server 2014.

Memory Optimized Tables does not support the following items in SQL Server 2014:

DML Trigger
XML ve CLR data types.
LOB data types like Varchar(MAX) .
FOREIGN KEY
CHECK Constraint
ALTER TABLE
Creating Index after creating table.(You must re-create the table)
TRUNCATE TABLE
does not suppot more than 8 indexes.

What’s new with SQL Server 2016 In Memory OLTP?

You can see the In Memory OLTP differences, between SQL Server 2014 and SQL Server 2016 in the following table.

	SQL Server 2014	SQL Server 2016
Max Table Size	256 GB	No Limit
Transparent Data Encryption (TDE)	Not Supported	Supported
Nested Natively Compiled Procedure	Not Supported	Supported
Natively-compiled scalar UDF (user defined function)	Not Supported	Supported
ALTER TABLE, PROCEDURE, INDEX	Not Supported	Supported (OFFLINE)
AFTER TRIGGER	Not Supported	Supported (WITH NATIVE_COMPILATION)
Index on nullable columns	Not Supported	Supported
Index in non-BIN2 columns	Not Supported	Supported
Foreign Keys	Not Supported	Supported
Check/Unique Constraints	Not Supported	Supported
Parallelism	Not Supported	Supported
OUTER JOIN, OR, NOT, UNION [ALL],DISTINCT, EXISTS, IN	Not Supported	Supported
Multiple Active Result Sets (MARS)	Not Supported	Supported
Table Design in SSMS	Not Supported	Supported
ColumnStore Index in Memory Optimized Tables	Not Supported	Supported
Varbinary(max), varchar(max), nvarchar(max)	Not Supported	Supported
“EXECUTE AS OWNER” Statement is neededin “Natively Compiled SP”?	Needed	Not Needed

By rebuilding Index, you can modify BUCKET_COUNT as follows.

ALTER TABLE dbo.MemoryOptimizedTable_Example
  ALTER INDEX IDX_ID
  REBUILD WITH (BUCKET_COUNT = 1042);

ALTER TABLE dbo.MemoryOptimizedTable_Example

ALTER INDEX IDX_ID

REBUILD WITH (BUCKET_COUNT = 1042);

What’s new with SQL Server 2017 In Memory OLTP?

sp_spaceused can be used for memory optimized tables.
sp_rename memory can be used for memory optimized tables and natively compiled stored modules.
CASE support for Natively compiled modules.
Memory optimized tables had 8 index limits. This limit has been removed.
TOP (N) WITH TIES is now supported in Natively compiled modules.
ALTER TABLE operation is now faster in Memory optimized tables.
Transaction Log redo operation is now parallel. This shortened the recovery time.
Memory optimized filegroups can be stored in Azure Blob Storage.
Memory optimized tables support computed column. And we can create index on this computed column.
JSON functions can be used in Natively compiled modules and in CHECK Constraint.
CROSS APPLY operator can be used in Natively compiled modules.
Rebuild operation of nonclustered indexes in memory optimized tables has been optimized. With this improvement, recovery time for databases that has memory optimized table with non clustered index has been shortened.