Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Comparing memory-optimized tables and disk-based tables

In all tests performed by expert Basit Farooq, memory-optimized tables outperformed disk-based tables by running faster and generating less disk I/O.

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.

Figure 1. The T-SQL code for 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 11. The results of comparing memory-optimized to disk-based tables.

Next Steps

Learn about in-memory OLTP in SQL Server 2014

Find out how to improve OLTP performance with SQL Server 2014 in-memory

This was last published in September 2015

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

Which do you use more, memory-optimized tables or more disk-based tables?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close