Home > SQL Server 2014 > Getting Started with Hekaton (Memory Optimized) tables | SQL Server 2014

Getting Started with Hekaton (Memory Optimized) tables | SQL Server 2014


Hekaton, which also means in-Memory or Memory Optimized Tables, is a Microsoft Code Project for its new version of SQL Server 2014. This version of SQL Server mainly focuses on the high performance In-Memory Database workloads. Which means this version includes Memory Optimized Tables which will reside into Server’s memory and efficiently provides execution of Business Logic by completely reducing I/O between Disk & Memory.

So, as soon as a Memory Optimized Table is created the data it contains will also load into Memory. Let’s see here a Hands-On with Hekaton Tables by simple T-SQL Scripts:

–> Let’s first create a Test Database:

USE [master]
GO

CREATE DATABASE [ManTest]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'ManTest', 
	FILENAME = N'D:\SQLServer2014\MSSQL\DATA\ManTest.mdf', 
	SIZE = 5120KB, 
	FILEGROWTH = 1024KB 
 )
 LOG ON ( 
	NAME = N'ManTest_log', 
	FILENAME = N'D:\SQLServer2014\MSSQL\Log\ManTest_log.ldf', 
	SIZE = 1024KB, 
	FILEGROWTH = 10%
 )
GO

–> Now we will create a FileGroup and associate the [ManTest] Database with it:

USE [ManTest]
GO

-- Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE [ManTest] 
	ADD FILEGROUP [ManTestFG] CONTAINS MEMORY_OPTIMIZED_DATA 
GO

-- Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE [ManTest]
ADD FILE
  ( NAME = ManTestFG_file1,
    FILENAME = N'D:\SQLServer2014\MSSQL\DATA\Datasample_database_1') -- You might need to check and correct the path here.
TO FILEGROUP ManTestFG
GO

A SQL Server FileGroup is required to store memory_optimized_data in a SQL Server Database. As per MSDN this FileGroup will be used to guarantee durability of memory-resident data in the event of a server crash or restart. During the crash recovery phase in server startup, the data is recovered from this FileGroup and loaded back into memory.

–> Now we will create Memory Optimized Table with new Options added in CREATE TABLE syntax:

-- Create memory optimized table and hash indexes on the memory optimized table:
CREATE TABLE dbo.Person_memoryOptimizedTable
(
	PersonID INT NOT NULL
		PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 19972),
	
	PersonName NVARCHAR(100) NOT NULL, 
	
	DateAdded DATETIME NOT NULL

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

The Options:
– NONCLUSTERED HASH WITH : Clustered Index not allowed (as indexes are not stored, but re-created after SQL Server is restarted), so a PK should be defined with NonClustered Index of Type ‘Hash’.
– BUCKET_COUNT : Indicates the number of buckets that should be created in the Hash index.
– MEMORY_OPTIMIZED=ON : makes the table Memory Optimized.
– DURABILITY = SCHEMA_AND_DATA : makes the Table and its Data available all the time in-Memory.

Note:
– Indexes can only be created online with CREATE TABLE statement.
– If you don’t provide the DURABILITY = SCHEMA_AND_DATA option, then the Data will be persisted in the Table only.
– The Option DURABILITY = SCHEMA_ONLY will not make the Table Data Durable on Disk and data will lost after Server Restart or Crash, but the Table Schema will be available.

–> Let’s insert some records in the table created above:

-- Inserting records into the Memory Optimized Table from [Person] table in [AdventureWorks] DB:
INSERT INTO dbo.Person_memoryOptimizedTable
SELECT 
	[BusinessEntityID], 
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS PersonName, 
	GETDATE() AS DateAdded
FROM [AdventureWorks2012].[Person].[Person]

You will get following error:
Msg 41317, Level 16, State 3, Line 34
A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

This means that you cannot insert records into Memory Optimized Tables across databases.
So, we have a workaround here, we will insert records into a #temp table first and then insert from this #temp table.

-- So, I'll insert records from [AdvantureWorks] Database to #temp table first:
SELECT 
	[BusinessEntityID], 
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS PersonName, 
	GETDATE() AS DateAdded
into #tempPerson
FROM [AdventureWorks2012].[Person].[Person]
-- (19972 row(s) affected)

-- Now, I can insert records from #temp table into the Memory Optimized Table:
INSERT INTO dbo.Person_memoryOptimizedTable
SELECT 
	[BusinessEntityID], 
	PersonName, 
	DateAdded
FROM #tempPerson
-- (19972 row(s) affected)

-- Let's check contents of the Table:
SELECT * 
FROM Person_memoryOptimizedTable
GO

So, we here saw how to create Memory Optimized Tables and create a Separate Database to store them by allocating a FileGroup to the DB.

-- Final Cleanup
DROP TABLE dbo.Person_memoryOptimizedTable

DROP TABLE #tempPerson
GO

USE master
GO

DROP DATABASE ManTest
GO

 

Update: Know more about In-Memory tables:


 

Next post I’ll discuss more about Memory Optimized tables, their benefits and limitations.
 


Advertisement
  1. SelvakumarSubramaniam
    August 2, 2013 at 12:30 pm

    hi manoj
    can i install 2014 CTP1 in window 7?

    • August 2, 2013 at 12:58 pm

      As mentioned in Prerequisites, it can only be installed on Windows 8 or Windows Server 2012 or Windows Server 2012 R2. And the server Hardware should be x64 type.

      But you can give it a try installing on Windows 7, also do let us know!!!

      • SelvakumarSubramaniam
        August 2, 2013 at 4:30 pm

        Sure Manoj.but i heard WIN 7 sp1 will support.

  2. Enders
    September 2, 2013 at 8:04 pm

    Have you tried a common table expression instead of a temp table ?
    Or tried to make a view which points to that other table ?

  3. Scott
    March 21, 2014 at 10:46 pm

    How did you decide on the number of buckets fort the hash index? Is it a function of the expected amount of data?

  4. Sheethal Jayalingappa
    January 8, 2017 at 2:54 pm

    Hi Manoj,
    I have a problem in my code related to InMemory table updates. I encountered the same error 41317 as I was taking data from a diskbased table and updating InMemory table. Your suggestion to move it into temp table worked. However, if I do this within Begin transaction scope, it does not update the table.

    Appreciate your suggestion

  1. September 25, 2013 at 6:08 pm
  2. December 19, 2013 at 6:38 pm
  3. March 24, 2014 at 8:31 am
  4. June 3, 2015 at 7:51 am
  5. July 31, 2015 at 6:24 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: