SQL Server 2014 In-Memory OLTP

Hekaton (also known as SQL Server In-Memory OLTP) is an in-memory database for OLTP workloads built into Microsoft SQL Server. Hekaton was designed in collaboration with Microsoft Research and was released in SQL Server 2014. Traditional RDBMS systems were designed when memory resources were expensive, and was optimized for disk storage. Hekaton is instead optimized for a working set stored entirely in main memory, but is still accessible via T-SQL like normal tables.

The SQL Server 2014 In-Memory OLTP engine (a.k.a. Hekaton) is a huge change for relational databases.

Now consider memory as your new disk

Notes-

• In SQL Server 2014 you can create memory-optimized tables and indexes. Everything will be present in the memory that why we need more memory.

• Here we can also have disk-based tables and indexes. (SQL 2012 & before.)

• A memory-optimized table is a one where SQL Server will always store in memory the whole table and its indexes.

• The storage of memory-optimized tables is different from conventional storage mechanism hence there are no data pages, and no extents. There are just “data rows,” written to memory.

• For memory-optimized tables, SQL Server will never have to acquire latches nor perform I/O in order to retrieve data.

• SQL Server guarantees ACID properties of all transactions without acquiring any locks. Therefore no transaction will ever be blocked, waiting to acquire a lock.

• In the order the transactions occurred, with each row containing an index “pointer” to the next row. All “I/O” is in-memory scanning of these structures.

• Many versions of the same row can coexist at any given time. This allows concurrent access of the same row, during data modifications, with SQL Server making available the row version relevant to each transaction according to the time the transaction started relative to the timestamp values stored in the header of each row version.

Now we will have a small demo of how we can create in memory DB and in memory table.

In memory DB should have MEMORY_OPTIMIZED_DATA filegroup. This filegroup is specially created to store the checkpoint files needed by SQL Server to recover the memory-optimized tables. The syntax for creating the filegroup is almost the same as for creating a regular filestream filegroup, We just have to mention the option CONTAINS MEMORY_OPTIMIZED_DATA.

--Create an In Memory Database

/*


FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA

*/

CREATE DATABASE Mem
ON PRIMARY
(
	NAME = Mem,
	FILENAME = 'C:\Mem.mdf', size=2000 MB
)
,	FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA
	(
		NAME = [Mem_dir],
		FILENAME = 'C:\Mem_dir'
	)
	LOG ON (name = [InMem_demo_log], Filename='c:\Mem.ldf', size=1000MB
)
GO

--CREATE an In Memory table


/*
There are some syntactical differences between creating a disk based table 
and a memory optimized table. All memory-optimized table should use the 
MEMORY_OPTIMIZED = ON clause as shown in the Create Table query below.
*/


CREATE TABLE TestMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 10000 ) 
  ,[Name] [int] NOT NULL  
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )


--Insert Data in a In Memory Table
DECLARE @cntr bigint = 1
WHILE @cntr<= 100000
BEGIN
	INSERT INTO dbo.TestMem(Id,Name) VALUES(@cntr,@cntr)
	SET @cntr+=1
END

--Time taken here is -- 15 Sec

Now since we heard that this memory things if faster than the old ways to doing things. Lets create a disk based table and insert the same no of rows and checkout the timings.

CREATE TABLE TestNONMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED
  ,[Name] [int] NOT NULL  
)
GO

DECLARE @cntr1 bigint = 1
WHILE @cntr1 <= 100000
BEGIN
	INSERT INTO dbo.TestNONMem(Id,Name) 
        VALUES(@cntr1,@cntr1)
	SET @cntr1+=1
END

--Time taken -- 22 Sec

We can see that the Memory optimzed insertion took less time than the old version. See the attached snap below.

Keep Learning. We all need to learn.

http://MSBISkills.com