A few hours ago Microsoft released the first public CTP of SQL Server 2014. The download and the installation went very smooth, the only new restriction is that you are not allowed to install the CTP 1 version in parallel with any other SQL Server version. Aaron Bertrand has blogged about that new behavior.

In this blog posting I want to give a brief overview how to make your first steps with Extreme Transaction Processing (XTP) that was formerly known as Hekaton. If you want to have a good overview about XTP, I recommend Kalen Delaney’s whitepaper about it, and the research whitepaper titled High-Performance Concurrency Control Mechanisms for Main-Memory Databases (can be downloaded here) that was released by Microsoft Research.

All the versioning history that XTP maintains transparently for you in the background is stored in a FILESTREAM filegroup with your SQL Server database. So the first thing that you have to do, when you want to work with XTP, is to add a new FILESTREAM Filegroup to the corresponding database. That new file group must be also marked with the property MEMORY_OPTIMIZED_DATA

-- Create new database
CREATE DATABASE TestDatabase
GO

--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE TestDatabase
ADD FILEGROUP XTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA

After you have created the new FILESTREAM file group, you have to add a new file to the file group.

-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
   NAME = N'HekatonFile1',
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HekatonFile1'
)
TO FILEGROUP [HekatonFileGroup]
GO

After we have prepared our database for XTP, we can finally add our Memory Optimized Table – that’s the name how SQL Server refers to XTP tables:

From the above screenshot you expect a very cool table wizard, but the only thing that we are currently getting is a T-SQL script template – nothing more. That reminds me back to my Service Broker days, and we still have no wizard for Service Broker in SQL Server 2014 – 8 years are just gone without any improvements in this area. Hopefully Microsoft invests a little bit more into XTP… Here is the necessary T-SQL script to create a XTP table:

-- Let's create a new Memory Optimized Table
CREATE TABLE TestTable
(
   Col1 INT NOT NULL,
   Col2 VARCHAR(100) NOT NULL,
   Col3 VARCHAR(100) NOT NULL
   CONSTRAINT chk_PrimaryKey PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON)
GO

Every table in XTP needs a primary key constraint that must be a Non-Clustered Hash Index. Clustered Hash Indexes are currently not support. You also have to specify the bucket count with the BUCKET_COUNT clause. And finally you have to mark your table as MEMORY_OPTIMIZED. Congratulations, you have created your first Memory Optimized table – it wasn’t that hard!
Working with the new table is also very easy. Let’s insert a simple record:

-- Let's insert a simple record into the new table
INSERT INTO TestTable (Col1, Col2, Col3) VALUES (1, 'Klaus', 'Aschenbrenner')
GO

But the real power of XTP is in combination with concurrent users, because there is now no Locking/Blocking/Latching anymore. Even Exclusive Locks (X) are just gone. Normally when you are running the above INSERT statement on a “normal” table, you have an IX lock on the table, and on the page, and on the record itself an X lock. But with XTP all those locks are just gone. See the following query:

-- Make an insert in an explicit transaction
BEGIN TRANSACTION

INSERT INTO TestTable (Col1, Col2, Col3) VALUES (2, 'Philip', 'Aschenbrenner')

-- No IX, X locks anymore!
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT
GO

The following screenshot shows the output from sys.dm_tran_locks. As you can see there is only a Schema Stability Lock (Sch-S) on the table itself, but the IX and X locks are just gone – very nice:

When you are running the above transactions without committing it immediately, you can also retrieve the data without blocking from a different session through a simple SELECT statement. SQL Server uses an Index Scan (NonClusteredHash) operator in the Execution Plan:

-- Retrieve the data from the table, SQL Server uses an "Index Scan (NonClusteredHash)" operator in the Execution Plan
SELECT * FROM TestTable
GO

Before XTP this behavior was only possible by enabling Optimistic Concurrency within the database – Read Committed Snapshot Isolation or Snapshot Isolation that was introduced back in SQL Server 2005.
Let’s try to run an UPDATE statement:

UPDATE TestTable SET Col2 = 'Test' WHERE Col1 = 1
GO

When we look at the Update Execution Plan, there is nothing special here expect the Index Seek (NonClusteredHash) operator. So XTP is able to Scan and Seek Hash Indexes. Let’s try to make the UPDATE statement in an explicit transaction:

-- Let's try the UPDATE statement in an explicit transaction
BEGIN TRANSACTION

UPDATE TestTable SET Col2 = 'Test' WHERE Col1 = 1

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT
GO

In this case, SQL Server gives us the following error message:

Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

So when you are using explicit transactions, we have to hint SQL Server. But WITH (SNAPSHOT) isn’t the same as the Transaction Isolation Level Snapshot, because when I’m changing the Isolation Level to Snapshot, and trying to rerun the transaction, I’m getting the following error message:

BEGIN TRANSACTION

UPDATE TestTable WITH (SNAPSHOT) SET Col2 = 'Test' WHERE Col1 = 1

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT
GO

BEGIN TRANSACTION

UPDATE TestTable WITH (SNAPSHOT) SET Col2 = 'Test' WHERE Col1 = 1

CREATE PROCEDURE HekatonProcedure
(
   @Param INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
   ATOMIC WITH
   (
      TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'
   )

   INSERT INTO dbo.TestTable (Col1, Col2, Col3) VALUES (@param, 'Klaus', 'Aschenbrenner')

   SELECT Col1, Col2, Col3 FROM dbo.TestTable
END
GO

EXEC HekatonProcedure 5
GO