In-Memory OLTP (Hekaton)

By Lori Brown | SQL Administration

May 13

 Available in SQL Server 2014 Enterprise Edition

In-Memory OLTP is a new lock-free, latch-free, optimistic concurrency approach to storing data in memory. While it may seem reminiscent of DBCC PINTABLE is it definitely not the same since data in memory-optimized tables is not organized in pages, and does not use the Buffer Pool. It is designed to eliminate locking delays by incurring less latching and locking for high volume insertion workloads and can drastically improve performance. It gives you a way to get around locking and latching issues without implementing partitioning which requires code changes.

Create a MEMORY OPTIMIZED DATA filegroup:

Note: You can have only one filestream filegroup for memory optimized tables but you can create multiple files and place them into the filegroup.

In SSMS, go to the properties of a database that you want to enable to use In-Memory OLTP and click on the Filegroups page. Under MEMORY OPTIMIZED DATA, click Add filegroup and then enter a name for the filegroup.

Blog_20150513_1

Then add a file to the filegroup, click the Files page, click Add and then set up the new file with a Logical Name, File Type = FILESTREAM Data and Filegroup = the Memory Optimized filegroup that was just created.

Blog_20150513_2

Create Memory Optimized Tables:

Create memory optimized tables by specifying the MEMORY_OPTIMIZED = ON clause to differentiate it from regular disk based tables. Specifying the MEMORY_OPTIMIZED = ON clause tells the In-Memory OLTP engine to handle this table instead of the SQL database engine. You might wonder how data is put back into memory especially after a server restart of crash… This is done by SQL recovering objects by reading data into memory from the transaction log and checkpoint files on recovery. BUCKET COUNT and DURABILITY must be set when you create the In-Memory table.

CREATE TABLE [RoadRaces](

[RaceID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000),

[RaceName] VARCHAR(500) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [RName] HASH WITH (BUCKET_COUNT = 500000),

[LengthMiles] INT NULL,

[State] CHAR(2) NULL

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

BUCKET COUNT and DURABILITY

  • BUCKET COUNT should be 2X the number of unique values of the column.
  • DURABILITY = SCHEMA_AND_DATA means that you want the data and table schema persisted and able to be recovered on server restart or after a crash.
  • DURABILITY = SCHEMA_ONLY means that the only the schema would be persisted and not the data on server restart or after a crash.

When done a dll and other files will be created.   You can find them in this folder: <Drive letter>:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\DATA\xtp\<database id>\ You should notice that there will be a new XTP folder and a subfolder with a number which is the database id. The number on the end of the dll name is the object id of the table that was just created.

Blog_20150513_3

Indexes:

Memory optimized tables can have up to 8 non-clustered indexes but no clustered index and must be created when the table is created.  Indexes can be created on string columns but only if they use a BIN2 collation.  You can query the BIN2 collations to choose from by checking sys.fn_helpcollations. When indexes are created you cannot drop or modify the index so you must plan well before you create it.   There are 2 types of indexes (Hash Index and Range Index) and all are inherently covering indexes meaning it will virtually contain all of the columns in the table.

  • Hash Index – best for joins using the “=” operator
  • Range Index – best for queries with “>” or “<” operators.

 

Create Natively Compiled Stored Procedures:

You can also create natively compiled stored procedures that use the In-Memory OLTP engine. These stored procedures can only access memory optimized tables.   Creating a natively compiled stored procedure is very much like creating an In-Memory OLTP table. The WITH NATIVE_COMPILATION option tells SQL that the procedure is to be handled by the In-Memory OLTP engine.

CREATE PROCEDURE [dbo].[NewRoadRace] @RaceID INT, @RaceName VARCHAR(500), @LengthMiles INT, @State CHAR(2)

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘us_english’)

–Insert

INSERT INTO dbo.RoadRaces (RaceID, RaceName, LengthMiles, [State]) VALUES (@RaceID, @RaceName, @LengthMiles, @State)

END

GO

Now let’s put all the parts into motion. Execute the stored proc just like you would any other stored proc.

EXECUTE [dbo].[NewRoadRace] @RaceID = 100, @RaceName = ‘Run The Country 5K’, @LengthMiles = 3.1, @State = ‘TX’

EXECUTE [dbo].[NewRoadRace] @RaceID = 101, @RaceName = ‘Run The Country 10K’, @LengthMiles = 6.2, @State = ‘TX’

And query the In-Memory OLTP table as usual.

Blog_20150513_4

When considering using In-Memory OLTP be aware that code changes may be needed especially for stored procedures. See this for more info:  https://msdn.microsoft.com/en-us/library/dn246937.aspx 

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>