In Memory OLTP or Memory Optimized OLTP

If an enterprise server has to be upgraded to boost performance then memory can be the first and foremost resource that can be considered as a quick and cheap upgrade. Memory is not only cheap , but also very easy to install. If server supports, memory can be added without bringing the server down for maintenance.

Starting with SQL Server 2014, Microsoft introduced In Memory Tables also known as Memory Optimized Tables or In Memory OLTP. Memory optimized tables can take advantage of additional Memory (RAM) to provide better performance.

As name suggests, the data in these tables remain in memory when SQL Service is running. This does not mean that data will always vanish away when server is powered off or service interrupts. SQL Server keeps copy of data in Logs (write ahead log), if data has to be retained, the pages from log will be loaded back to memory optimized tables.

While defining the table you can specify whether the durability of the table will be Schema only or Schema and Data both.

Memory Optimized Table is brand new concept for SQL 2014 and the allocation works little differently from regular indices. Unlike regular tables, data in Memory Optimized table is stored in streams also called Data Streams. These Tables don't use IAM (Index Allocation Map) to link extent chain, because of this reason each Memory Optimized Table requires at least one index to link the data.

These are the requirements for Memory Optimized Tables are:

1. File Group designated for Memory Optimized Data,
2. Data Files in Memory Optimized File Group.
3. Disable Broker (or any feature that enables Event Notification) for that database. It is not supported.
4. At least one Index per Memory Optimized Table. Index can be Hash Index or Range Index. Hash Indexes use Buckets to organize and point the underlying data, and Range Indexes are the regular indexes using "BW"-Tree architecture.

You can either create a new Memory Optimized Database or you can add Memory Optimized Data file to existing database also.

Here is the small example to introduce with Memory Optimized OLTP/Table.


Create Database

CREATE DATABASE [MYDB]
CONTAINMENT = NONE
ON PRIMARY
(NAME ='DataFile',
FILENAME='H:\SQLDATAFILES\MYDB_DATAFILE.MDF',
SIZE = 4096KB,
MAXSIZE = 10240KB,
FILEGROWTH=1024KB
)
LOG ON
(NAME = 'LogFile',
FILENAME='H:\SQLDATAFILES\MYDB_LOGFILE.LDF',
SIZE=4096,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
)


Disable Broker


IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = 'MYDB'  AND IS_BROKER_ENABLED = 1)
          ALTER DATABASE MYDB SET DISABLE_BROKER


Add Memory Optimized File Group and Data File


ALTER DATABASE MYDB
ADD FILEGROUP [MYDB_MEMORY_OPTIMIZED_GROUP] CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE MYDB
ADD FILE
(NAME='MYDB_MEM_OPT_DAT',
FILENAME='H:\SQLDATAFILES\MYDB_MEM_OPT_DAT',
MAXSIZE=UNLIMITED

) TO FILEGROUP [MYDB_MEMORY_OPTIMIZED_GROUP]


Create Table

USE MYDB;

CREATE TABLE MYDB_INMEMORY_TABLE
(
COLUMN1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
COLUMN2 INT NOT NULL INDEX INDEX1 HASH WITH (BUCKET_COUNT=1000),
COLUMN3 CHAR(100) COLLATE LATIN1_GENERAL_100_BIN2 NOT NULL INDEX INDEX2
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)


It is all done and ready to use. Though it is recommended to create resource pooling on memory for the database so that memory utilization can be maintained and monitored.

Create Resource Pool

IF NOT EXISTS(SELECT * FROM SYS.RESOURCE_GOVERNOR_RESOURCE_POOLS WHERE NAME = 'POOL_MYDB')
BEGIN
CREATE RESOURCE POOL POOL_MYDB WITH (MAX_MEMORY_PERCENT=10);
ALTER RESOURCE GOVERNOR RECONFIGURE;
END

Map Resource Pool and Database

EXEC sp_xtp_bind_db_resource_pool 'MYDB', 'POOL_MYDB';

Take Database Offline and Online again to apply the changes

USE MASTER;
ALTER DATABASE MYDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MYDB SET ONLINE;



Please leave your feedback, if you like this article or have any comments, below.