Let’s talk about In memory OLTP

In-memory OLTP frequently referred to by its code name "Hekaton", was introduced in SQL Server 2014 but it got more popular from SQL Server 2016. This feature was being developed by Microsoft to stay in the game for high speed OLTP transaction speed and recent hardware changes in terms of the cost and throughput.

Many people utilize this feature for maximum performance where they want ultimate speed in  response from SQL server.  

The evaluating and migration of the normal disk based table and workload is made very simple by SQL server, the standard report “Transaction performance analysis overview”can scan through table and stored procedures based on usage. 

****

The data stored in the memory is separate from the buffer pool as like buffer pool paging out the disk when there is memory pressure but this is not with memory optimized tables.

Memory optimized tables use different structures to store data not like data pages. All the data gets loaded to memory at the time of SQL server startup.There is a separate checkpoint file created to just boost the database startup process. 

 Supported Data types:

• bit 

• All integer types: tinyint, smallint, int, bigint 

• All money types: money, smallmoney  

• All floating types: float, real  

• date/time types: datetime, smalldatetime, datetime2, date, time  numeric and decimal types  String types: char(n), varchar(n), nchar(n), nvarchar(n), sysname, varchar(MAX),   Varchar(MAX) 

•  Binary types: binary(n), varbinary(n), varbinary(MAX)  Uniqueidentifie

Steps to implement 

ALTER DATABASE AdventureWorks2016CTP3 

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; 

 

ALTER DATABASE AdventureWorks2016CTP3 

ADD FILEGROUP AdventureWorks2016CTP3_mod CONTAINS MEMORY_OPTIMIZED_DATA;

  

ALTER DATABASE AdventureWorks2016CTP3 

ADD FILE (name='AdventureWorks2016CTP3_mod1', 

filename='E:\data\AdventureWorks2016CTP3') 

TO FILEGROUP AdventureWorks2016CTP3_mod 

  

USE AdventureWorks2016CTP3

CREATE TABLE dbo.orderdetails

( OrderID INTEGER NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED, ItemNumber   INTEGER    NOT NULL,

  OrderDate DATETIME   NOT NULL )  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 

Performance benchmark for disk-based table and memory-optimized table.

SQLQueryStress is a simple, lightweight performance testing tool, designed to load test individual queries using this tool we tried generating load on the SQL server 

We did random inserts from 100 connections with 500 iterations on both Disk based table and memory optimized table.

As performance benchmark memory based table load was very fast , it was just 1.15 minutes to complete where on disk based table , the same operation took around 09:30 Minutes.

 

Disk based load

[RML] C:\Program Files\Microsoft Corporation\RMLUtils>     ostress.exe -n100 -r500 -S. -E -dAdventureWorks2016 -q -i"C:\Temp\Disk-based Inserts.sql"

Memory optimized table load

[RML] C:\Program Files\Microsoft Corporation\RMLUtils>    ostress.exe -n100 -r500 -S. -E -dAdventureWorks2016 -q -i"C:\temp\Memory-optimized Inserts.sql"

For new application development and design this feature is great has very sound on performance perspective but on existing tables there is some over head and challenges on data type and referential integrity if those can be handled its great feature in the SQL server 

 

--By Santosh Sethi