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!



[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

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: