In my previous article of this blog series, I introduced SQL ServerIn-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”.
What are SQL Server Natively-Compiled Stored Procedures?
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.
Watch a Video About Natively-Compiled Stored Procedures
An Example with Memory-Optimized Tables and Natively-Compiled Stored Procedures
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.
Creating the Memory-Optimized Tables
/*
* 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
Learn more about SQL Server In-Memory OLTP – Enroll to the Course!
In this course, you will learn all about SQL Server’s In-Memory Database Processing Engine also known as In-Memory OLTP. To this end, you will learn how to enable In-Memory OLTP in SQL Server, what memory-optimized tables and natively-compiled stored procedures are, as well as, how to boost the performance of your data processes using this powerful SQL Server feature.
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
--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
Running the Workload of 100.000 Inserts
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).
Below you can also find some graphs illustrating the 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, a distinguished Senior Database and Software Architect, brings over 20 years of expertise to the IT industry. A Certified Database, Cloud, and AI professional, he earned the Microsoft Data Platform MVP title for nine consecutive years (2009-2018). As the founder of SQLNetHub and GnoelixiAI Hub, Artemakis is dedicated to sharing his knowledge and democratizing education on various fields such as: Databases, Cloud, AI, and Software Development. His commitment to simplicity and knowledge sharing defines his impactful presence in the tech community.
Views:3,146
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.