I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

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.

Figure 1. The T-SQL code for 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:

Figure 2. The T-SQL code to create tables inside PerfTestDB.

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

Figure 3. The T-SQL script to create one million rows in dbo.OnDisk.

Next, I run the following Transact-SQL script in SSMS, to insert one million rows in the memory-optimized table (dbo.InMemory):

Figure 4. The T-SQL script to create one million rows in 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.

Figure 5. The INSERT statement run in dbo.OnDisk versus dbo.InMemory.

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.

Figure 6. T-SQL code to return multiple rows to dbo.OnDisk.

To return multiple rows from the memory-optimized table (dbo.InMemory), I run the following SELECT statements with SQL Profiler running in the background.

Figure 7. T-SQL code to return multiple rows to dbo.InMemory.

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.

Figure 8. The SELECT statement run in dbo.OnDisk versus dbo.InMemory.

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:

Figure 9. T-SQL code to UPDATE and DELETE in dbo.OnDisk.

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:

Figure 10. T-SQL code to UPDATE and DELETE in dbo. InMemory.

The SQL Profiler results in Figure 11 show that in all areas, memory-optimized tables surpass disk-based tables.

Figure 11. The results of comparing memory-optimized to disk-based tables.

Join the conversation

1 comment

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.