Export (0) Print
Expand All

Demonstration: Performance Improvement of In-Memory OLTP

SQL Server 2014

The code sample in this topic demonstrates memory-optimized tables. This performance improvement is evident when data in a memory-optimized table is accessed from traditional, interpreted Transact-SQL. This performance improvement is even greater when data in a memory-optimized table is accessed from a natively compiled stored procedure.

Another sample demonstrating memory-optimized tables is available at SQL Server 2014 In-Memory OLTP Sample.

This sample is single-threaded and does not take advantage of the concurrency benefits of In-Memory OLTP. A workload that uses concurrency will see a greater performance gain. This sample shows only one aspect of performance improvement, data access efficiency for insert.

The performance improvement offered by memory-optimized tables is fully realized when data in a memory-optimized table is accessed from a natively compiled stored procedure.

First create a database that has a memory-optimized file group. This sample assumes a directory called c:\data.

CREATE DATABASE imoltp
ON PRIMARY (name = [imoltp_data], filename = 'c:\data\imoltp_mod1.mdf', size=500MB)
LOG ON (name = [imoltp_log], filename='C:\data\imoltp_log.ldf', size=500MB)
GO
 
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name = [imoltp_dir], filename= 'c:\data\imoltp_dir') TO FILEGROUP imoltp_mod;
GO

Next, let's create the tables and the natively compiled stored procedure.

USE imoltp;
GO

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

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

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

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


CREATE TABLE [dbo].[sql] (
  c1 INT NOT NULL PRIMARY KEY,
  c2 NCHAR(48) NOT NULL
)
GO

CREATE TABLE [dbo].[hash] (
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  c2 NCHAR(48) NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

CREATE TABLE [dbo].[hash1] (
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  c2 NCHAR(48) NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

CREATE PROCEDURE xx 
  @rowcount INT,
  @c NCHAR(48)
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
  AS 
  BEGIN ATOMIC 
  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
  DECLARE @i INT = 1;

  WHILE @i <= @rowcount
  BEGIN;
    INSERT INTO [dbo].[hash1] VALUES (@i, @c);
    SET @i += 1;
  END;
END;
GO

Now we can execute the queries that will demonstrate the performance of memory-optimized tables. Before you execute the following code sample, you must execute the previous code sample, which drops the existing tables and natively compiled stored procedure, and recreates them.

Discard the performance of the first execution, which is negatively affected by initial memory allocation.

SET STATISTICS TIME OFF;
SET NOCOUNT ON;

-- inserts - 1 at a time

DECLARE @starttime datetime2 = sysdatetime();
DECLARE @timems INT;
DECLARE @i INT = 1
DECLARE @rowcount INT = 100000
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678'

--- disk-based table and interpreted Transact-SQL

BEGIN TRAN;
  WHILE @I <= @rowcount
  BEGIN
    INSERT INTO [dbo].[sql] VALUES (@i, @c)
    SET @i += 1
  END;
COMMIT;

SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'Disk-based table and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms';

--- Interop Hash
SET @i = 1;
SET @starttime = sysdatetime();

BEGIN TRAN
  WHILE @i <= @rowcount
    BEGIN
      INSERT INTO [dbo].[hash] VALUES (@i, @c);
      SET @i += 1;
    END;
COMMIT;


SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + CAST(@timems as VARCHAR(10)) + ' ms';

--- Compiled Hash
SET @starttime = sysdatetime();

EXEC xx @rowcount, @c;

SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT 'memory-optimized table w/hash index and native SP:' + CAST(@timems as varchar(10)) + ' ms';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft