(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)

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:

Msg 41332, Level 16, State 0, Line 3

Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

So let’s let’s add a query hint to the query itself:

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

Now the transaction commits, and sys.dm_tran_locks again only shows the Sch-S lock. The next thing that I wanted to try is to run 2 UPDATE statements in parallel by not committing the 1st transaction. So let’s just run the following query in 2 different sessions, and make sure you are not committing both transaction. Without XTP, the 2nd transaction would block, because of the X lock that is held by the 1st session:

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

Unfortunately SQL Server gives us in the 2nd transaction the following error message:

Msg 41302, Level 16, State 110, Line 3

The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

The statement has been terminated.

We were just running into an Update Conflict, and SQL Server has rolled back the 2nd transaction. I have not expected that, but I have to think a little bit more about it.

The next thing that XTP provides you in the CTP1 version are so-called Natively Compiled Stored Procedures:

Again, there is no wizard, just a simple T-SQL script template that you can use. Native Compilation means that SQL Server is compiling the whole stored procedure down to C/C++ code in the background – the performance will be awesome, because we are now executing directly native code within SQL Server. The following script shows how a simple XTP stored procedure looks like.

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

There are a lot of prerequisites that must be met:

• The Stored Procedure must be created with SCHEMABINDING and EXECUTE AS
• The Stored Procedure must be marked with NATIVE_COMPILATION
• You have to specify the ATOMIC block, where you set the Transaction Isolation Level, and the language

When you have created the stored procedure, you can simply execute it:

EXEC HekatonProcedure 5
GO

Because you are now dealing with native code, there is no Execution Plan anymore! Have fun with performance tuning… An ALTER PROCEDURE is also not supported (doesn’t make sense, because of the native generated code), means you have to DROP and CREATE the stored procedure again, if you want to make changes.

This was a short walkthrough of XTP that I have encountered in the 1st hour after installing the CTP1 version of SQL Server 2014. More information and additional content will definitely come. You can download the whole T-SQL code that I have shown in this blog posting here.

Call to action: Download the CTP1 of SQL Server 2014, play around with XTP and all the other nice cool features. Please feel free to leave a comment about your experience and your opinions about Extreme Transactional Processing.

Thanks for reading

-Klaus