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

In my previous article of this blog series, I introduced SQL Server In-Memory OLTP and explained how it is possible to create an In-Memory OLTP – enabled database. Also, I showed via simple examples, how easy is to create 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. This the second part of my article series “Getting Started with SQL Server 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:

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:

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).