Press "Enter" to skip to content

SQL Server 2014 Features Part 1: Memory Optimized Tables

Deirdre O'Leary Deirdre O'Leary
August 8, 2014
Leave a Comment

Aim: Test speed of memory optimized tables & natively-compiled stored procedures against disk-based tables & stored procedures.

There are a couple of examples available from Microsoft to demonstrate the performance benefits of Memory Optimized Tables:

I roughly followed the first code example but I found the naming of the objects quite abstract so I adapted it to something more in line with actual data to give it some context.

Create the Memory Optimized Database:
The database is created in the same way as a normal database. The key difference is the addition of filegroups configured to contain memory optimized data.

Create a directory C:\Data if one does not already exist. Create a 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 Disk-based & Memory Optimized Objects:
In InMemoryOLTPExample, create a standard disk-based table & stored procedure to populate it with data.


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

CREATE TABLE dbo.Dates (
   Id INT NOT NULL PRIMARY KEY,
   DateRecord DATE NOT NULL
);
GO

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

CREATE PROCEDURE dbo.PopulateDateList
   @rowcount INT,
   @startdate DATE
WITH EXECUTE AS OWNER
AS
BEGIN
   BEGIN TRANSACTION
      DECLARE @i INT = 0;
      DECLARE @d DATE;
      WHILE @i < @rowcount
      BEGIN
         SET @d = DATEADD(DAY, @i, @startdate);
         INSERT INTO dbo.Dates (Id, DateRecord)
         VALUES (@i, @d);
         SET @i += 1;
      END
   COMMIT TRANSACTION
   -- Clean up
   DELETE FROM dbo.Dates;
END;

Even though InMemoryOLTPExample has a memory optimized filegroup, objects created without specifying the memory optimized setting (for tables) or native compilation setting (for stored procedures) will be created on disk.
Create a memory optimized table & a standard disk-based stored procedure to populate it.


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

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

CREATE PROCEDURE dbo.PopulateDateList1
   @rowcount INT,
   @startdate DATE
WITH EXECUTE AS OWNER
AS
BEGIN
   BEGIN TRANSACTION
   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
   COMMIT TRANSACTION
   -- Clean up
   DELETE FROM dbo.MemoryOptimizedDates1;
END;

The memory optimized table creation differs from disk-based table creation in a number of ways.
The table is specified as memory optimized by setting MEMORY_OPTIMIZED=ON. The table is specified as durable by setting DURABILITY = SCHEMA_AND_DATA (which is the default option). This setting indicates that the table is durable as both the schema & data are persisted on restart of the database. The other option is DURABILITY = SCHEMA_ONLY which indicates that the data is not persisted on restart.
The primary key has a new type of index created on it, a HASH index. This index is specific to memory optimized tables & is very efficient for point lookup operations. It is also possible to create NONCLUSTERED indexes on memory optimized tables which are efficient for range scans & ordered scans. Indexes on memory optimized tables exist only in memory, they are not persisted to disk.
When a HASH index is created, the BUCKET_COUNT must be specified. It specifies the size of the hash table allocated to the index. It should be typically set between 1 & 2 times the number of unique values in the index key. Specifying a BUCKET_COUNT that is too low can significantly affect performance.

Create a memory optimized 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_AND_DATA);
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;

The natively compiled stored procedure creation differs from disk-based stored procedure creation in a number of ways.
The stored procedure is specified as natively compiled by creating it WITH NATIVE_COMPILATION.
Natively compiled stored procedures must be bound to the schema of the objects they reference. Referenced tables must include their schema name. If you try to drop the referenced table, you may see the following error so you must drop the stored procedure before the table in this case.

Cannot DROP TABLE 'MemoryOptimizedDates2' because it is being referenced by object 'PopulateDateList2'.

Natively compiled stored procedures do not support EXECUTE AS CALLER (the default). Therefore, specifying the execution context is required (e.g. EXECUTE AS OWNER).
The body of the natively compiled stored procedure must consist of one atomic block, specified by BEGIN ATOMIC. The atomic blocks in natively compiled stored procedures require TRANSACTION ISOLATION LEVEL & LANGUAGE settings.
Unlike disk-based stored procedures, which are compiled when they are first executed, natively compiled stored procedures are compiled when they are created. One of the results of this is that error conditions can prevent creation of a natively compiled stored procedure whereas they would not be highlighted at creation of a disk-based stored procedure & would instead cause the stored procedure to fail on execution.

Test the different scenarios:
Run the following script to test the speed of the different scenarios we have created.

  1. Disk-based table & disk-based stored procedure
  2. Memory optimized table & disk-based stored procedure
  3. Memory optimized table & natively-compiled stored procedure

Ignore the results from the first execution as they will be negatively impacted by the 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. Disk-based table & disk-based stored procedure
EXEC PopulateDateList @rowcount, @startdate;
SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Disk-based table & disk-based stored procedures: ' + CAST(@timems AS VARCHAR(10)) + ' ms';

-- 2. Memory optimized table & disk based stored procedure
SET @starttime = sysdatetime();
EXEC PopulateDateList1 @rowcount, @startdate;
SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Memory optimized table & disk-based stored procedure: ' + CAST(@timems as VARCHAR(10)) + ' ms';

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

The results are:

  1. Disk-based table & disk-based stored procedures: 4125 ms
  2. Memory optimized table & disk-based stored procedure: 3015 ms
  3. Memory optimized table & natively-compiled stored procedure: 969 ms

Test using SQLQueryStress:
However, I’m not content with a single iteration of the test. I’d like to see the average results over a number of iterations. So I am going to use a tool called SQLQueryStress to run the scenarios multiple times.
Download & install SQLQueryStress from http://www.datamanipulation.net/sqlquerystress/. Launch SQLQueryStress.
1 launch_sqlquerystress
In Database, add the Server, Authentication & Default Database details.
2 sqlquerystress_database
In the Query box, add the following query to test the first scenario (disk-based table & disk-based stored procedure).


EXEC PopulateDateList @rowcount, @startdate;

Increase the Number of Iterations to 1000.
In InMemoryOLTPExample, create a table to hold the parameters that SQLQueryStress will use for the test. Using random parameters is important because if the same value were used for the parameter every time, every execution after the first would be faster due to data caching.


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

In Parameter Substitution in SQLQueryStress, add the following query to the Parameter Query box.


SELECT [rowcount], [date] FROM test_params;

Click Get Columns & map the @rowcount parameter to the rowcount column & the @startdate parameter to the date column.
3 sqlquerystress_params
Click GO & monitor the progress of the query which will execute 1000 times.
4 sqlquerystress_results1
Replace the query in the Query box with the following & repeat the test for the second scenario (memory optimized table & disk-based stored procedure).


EXEC PopulateDateList1 @rowcount, @startdate;

5 sqlquerystress_results2
Replace the query in the Query box with the following & repeat the test for the third scenario (memory optimized table & natively-compiled stored procedure).


EXEC PopulateDateList2 @rowcount, @startdate;

6 sqlquerystress_results_3
The results (Actual Seconds/Iteration (Avg)) from the tests are:

  1. Disk-based table & disk-based stored procedures: 0.5617s
  2. Memory optimized table & disk-based stored procedure: 0.2224s
  3. Memory optimized table & natively-compiled stored procedure: 0.0214s

So we can see that the memory optimized table is significantly faster than the disk based table & the combination of memory optimized table & natively compiled stored procedure is faster still (ten times faster!).

Some limitations of natively compiled stored procedures that I have identified while setting up these tests:

  • IDENTITY(1,1) is supported with memory optimized tables however the function ‘setidentity’ is not supported with natively compiled stored procedures.
  • Subqueries (queries nested inside another query) are not supported with natively compiled stored procedures.

Categories:SQL Server

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *