maxkabakov - Fotolia
In-memory OLTP, which is designed to boost OLTP performance and reduce overall processing times, was introduced with SQL Server 2014. Improvements have been made to in-memory OLTP in SQL Server 2016. In-memory OLTP lets you create memory-optimized tables within a conventional relational database. According to Microsoft, performance of existing OLTP workloads can increase 50 times simply by using the in-memory OTLP engine. In this article, I'll compare the performance of memory-optimized tables and disk-based tables by running the data modification operations against each of them.
Prerequisites for this demo
For this demo, I'll create a test database with a memory-optimized filegroup. Then I'll use this database to hold the demo's tables and stored procedures. To create the test database, I enter and execute the Transact-SQL script in Figure 1 in SQL Server Management Studio (SSMS). This creates a database called PerfTestDB.
In addition to creating the PerfTestDB database, the script above alters the database to add a memory-optimized (MEMORY_OPTIMIZED_DATA) filegroup. The name of the memory-optimized filegroup is PerfTestDB_Mem_Optimized. The filegroup is necessary if you want to create memory-optimized tables in the database.
After creating the database, I create the tables for this demo. To do so, I use the script in Figure 2:
This script creates the following two tables inside PerfTestDB database:
- dbo.OnDisk: A normal database table whose data will reside on disk.
- dbo.InMemory: An in-memory table whose data will reside in memory.
Both tables have the same schema. The only difference is that the data for one table will reside on disk; the data for other table will reside in memory.
Performance Test 1 -- INSERT operation
Now, let's run our first test. I will insert one million rows each to both tables, with SQL Profiler running in the background. Then I'll examine the amount of time and resources each insert operation took for each table.
In SSMS, I run the Transact-SQL script below to insert one million rows in the disk-based table (dbo.OnDisk):
Next, I run the following Transact-SQL script in SSMS, to insert one million rows in the memory-optimized table (dbo.InMemory):
The SQL Profiler results in Figure 5 show that the INSERT operation against the memory-optimized table runs faster than it does against the disk-based table. Moreover, the INSERT operation against the memory-optimized table generates no disk I/O, compared to quite a bit of disk I/O activity for the disk-based table. Finally, the memory-optimized table used less CPU than the disk-based table.
Performance Test 2 -- SELECT operation
In this test, I will run two SELECT statements against both tables. The first SELECT statement returns multiple rows based on values specified in the WHERE clause of the SELECT statement. The second SELECT statement returns multiple rows based on a range specified in the WHERE clause of the SELECT statement.
To return multiple rows from the disk-based table (dbo.OnDisk) I run the SELECT statements in Figure 6, with SQL Profiler running in the background.
To return multiple rows from the memory-optimized table (dbo.InMemory), I run the following SELECT statements with SQL Profiler running in the background.
The SQL Profiler results the table below show that the SELECT statement against the memory-optimized table runs faster and uses fewer resources than the disk-based table.
Performance Test 3 -- UPDATES and DELETES operations
In this final test, I run UPDATE and DELETE statements against both tables to see how they compare. To update and delete data inside the disk-based table (dbo.OnDisk), I run the UPDATE and DELETE statements in Figure 9 with SQL Profiler running in the background:
To update and delete data inside the memory-optimized table (dbo.InMemory), I run the following UPDATE and DELETE statements with SQL Profiler running in the background:
The SQL Profiler results in Figure 11 show that in all areas, memory-optimized tables surpass disk-based tables.
Learn about in-memory OLTP in SQL Server 2014
Find out how to improve OLTP performance with SQL Server 2014 in-memory