Wednesday, February 1, 2017

Getting Started with SQL Server In-Memory OLTP – Part 2

In the previous article of this blog series, I introduced In-Memory OLTP and explained how it is possible to create an In-Memory OLTP - enabled database as well as memory-optimized tables (durable and non-durable).

Today we are going to talk about naively-compiled stored procedures as well as see a full example of performance improvement when using In-Memory OLTP. 

Natively-compiled stored procedures are SQL Server objects that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc.

The main features of natively-compiled stored procedures are:
  • They are compiled to native code (DLL) upon their reation (the interpreted stored procedures are compiled at first execution)
  • Their call is actually the invokation of a DLL
  • Aggressive optimizations take time at compile time
  • They can only interact with memory-optimized tables and other memory-optimized data structures (i.e. memory-optimized table variables, etc.)
To define a natively-compiled stored procedure, you first need to create the database objects that will be referenced in the procedure.

Now let's see a comprehensive example where I'm going to create a disk-based table, two durable memory-optimized tables and a natively-compiled stored procedure. Then, I'm going to run 100.000 insert statements against each one of those tables and measure the execution times.

/*
* DDL T-SQL Code for Tables and Natively-Compiled SP
*/
--Create Disk-Based Table
CREATE TABLE [dbo].[Person_DiskBased]
(
ID INT NOT NULL PRIMARY KEY ,
FirstName VARCHAR(50) NOT NULL ,
LastName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  
);
GO

--Create Durable Memory-Optimized Table
CREATE TABLE [dbo].[Person_MemOpt_Durable]
(
ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1000000 ) ,
FirstName VARCHAR(50) NOT NULL ,
LastName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

 --Create Durable Memory-Optimized Table for natively-compiled SP
CREATE TABLE [dbo].[Person_MemOpt_Durable_NativeSP]
(
ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1000000 ) ,
FirstName VARCHAR(50) NOT NULL ,
LastName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

--Create Natively-Compiled Stored Procedure
CREATE PROCEDURE uspInsertRecords
 @rowcount INT,  
 @firstName VARCHAR(50),
 @lastName VARCHAR(50),
 @remarks VARCHAR(50)
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].[Person_MemOpt_Durable_NativeSP] VALUES (@i, @firstName, @lastName, @remarks);  
    SET @i += 1;  
  END;  
END;  
GO

Now that the necessary data structures and natively-compiled stored procedure are created, let's run the workload against each table.

--We are working with the database created in part 1 of this series
USE InMemOLTPDB;
GO

--Initialization
SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
GO

DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timeDiff INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @firstname VARCHAR(50) = N'Firstname';  
DECLARE @lastname VARCHAR(50) = N'Lastname';  
DECLARE @remarks VARCHAR(50) = N'Remarks';  

--Clear buffers for disk-based execution
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

--Run disk-based execution
BEGIN TRAN;  
  WHILE @i <= @rowcount  
  BEGIN;      
INSERT INTO [dbo].[Person_DiskBased] VALUES (@i,@firstname,@lastname,@remarks);
    SET @i += 1;  
  END;  
COMMIT;  
  
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Disk-Based Execution: ' + cast(@timeDiff AS VARCHAR(10)) + ' ms';  

--Clear buffers for memory-optimized/interop execution
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

-- Run memory-optimized execution/interop execution
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
    BEGIN;        
 INSERT INTO [dbo].[Person_MemOpt_Durable] VALUES (@i,@firstname,@lastname,@remarks);
      SET @i += 1;  
    END;  
COMMIT;  
  
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Memory-optimized execution (interop): ' + cast(@timeDiff as VARCHAR(10)) + ' ms';  
  

--Clear buffers for memory-optimized with natively-compiled stored procedure execution
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

--Run memory-optimized with natively-compiled stored procedure execution
SET @starttime = sysdatetime();   
EXECUTE uspInsertRecords @rowcount, @firstname, @lastname, @remarks; 
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Memory-optimized with natively-compiled stored procedure execution: ' + cast(@timeDiff as varchar(10)) + ' ms';  
go  
  
--Count records-check result in each table
SELECT COUNT(*) FROM dbo.Person_DiskBased (NOLOCK)
SELECT COUNT(*) FROM dbo.Person_MemOpt_Durable (NOLOCK)
SELECT COUNT(*) FROM dbo.Person_MemOpt_Durable_NativeSP (NOLOCK)

--Re-initialize tables for new run (optional)
DELETE dbo.Person_DiskBased;  
DELETE dbo.Person_MemOpt_Durable;
DELETE dbo.Person_MemOpt_Durable_NativeSP;
GO

Execution Results:


In-Memory OLTP Example Execution Results

As you can see from the above screenshot:
  • Disk-based execution took 2408 ms to complete.
  • Memory-Optimized/interop execution took 297 ms to complete (8x speedup).
  • Memory-Optimized with natively-compiled stored procedure took only 94 ms to complete (25.6x speedup).

Below you can also find some graphs illustrating the execution results:

In-Memory OLTP Execution Results


In-Memory OLTP Execution Results


Conclusions
In this article (part 2 of the series dedicated to SQL Server In-Memory OLTP) we talked about natively-compiled stored procedures and saw a full example comparing the execution of 100.000 insert statements against three modes:
  • Disk-Based
  • Memory-Optimized/Interop
  • Memory-Optimized with Natively-Compiled Stored Procedure
For the above scenario, we witnessed a performance improvement of 8x for the Memory-Optimized/Interop mode and 25.6x for Memory Optimized with Natively Compiled stored procedure.

The above is just a small indication on what you can achieve with SQL Server In-Memory OLTP.

Stay tuned as subsequent articles in this series will discuss more specialized topics of In-Memory OLTP!



Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: