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
    1 results_1
  2. Non-durable memory optimized table & natively-compiled stored procedure: 0.0080s
    2 results_2

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: