Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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] |
Create Memory Optimized Table
1 2 3 4 5 6 7 | 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) |
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.
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.
1 2 3 4 | USE master GO ALTER DATABASE Test COLLATE SQL_Latin1_General_CP1_CI_AS GO |
Or you can create a table with the help of the following script without changing the database collation.
1 2 3 4 5 6 7 | 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.
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.
1 2 3 4 5 6 7 | 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.
1 2 3 | 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.
