SQL Server 2014 Features Part 2: Durable vs Non-Durable Memory Optimized Tables

Aim: Test speed of durable memory optimized tables against non-durable.

In the previous post, I tested the speed of memory optimized tables & natively-compiled stored procedures against disk-based tables & stored procedures. In this post, I will perform a similar test of durable & non-durable memory optimized tables. The previous post outlines the difference between durable & non-durable tables.

---

Create the Memory Optimized Database:

CREATE DATABASE InMemoryOLTPExample
ON PRIMARY (
   name = [InMemoryOLTPExample_data],
   filename = 'C:\Data\InMemoryOLTPExample_data1.mdf',
   size=500MB
)
LOG ON (
   name = [InMemoryOLTPExample_log],
   filename='C:\Data\InMemoryOLTPExample_log.ldf',
   size=500MB
);

ALTER DATABASE InMemoryOLTPExample
   ADD FILEGROUP [fg_InMemoryOLTPExample]
   CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE InMemoryOLTPExample
ADD FILE (
   name = [file_InMemoryOLTPExample],
   filename= 'C:\Data\file_InMemoryOLTPExample'
) TO FILEGROUP fg_InMemoryOLTPExample;

---

Create Durable & Non-Durable Memory Optimized Objects:

In InMemoryOLTPExample, create a durable table & a natively-compiled stored procedure to populate it.

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'PopulateDateList1')
   DROP PROCEDURE dbo.PopulateDateList1;
GO

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'MemoryOptimizedDates1')
   DROP TABLE dbo.MemoryOptimizedDates1;
GO

CREATE TABLE dbo.MemoryOptimizedDates1 (
   Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
   DateRecord DATE NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

CREATE PROCEDURE dbo.PopulateDateList1
   @rowcount INT,
   @startdate DATE
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
   WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   DECLARE @i INT = 0;
   DECLARE @d DATE;
   WHILE @i < @rowcount
   BEGIN
      SET @d = DATEADD(DAY, @i, @startdate);
      INSERT INTO dbo.MemoryOptimizedDates1 (Id, DateRecord)
      VALUES (@i, @d);
      SET @i += 1;
   END
   -- Clean up
   DELETE FROM dbo.MemoryOptimizedDates1;
END;

Create a non-durable table & a natively-compiled stored procedure to populate it.

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'PopulateDateList2')
   DROP PROCEDURE dbo.PopulateDateList2;
GO

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'MemoryOptimizedDates2')
   DROP TABLE dbo.MemoryOptimizedDates2;
GO

CREATE TABLE dbo.MemoryOptimizedDates2 (
   Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
   DateRecord DATE NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE PROCEDURE dbo.PopulateDateList2
   @rowcount INT,
   @startdate DATE
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
   WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   DECLARE @i INT = 0;
   DECLARE @d DATE;
   WHILE @i < @rowcount
   BEGIN
      SET @d = DATEADD(DAY, @i, @startdate);
      INSERT INTO dbo.MemoryOptimizedDates2 (Id, DateRecord)
      VALUES (@i, @d);
      SET @i += 1;
   END
   -- Clean up
   DELETE FROM dbo.MemoryOptimizedDates2;
END;

---

Test the different scenarios:
Run each stored procedure to test the time taken. Ignore the results from the first execution as they will be adversely affected by initial memory allocation.

USE InMemoryOLTPExample;
GO

SET STATISTICS TIME OFF;
SET NOCOUNT ON;

-- Parameters for inserting records
DECLARE @rowcount INT = 100000;
DECLARE @startdate DATE = '19010101';

-- Parameters for time-taken
DECLARE @starttime datetime2 = sysdatetime();
DECLARE @timems INT;

-- 1. Durable memory optimized table & natively-compiled stored procedure
EXEC PopulateDateList1 @rowcount, @startdate;
SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Durable memory optimized table & natively-compiled stored procedure: ' + CAST(@timems AS VARCHAR(10)) + ' ms';

-- 2. Non-durable memory optimized table & natively-compiled stored procedure
SET @starttime = sysdatetime();
EXEC PopulateDateList2 @rowcount, @startdate;
SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Non-durable memory optimized table & natively-compiled stored procedure: ' + CAST(@timems as VARCHAR(10)) + ' ms';

The results are:

1. Durable memory optimized table & natively-compiled stored procedure: 1109 ms
2. Non-durable memory optimized table & natively-compiled stored procedure: 235 ms

---

Test using SQLQueryStress:
In InMemoryOLTPExample, create a table to hold the parameters that SQLQueryStress will use for the test.

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'test_params')
   DROP TABLE dbo.test_params;
GO

CREATE TABLE dbo.test_params (
   [rowcount] INT,
   [date] DATE
);
GO

DECLARE @i INT = 0;
DECLARE @n INT = 1000;
DECLARE @r INT;
DECLARE @d DATE;

WHILE @i < @n
BEGIN
   -- Create list of 1000 random dates
   SET @r = ROUND(((@n - @i) * RAND() + @i), 0);
   SET @d = DATEADD(DAY, @r, '19010101');
   INSERT INTO dbo.test_params ([rowcount], [date])
   SELECT @i AS [rowcount], @d AS [date];
   SET @i += 1;
END

Repeat the tests in SQLQueryStress – the previous post details how to test using SQLQueryStress. The results (Actual Seconds/Iteration (Avg)) from the tests are:

1. Durable memory optimized table & natively-compiled stored procedure: 0.0188s
   
2. Non-durable memory optimized table & natively-compiled stored procedure: 0.0080s
   

So we can see that the non-durable memory optimized table is significantly faster than the durable table.