In-Memory OLTP: Inserting 3 millions records in 3 seconds

One of the sessions we delivered with SQL Server Sri Lanka User Group Meeting – October 2013 was related to SQL Server 2014: In-Memory OLTP. I am sure that everyone was thrilled seeing, inserting millions of records into SQL Server table within seconds. Initially it amazed me too :), I believe that this is the key for 2014, just like Columnstore in 2012.

Here is the demo code I used with the session.

During the demonstration, I showed two scenarios based on three tables;

1. Normal disk-based table
2. In-Memory Durable table
3. In-Memory non-durable table.

Once scenario is inserting records from another table. Other is inserting another set with a stored procedure. Before going through the code, let’s understand In-Memory OLTP.

In-Memory OLTP is a database engine component which is optimized for accessing memory-resident tables. This was started 4 years ago with codename “Hekaton”. It is mainly for OLTP scenario and it significantly improves the performance of OLTP workloads. Here are some of key points on it;

• Currently available with SQL Server 2014 - 64bit CTP1, CTP2.
• Tables can be created in memory as fully Durable or Non-Durable.
• Content of durable tables will not be lost in case of server crash.
• Non-durable tables are fully in memory. Content will be lost at server crash (including service restart).
• Indexes created for in-memory tables are in memory only.
• T-SQL can be written for accessing both in-memory tables and disk tables.
• In-memory tables are fully supported for ACID.
• Stored procedures written for accessing only in-memory tables can be made as natively compiled procedures.

Let’s see the code. I have already created a DB called TestDatabase01 and it has a table called dbo.FactSales. This table is from ContosoRetailDW which is available at: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=18279.

First step is enable the database for in-memory tables. It is done by adding a file group specifically for in-memory tables and add a file to it.

ALTER DATABASE [TestDatabase01] ADD FILEGROUP FG_MemoryOptiimizedData 

CONTAINS MEMORY_OPTIMIZED_DATA

GO

ALTER DATABASE TestDatabase01 ADD FILE 

    (NAME = 'TestDatabase01_MemoryOptiimizedData'

    , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDatabase01_MemoryOptiimizedData.ndf') 

TO FILEGROUP FG_MemoryOptiimizedData

GO

Next step is creating tables. This code creates three tables as mentioned above.

-- creating a normal disk table

CREATE TABLE dbo.NewFactSales

    (

        SalesKey int NOT NULL PRIMARY KEY 

        , DateKey datetime NOT NULL

        , channelKey int NOT NULL

        , StoreKey int NOT NULL

        , ProductKey int NOT NULL

        , PromotionKey int NOT NULL

        , CurrencyKey int NOT NULL

        , UnitCost money NOT NULL

        , UnitPrice money NOT NULL

        , SalesQuantity int NOT NULL

        , ReturnQuantity int NOT NULL

        , ReturnAmount money NULL

        , DiscountQuantity int NULL

        , DiscountAmount money NULL

        , TotalCost money NOT NULL

        , SalesAmount money NOT NULL

        , ETLLoadID int NULL

        , LoadDate datetime NULL

        , UpdateDate datetime NULL

    )

GO

-- creating an in-memory table.

-- Option DURABILITY = SCHEMA_AND_DATA makes table durable

CREATE TABLE dbo.FactSales_MemoryOptimized_Durable

        SalesKey int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 3000000)

        , DateKey datetime NOT NULL

        , channelKey int NOT NULL

        , StoreKey int NOT NULL

        , ProductKey int NOT NULL

        , PromotionKey int NOT NULL

        , CurrencyKey int NOT NULL

        , UnitCost money NOT NULL

        , UnitPrice money NOT NULL

        , SalesQuantity int NOT NULL

        , ReturnQuantity int NOT NULL

        , ReturnAmount money NULL

        , DiscountQuantity int NULL

        , DiscountAmount money NULL

        , TotalCost money NOT NULL

        , SalesAmount money NOT NULL

        , ETLLoadID int NULL

        , LoadDate datetime NULL

        , UpdateDate datetime NULL

    )

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

GO

-- creating another in-memory table.

-- Option DURABILITY = SCHEMA_ONLY, this is non-durable

CREATE TABLE dbo.FactSales_MemoryOptimized_NonDurable

    (

        SalesKey int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 3000000)

        , DateKey datetime NOT NULL

        , channelKey int NOT NULL

        , StoreKey int NOT NULL

        , ProductKey int NOT NULL

        , PromotionKey int NOT NULL

        , CurrencyKey int NOT NULL

        , UnitCost money NOT NULL

        , UnitPrice money NOT NULL

        , SalesQuantity int NOT NULL

        , ReturnQuantity int NOT NULL

        , ReturnAmount money NULL

        , DiscountQuantity int NULL

        , DiscountAmount money NULL

        , TotalCost money NOT NULL

        , SalesAmount money NOT NULL

        , ETLLoadID int NULL

        , LoadDate datetime NULL

        , UpdateDate datetime NULL

    )

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

GO

Let’s insert records. Insert three statements separately for seeing the difference.

-- Insert into disk table

INSERT INTO dbo.NewFactSales

(

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

)

SELECT 

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

FROM dbo.FactSales

-- Insert into in-memory durable table

INSERT INTO dbo.FactSales_MemoryOptimized_Durable

(

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

)

SELECT 

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

FROM dbo.FactSales

-- Insert into in-memory non-durable table

INSERT INTO dbo.FactSales_MemoryOptimized_NonDurable

(

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

)

SELECT 

   SalesKey, DateKey, channelKey, StoreKey

   , ProductKey, PromotionKey, CurrencyKey

   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity

   , ReturnAmount, DiscountQuantity, DiscountAmount

   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate

FROM dbo.FactSales

My VM is configured with 5.5GB memory. Three INSERT statements took 00:01:46, 00:00:17, and 00:00:09 respectively. As you see clearly, in-memory optimized tables give better performance than disk based tables. Let’s run the same with stored procedures. Below code adds three stored procedures. Note the second and third. Few options have been added to them, making them as natively compiled procedures. In addition to that, isolation level and language options are added too. Currently they are required for these procedures.

Note that current version requires manual statistics updates for in-memory tables.

UPDATE STATISTICS dbo.FactSales_MemoryOptimized_Durable WITH FULLSCAN, NORECOMPUTE

UPDATE STATISTICS dbo.FactSales_MemoryOptimized_NonDurable WITH FULLSCAN, NORECOMPUTE

Now let’s add procedures and run, and see the time each takes.

-- Procedure inserts records to disk table

CREATE PROCEDURE dbo.Insert_FactSales

AS

BEGIN

    DECLARE @a int = 4000000

    WHILE (@a < 7000000)

    BEGIN

        INSERT INTO dbo.NewFactSales

            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost

            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount

            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)

        VALUES

            (@a,'2013-01-01',1,1,1,1,1,1

            ,1,1,1,1,1,1

            ,1,1,1,'2013-01-01','2013-01-01')

        SET @a = @a + 1

    END

END

GO

-- Procedure inserts records to in-memory durable table

CREATE PROCEDURE dbo.Insert_FactSales_MemoryOptimized_Durable

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC

    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_English')

    DECLARE @a int = 4000000

    WHILE (@a < 7000000)

    BEGIN

        INSERT INTO dbo.FactSales_MemoryOptimized_Durable

            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost

            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount

            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)

        VALUES

            (@a,'2013-01-01',1,1,1,1,1,1

            ,1,1,1,1,1,1

            ,1,1,1,'2013-01-01','2013-01-01')

        SET @a = @a + 1

    END

END

GO

-- Procedure inserts records to in-memory non-durable table

CREATE PROCEDURE dbo.Insert_FactSales_MemoryOptimized_NonDurable

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC

    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_English')

    DECLARE @a int = 4000000

    WHILE (@a < 7000000)

    BEGIN

        INSERT INTO dbo.FactSales_MemoryOptimized_NonDurable

            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost

            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount

            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)

        VALUES

            (@a,'2013-01-01',1,1,1,1,1,1

            ,1,1,1,1,1,1

            ,1,1,1,'2013-01-01','2013-01-01')

        SET @a = @a + 1

    END

END

GO

EXEC dbo.Insert_FactSales

EXEC dbo.Insert_FactSales_MemoryOptimized_Durable

EXEC dbo.Insert_FactSales_MemoryOptimized_NonDurable

Again, natively compiled procedures against give better performance than others. Here is the summary;

Insert 3 million records	Disk table	In-Memory table – Durable	In-Memory table - NonDurable
INSERT statement	00:01:46	00:00:17	00:00:09
SP	00:15:19	00:00:19 (Natively Compiled)	00:00:03 (Natively Compiled)

Expect more codes on this with future posts. Code related to this can be downloaded from: http://sdrv.ms/18sAxEa

The presentation used for the session can be downloaded from: http://sdrv.ms/1d2xSoe

SQL 2014 is still being developed. However, for testing purposes, you can download CTP 2 from here: http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx

Here are useful links on this;

• In-Memory OLTP (In-Memory Optimization) - TechNet
• Hekaton: SQL Server’s Memory-Optimized OLTP Engine - SQL Dev Team - Whitepaper
• SQL Server In-Memory OLTP Internals Overview for CTP1 - Whitepaper - Kalen Delany
• http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B307#fbid=rVEQ5ykJ4kA
• http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B308#fbid=rVEQ5ykJ4kA
• http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B204#fbid=rVEQ5ykJ4kA