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”.
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!
We recommend enrolling to our online course titled “Boost SQL Server Database Performance with In-Memory OLTP” and learn how to harness the power of SQL Server’s In-Memory Optimization and boost the performance of your data processes!
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.
Creating the Natively-Compiled Stored Procedure
--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
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).
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:
- 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!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- Boost SQL Server Database Performance with In-Memory OLTP
- In-Memory OLTP Simulator
- Introducing In-Memory Optimization in SQL Server
- Getting Started with SQL Server In-Memory OLTP – Part 1
- Introducing In-Memory OLTP Simulator
- In-Memory Optimization in SQL Server: Will my Workload Execute Faster?
- Simple Experiment with SQL Server In-Memory OLTP is 79 Times Faster
Check our other related SQL Server Performance articles.
Subscribe to our newsletterand stay up to date!
Check In-Memory OLTP Simulator: Our special tool for easily benchmarking memory-optimized tables in SQL Server!
Check out our latest software releases!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.