In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident.

So I wanted to have a play – and I immediately came across a couple of gotchas:

Gotcha 1: Because SQL 2014 is only at CTP1 compatibility with earlier versions is not guaranteed and therefore Microsoft won’t let you install this side by side with any other versions.

Gotcha 2: Because of gotcha number one you’ll probably decide, like me, to install in a virtual machine. Using Oracle Virtual Box you might strike this error when you try to create a memory optimised filegroup:

Msg 41342, Level 15, State 1, Line 5

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors. See SQL Server Books Online for information on supported models.

To resolve this – navigate to the virtual box install folder and run this command:

VBoxManage setextradata [vmname] VBoxInternal/CPUM/CMPXCHG16B 1

I had to restart the guest and the host for this change to stick.

DDL.

Now with that all working time to create the database.

CREATE DATABASE InMemDB 
GO

Add a filegroup for the in memory objects (alter the path for your instance).

ALTER DATABASE InMemDB ADD FILEGROUP InMemDB_mod CONTAINS MEMORY_OPTIMIZED_DATA 
ALTER DATABASE InMemDB ADD FILE (name='InMemDB_mod1', filename='E:\SQLData\InMemDB_mod1') TO FILEGROUP InMemDB_mod 
GO

A database can contain a mix of in memory tables (and the new natively compiled stored procedures) and traditional disk based tables. The in memory tables are marked with the keywords MEMORY_OPTIMIZED=ON and they must have at least one hash index – it is not possible to create a heap.

USE InMemDB 
GO
CREATE TABLE dbo.Table1 ( 
   Id_tb1 int not null primary key nonclustered hash with (bucket_count=20480), 
   Int_Val int not null index ix_Int_Val nonclustered hash with (bucket_count=10240), 
   CreateDate datetime2 not null, 
   [Description] varchar(255) 
) 
WITH (MEMORY_OPTIMIZED=ON) 
GO
CREATE TABLE dbo.Table2 ( 
   Id_tb2 int not null primary key nonclustered hash with (bucket_count=4096), 
   Int_Val int not null, 
   CreateDate datetime2 not null, 
   Id_tb1_fk int, 
   index ix_Int_Val nonclustered hash (Int_Val) with (bucket_count=4096) 
) 
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) 
GO

In memory tables have a durability property that can be set to SCHEMA_AND_DATA or SCHEMA_ONLY. There are two new columns in sys.tables to track this. SCHEMA_ONLY tables are volatile, SCHEMA_AND_DATA are persisted to disk.

SELECT name,type_desc,durability_desc FROM sys.tables

The hash indexes can be tracked with a new catalog view.

SELECT * FROM sys.hash_indexes

DML.

The two big selling points of in memory OLTP are improved performance with entirely memory resident data accessed with hash indexes and use of an optimistic multiversion concurrency control … no locking.

So let’s run some DML and test this out. Insert a row:

BEGIN TRAN
INSERT dbo.Table1 VALUES (1,427,getdate(),'Insert transaction')
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions
COMMIT TRAN

The sys.dm_tran_locks DMV will return a shared object lock and a Schema shared lock on the database. The new sys.dm_db_xtp_transactions is a new DMV for in memory OLTP and returns information about current transactions against the database.

Now run an update inside an explicit transaction. Note that we will get an error if we don’t use the WITH(SNAPSHOT) hint.

BEGIN TRAN
UPDATE dbo.Table1  WITH (SNAPSHOT) 
SET [Description] = 'Updated transaction' 
WHERE Id_tb1 = 1
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

In another session run a select.

SELECT * 
FROM dbo.Table1
WHERE Id_tb1 = 1

The select returns the pre updated version of the row – but there is no blocking. This is achieved using a versioning system that is, despite the keyword SNAPSHOT, not the familiar SNAPSHOT ISOLATION row versioning based in tempdb. (Details of how this is achieved can be found in the readings at the end of this blog.)

Commit the update and run the following delete.

BEGIN TRAN
DELETE dbo.Table1 WITH (SNAPSHOT)
WHERE Id_tb1 = 1
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
SELECT * FROM sys.dm_db_xtp_transactions

Note that the delete transaction is still only holding lightweight shared locks. The select in another session will now return the updated row.

SELECT * 
FROM dbo.Table1
WHERE Id_tb1 = 1

Commit the delete transaction.

Concluding Remarks.

As of CTP1 not all of the features are fully working and there are a number of limitations that are likely to roll into RTM – hopefully to be slowly improved in subsequent releases. The whitepaper in the reading list below has an exhaustive list of these restrictions but one of the biggest ones for me is that foreign key constraints are not supported.

Another big feature that is available for in memory tables are the native stored procedures. I didn’t touch on these but they are tipped to offer highly optimised performance. A stored procedure that only operates against in memory tables can be marked using the WITH NATIVE_COMPILATION option and this will compile the sproc into a DLL using C code.

Further Reading.

http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/getting-started-with-sql-server-2014-in-memory-oltp.aspx

http://blogs.msdn.com/b/sqlcat/archive/2013/06/25/sql-server-in-memory-oltp-internals-overview-for-ctp1.aspx

http://research.microsoft.com/apps/pubs/default.aspx?id=156645

https://www.simple-talk.com/sql/database-administration/exploring-in-memory-oltp-engine-(hekaton)-in-sql-server-2014-ctp1/