In this article, we will be discussing about In-Memory Optimization in SQL Server, and we will see a simple example, of optimizing a workload using In-Memory Optimization and thus improve performance.
Introduction to SQL Server In-Memory Optimization
In one of my previous articles I briefly introduced In-Memory Optimization in SQL Server. In this article we will conduct a simple experiment utilizing this new technology and discussing the benefits. The experiment will be organized in the following steps:
- Create the In-Memory processing enabled database
- Create the tables (disk-based, memory-enabled)
- Populate the tables with sample data
- Prepare the processing code for three scenarios (execution modes):
- Disk-based processing
- In-Memory optimization
- In-Memory optimization with natively compiled stored procedure
- Run all three execution modes and measure execution times
- Discuss findings
Let’s Write Some Code!
OK, enough words, let’s write some code!
Step 1: Create the In-Memory processing enabled database
--Creates a memory-enabled database by adding a memory-optimized filegroup with a specified directory. --Note: This assumes that directory c:\tmp exists (the subdirectory is created by SQL Server). CREATE DATABASE InMemDB; GO ALTER DATABASE InMemDB ADD FILEGROUP [inMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE InMemDB ADD FILE ( name = [inMemOLTP_fg_dir], filename= 'c:\tmp\InMemOLTPDir') TO FILEGROUP [inMemOLTP_fg]; GO
Step 2: Create the tables (disk-based, memory-enabled)
--Create sample tables representing a scenario involving 200000 Products USE InMemDB; GO --Create traditional table (disk-based) CREATE TABLE tblDiskBased( id INT PRIMARY KEY, code VARCHAR(50), descr VARCHAR(250), price float ); GO --Create a durable (data will be persisted) memory-optimized table --Alternative choice: SCHEMA_ONLY = Data will be lost if the server turns off unexpectedly CREATE TABLE tblMemOptimized( id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000), code VARCHAR(50), descr VARCHAR(250), price float )WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA); GO --Create a durable (data will be persisted) memory-optimized table --Alternative choice: SCHEMA_ONLY = Data will be lost if the server turns off unexpectedly --Note: This table will be used by the natively-compiled stored procedure CREATE TABLE tblMemOptimized2( id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000), code VARCHAR(50), descr VARCHAR(250), price float )WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA); GO
So far: We have created a memory enabled database and data structures. Now, it’s time to populate the tables with sample data.
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.
Step 3: Populate the tables with sample data
--Populate the tables with sample data (200K records) --tblMemOptimized DECLARE @i INT SET @i=0 WHILE (@i<200000) BEGIN INSERT INTO tblMemOptimized SELECT CAST(@i as varchar(6)) as id, ('Code '+ (CAST (@i AS varchar(6)))) as code, ('Description '+ (CAST (@i AS varchar(6)))) as descr, (10 + (500-10)*RAND()) as price SET @i=@i+1 END GO --Populate the tables with sample data (200K records) --tblDiskBased --tblMemOptimized2 INSERT INTO tblDiskBased SELECT * FROM tblMemOptimized; GO INSERT INTO tblMemOptimized2 SELECT * FROM tblMemOptimized; GO
All three tables now have same data (200K rows each). You can easily check it out!
3a. Disk-based processing:
--Disk-based processing CREATE PROCEDURE updateDiskBased as BEGIN UPDATE dbo.tblDiskBased SET price=price-(price*0.05) END GO
3b. In-Memory optimization:
--Memory-Optimized CREATE PROCEDURE updateMemoryOptimized as BEGIN UPDATE dbo.tblMemOptimized SET price=price-(price*0.05) END GO
3c. In-Memory optimization with natively compiled stored procedure:
--Natively-Compiled Stored Procedure CREATE PROCEDURE updateMemoryOptimizedNatively with native_compilation, schemabinding, execute as owner as begin atomic with (transaction isolation level = snapshot,language = N'English') UPDATE dbo.tblMemOptimized2 SET price=price-(price*0.05) END GO
So far: We have created a memory enabled database and data structures. We have populated the tables with sample data (all three tables have identical data for experiment consistency purposes) and we have prepared the stored procedures that target each one of the three tables and perform the processing, that is the update of the ‘price’ column by decreasing it by 5%.
Running the Example
Ok, now it’s time to execute the three stored procedures, thus update all tables and measure execution times and here they are:
Additionally, let’s check once more some records just to confirm that the output is the same in all three tables:
First of all, from the above figure we can see that the output of the three update operations is exactly the same which is something that validates the correctness of the three different execution modes.
As you can see from the screenshot with execution times (Figure 2), the slowest execution was the one that involved the disk-based table (elapsed time: 1924 ms) having also the highest CPU time (328 ms).
The fastest execution was the one of the memory-optimized table with the natively compiled stored procedure where the elapsed time was 329 ms and the CPU 93 ms!
The memory optimized execution was faster than the disk-based with 404 ms elapsed time and 250 ms CPU time but it was not faster than the memory-optimized scenario combined with the natively compiled stored procedure.
In-Memory optimization in SQL Server is a great new technology which can significantly improve performance in many cases. Especially in cases of certain workloads such as staging processes in Data Warehouse systems, high data insert rates (i.e. smart metering) and low latency execution scenarios (i.e. investment trading), In-Memory optimization can boost performance. Of course, before applying it on Production systems, a proper analysis is required along with testing.
Enroll to the Online Course!
Enroll 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 improve the performance of your data processes!
Highly Recommended Tool: In-Memory OLTP Simulator
Our solution, “In-Memory OLTP Simulator“, helps you to easily benchmark standard and custom scenarios of data workloads and processing against the powerful In-Memory OLTP Engine in SQL Server. Moreover, you get rich analytics and insights, and see how Memory Optimization in SQL Server can help you boost the performance of your data processes.Try In-Memory OLTP Simulator free for 14 days
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 Optimization in SQL Server: A Simple Experiment
- Introducing In-Memory Optimization in SQL Server
- Getting Started with SQL Server In-Memory OLTP – Part 1
- Getting Started with SQL Server In-Memory OLTP – Part 2
- Where are temporary tables stored in SQL Server?
- SQL Server 2016: TempDB Enhancements
- tempdb growth
Check our other related SQL Server Performance articles.
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHub TV)
Check out our latest software releases!
Check out our eBooks!
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.