SQL Server 2014 In-Memory OLTP (Hekaton Project)

Today let us talk about SQL Server 2014 new feature in-memory OLTP or a.k.a Haketon Project. It is one of a well-spoken feature of SQL Server 2014 CTP1. MS claims it gives 50 time more performance gains with in-memory built-in and with average performance gains of 10 to 50 times for new and existing SQL Server apps.
So let us try to work with this feature and see the performance gain in action:
Now let us change ContosoRetailDW and make it compatible with SQL Server 2014 and add a memory optimized file group.

-- =============================================
-- Alter Database to support MEMORY_OPTIMIZED_DATA 
-- =============================================
USE [master]
GO
 
ALTER DATABASE [ContosoRetailDW]
	SET COMPATIBILITY_LEVEL = 110;
GO
 
ALTER DATABASE [ContosoRetailDW] 
	ADD FILEGROUP [MemoryOptimised] CONTAINS MEMORY_OPTIMIZED_DATA ;
GO
 
ALTER DATABASE [ContosoRetailDW] 
	ADD FILE ( NAME = N'ContosoRetailDWMemoryOptimised', 
		FILENAME = N'D:\DATA\ContosoRetailDWMemoryOptimised' ) 
		TO FILEGROUP [MemoryOptimised]
GO

For comparison let us create two tables one with traditional file group and a disk table and another memory optimized table

--======================================================
-- Create Disk Table
--======================================================
USE ContosoRetailDW;
GO
IF OBJECT_ID('dbo.FactSales_DiskTable','U') IS NOT NULL
    DROP TABLE dbo.[FactSales_DiskTable]
GO
CREATE TABLE [dbo].[FactSales_DiskTable](
	[SalesKey] [int] IDENTITY(1,1) NOT NULL,	
	[UnitCost] [money] NOT NULL,	
	[SalesQuantity] [int] NOT NULL,	
	[TotalCost] [money] NOT NULL
) ;
 
ALTER TABLE dbo.[FactSales_DiskTable] 
  ADD CONSTRAINT [PK_FactSales_DiskTable_SalesKey] 
		PRIMARY KEY NONCLUSTERED ( [SalesKey] ); 
GO

Now let us create the In-memory table with Heketon buckets

--======================================================
-- Create Memory Optimized Table 
--======================================================
USE ContosoRetailDW;
GO	
--Drop table if it already exists.
IF OBJECT_ID('dbo.FactSales_MemoryTable','U') IS NOT NULL
    DROP TABLE dbo.[FactSales_MemoryTable]
GO
 
CREATE TABLE [dbo].[FactSales_MemoryTable](
	[SalesKey] [int] NOT NULL , 		
	[UnitCost] [money] NOT NULL,	
	[SalesQuantity] [int] NOT NULL,	
	[TotalCost] [money] NOT NULL INDEX index_FactSales_MemoryTable_TotalCost HASH (TotalCost) 
                    WITH (BUCKET_COUNT = 1024), 
	--This time will be using Hekaton Hash Buckets
   CONSTRAINT PK_FactSales_MemoryTable PRIMARY KEY NONCLUSTERED HASH (SalesKey) 
              WITH (BUCKET_COUNT = 1024),
   INDEX index_FactSales_MemoryTable_UnitCost HASH (UnitCost) 
              WITH (BUCKET_COUNT = 1024),
   INDEX index_FactSales_MemoryTable_SalesQuantity HASH (SalesQuantity) 
              WITH (BUCKET_COUNT = 1024)
) 
WITH(MEMORY_OPTIMIZED=ON); /*,DURABILITY = SCHEMA_AND_DATA);*/
GO

Now let us add a million record to each one of them and compare how much time it will take to complete. Let us begin with the disk table

--======================================================
--Add data to SampleDiskTable--
--======================================================
USE ContosoRetailDW;
GO
INSERT INTO [FactSales_DiskTable] 
	SELECT top(1000000)		
		[UnitCost],	
		[SalesQuantity],	
		[TotalCost]
    FROM [ContosoRetailDW].[dbo].[FactSales];
GO

Now let us try for the in-memory table

--======================================================
--Add data to SampleMemoryTable
--======================================================
USE ContosoRetailDW;
GO
 
SET NOCOUNT ON;
GO
 
SET ROWCOUNT 0;
GO
--Since Identity Proporty is not allowed for memory
--optimized table I will use sequence object
CREATE SEQUENCE FactSales_Sequence
    START WITH 1
    INCREMENT BY 1 ;
GO
 
;WITH CTE AS (
SELECT top(1000000) 	    		
		[UnitCost],	
		[SalesQuantity],	
		[TotalCost]
    FROM [ContosoRetailDW].[dbo].[FactSales]
)
INSERT INTO [FactSales_MemoryTable]
	SELECT 
	    NEXT VALUE FOR FactSales_Sequence , 		
		[UnitCost],	
		[SalesQuantity],	
		[TotalCost]
    FROM CTE;
GO

Notice I have used a sequence object to generate a key for the table as in-memory table doesn’t support identity column property.
Okay now that we have our tables ready let us go head and compare the two tables in different data operations.

>-------------------------------------------------------------------
--Now let us play around with the data and see the difference
-------------------------------------------------------------------
--======================================================
--Check number of rows--
--======================================================
USE ContosoRetailDW;
GO
select COUNT(*) [DiskRows] from [FactSales_DiskTable];
go
 
select COUNT (*)[MemoryRows] from [FactSales_MemoryTable];
go

Okay we have a million record in each table

--======================================================
						-- SELECT -- 
--======================================================
-- Disk table
USE ContosoRetailDW;
GO
SELECT [SalesQuantity]      
      ,SUM([TotalCost]) AS [TotalCost]
  FROM [ContosoRetailDW].[dbo].[FactSales_DiskTable]
  GROUP BY [SalesQuantity] ;
GO --took 3 secods

This takes 3 seconds and here is its executions plan

--Memory table
SELECT [SalesQuantity]      
      ,SUM([TotalCost]) AS [TotalCost]   
FROM [ContosoRetailDW].[dbo].[FactSales_MemoryTable]
GROUP BY [SalesQuantity] ;
GO  --took 0 secods

This takes 0 second and here is the execution plan for it

--Memory 2 Stored procedure
CREATE PROC SP_SampleMemorySELECT AS
	SELECT [SalesQuantity]      
		  ,SUM([TotalCost]) AS [TotalCost]   
	FROM [ContosoRetailDW].[dbo].[FactSales_MemoryTable]
	GROUP BY [SalesQuantity] ;
GO
Exec SP_SampleMemorySELECT;
GO --took 0 secods

We can even build a stored procedure to save more time.

--======================================================
						-- DELETE --
--======================================================
-- Disk table
USE ContosoRetailDW;
GO
DELETE FROM [dbo].[FactSales_DiskTable]; 
GO --took 14 secods

--Memory table
DELETE FROM [dbo].[FactSales_MemoryTable]; 
GO  --took 1 secod

As you we have seen above MS did a good job on SQL Server 2014 and Download SQL Server 2014 CTP1 and give it a try yourself.

Comments