Around 8 months ago I wrote a blog posting where I discussed the various reasons why I don’t yet recommend In-Memory OLTP to my customers. A lot of people are approach me these days because they have some performance problems and they are thinking about using In-Memory OLTP to solve them.

As we all know, In-Memory OLTP is a very specialized technology in SQL Server and doesn’t yet have that many scenarios where it makes sense. In addition the first implementation in SQL Server 2014 has some huge limitations that I have also covered in my weblog posting.

Things have changed in the meantime – thank to God! For a few weeks now, the CTP 3 version of SQL Server 2016 has been available for public download. And Microsoft has made some huge improvements in the area of In-Memory OLTP. Let’s have a more detailed look at them.

Foreign-Key Constraints

One of the most important changes or should I say – improvements – is the support for Foreign-Key constraints! Yes, you heard correctly: In-Memory OLTP now supports Foreign-Key constraints. Normally you would have expected Foreign-Key constraints in the initial implementation, because it is about OLTP scenarios, but Microsoft didn’t support them on SQL Server 2014. Let’s have a look at the following code.

-- Create a parent table
CREATE TABLE Parent
(
	ParentID INT IDENTITY(1, 1) NOT NULL,
	Col1 CHAR(100) NOT NULL,
	Col2 CHAR(100) NOT NULL,
	Col3 CHAR(100) NOT NULL,
	CONSTRAINT chk_PrimaryKey_Parent PRIMARY KEY NONCLUSTERED HASH (ParentID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Create a child table
CREATE TABLE Child
(
	ChildID INT IDENTITY(1, 1) NOT NULL,
	ParentID INT NOT NULL,
	Col1 CHAR(100) NOT NULL,
	Col2 CHAR(100) NOT NULL,
	Col3 CHAR(100) NOT NULL,

	-- Create a FK constraint between both tables
	CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) 
    REFERENCES Parent (ParentID),
	
	CONSTRAINT chk_PrimaryKey_Child PRIMARY KEY NONCLUSTERED HASH (ChildID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Insert some records into both tables
INSERT INTO Parent VALUES ('a', 'a', 'a'), ('b', 'b', 'b'), ('c', 'c', 'c')
INSERT INTO Child VALUES (1, 'a', 'a', 'a'), (1, 'b', 'b', 'b'), (1, 'c', 'c', 'c')
GO

This code fragment creates a simple Foreign-Key constraint between 2 tables: a parent table, and a child table. In addition I have also populated both tables with some test data. And now let’s execute a simple query against both tables:

-- The unnecessary join is removed in the execution plan.
SELECT c.* FROM Parent p
JOIN Child c ON c.ParentID = p.ParentID
GO

When you look at the query itself, you can see that I only want to retrieve information from the child table. Based on the Foreign-Key constraint the Query Optimizer knows that a record in the parent table must be there. Therefore the Query Optimizer can simplify this query by removing the unneccesary join. And when you look at the execution plan, you can see that this simplification has actually happened – nice:

That’s one of the greatest improvements in In-Memory OLTP in SQL Server 2016 – the support for Foreign-Key constraints. The support for Foreign-Keys works even in Native Compiled Stored Procedures as you can see from the following listing.

-- Create a natively compiled Stored Procedure
CREATE PROCEDURE InMemoryOLTPProcedure
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
   ATOMIC WITH
   (
      TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'
   )

   SELECT c.ChildID, c.ParentID, c.Col1, c.Col2, c.Col3 FROM dbo.Parent p
   JOIN dbo.Child c ON c.ParentID = p.ParentID
END
GO

When you execute a Native Compiled Stored Procedure, you still have no actual execution plan available. The only thing that you can still request is the estimated execution plan…

Check Constraints

Another nice improvement is that we now support Check Constaints. Check Constraints are very important, because they give the Query Optimizer more information about the shape of your data. And based on this information the Query Optimizer can give you a better performing execution plan. The following listing shows a simple example of how you can define a Check Constraint with In-Memory OLTP.

-- You can't create a CHECK constraint on a Memory Optimized Table
CREATE TABLE CheckConstraint
(
	ID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Value INT NOT NULL DEFAULT 1 CONSTRAINT ck_Value CHECK (Value = 1)
)
WITH
(
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
)
GO

As you can see from this table definition, I have created here a simple Check Constraint, and told SQL Server that we only store the value 1 in the column Value. Even the contradiction detection works in combination with Check Constraints. The following query leads to a Constant Scan operator in the execution plan.

-- Contradiction detection works with In-Memory OLTP.
SELECT * FROM CheckConstraint
WHERE Value = 0
GO

Indexes on Character columns

Back in the old days with SQL Server 2014, indexes on character columns in In-Memory OLTP didn’t work out of the box, because you had to use the BIN2 collation. That was a show stopper for a lot of people, because using another collation also affects the results when you perform comparisons or sorts on character columns.

With SQL Server 2016 Microsoft has now finally removed this limitation, and you can now create a Hash- or Range Index directly on a character column without using the BIN2 collation. Let’s have a look at the following example, which now just works with SQL Server 2016.

-- Creates a table with an index on a character column.
-- This works now without any problems in SQL Server 2016.
CREATE TABLE TestTable1
(
	Col1 CHAR(10) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
	Col2 VARCHAR(100) NOT NULL,
	Col3 VARCHAR(100) NOT NULL
) WITH
(
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
)
GO

That’s really cool – well done!

Schema & Bucket Count Changes

Back in SQL Server 2014 this is where it got really bad and ugly: schema changes on Memory Optimized tables where not supported in any way. You had to drop and recreate your table, even if you just wanted to add a new index or change an existing index. Sorry, but this was one of most prominent limitations why I didn’t recommend In-Memory OLTP to my customers in any way. Even for changing the bucket count for a Hash Index you had to drop and recreate your table.

With SQL Server 2016 life is now fine and quite easy. First of all you can change the bucket count of an existing index with a simple ALTER INDEX REBUILD statement. Be aware that the memory that you need for the rebuild is twice the size of the table itself. The following listing shows you that improvement.

-- We can change now the bucket count without dropping the table
ALTER TABLE Parent
ALTER INDEX chk_PrimaryKey_Parent
REBUILD WITH (BUCKET_COUNT = 1048576)
GO

And in addition you can now alter your table. Unfortunately the creation of an index after the table creation doesn’t yet work on a Memory Optimized table. I haven’t yet have tried every possible change, but the following listing should give you an idea of what you can expect from SQL Server 2016.

-- Create a parent table
CREATE TABLE LetsTrySchemaChanges
(
	ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
	Col1 CHAR(100) NOT NULL,
	Col2 CHAR(100) NOT NULL,
	Col3 CHAR(100) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Schema Changes are now also supported on Memory-Optimized tables.
ALTER TABLE LetsTrySchemaChanges ADD Test CHAR(100) NULL
GO

-- The creation of an index after the table creation is not supported
CREATE NONCLUSTERED HASH INDEX idx_Test ON LetsTrySchemaChanges(Col3)
WITH (BUCKET_COUNT = 1024)
GO

Summary

As you have seen throughout this blog posting, In-Memory OLTP got a quite nice face-lift in SQL Server 2016. Now if someone asks me if they should use In-Memory OLTP, I would say yes – as long as they have the right problems, and these problems can’t be solved with the traditional relational engine of SQL Server.

Besides the improvements that I have mentioned in this blog posting, In-Memory OLTP in SQL Server 2016 gives you other new improvements and I will cover those in one of my upcoming blog postings.

What do you think about all these new improvements? Please feel free to leave a comment.

Thanks for your time,

-Klaus