Compare SQL Server Performance of In-Memory vs Disk Tables

Problem

With the release of SQL Server 2014 there came a lot of new features. One of the more interesting ones is the “In-Memory OLTP” feature. One would assume that storing a table in memory would most definitely be faster than using the conventional disk-based table storage engine. But just how much faster is it? This tip will use a few sample SELECT, INSERT, UPDATE and DELETE statements to compare the performance difference between each type of table storage.

Solution

For those that are not familiar with the In-Memory OLTP feature the following two links will give a good overview of the feature, outline the requirements for using it, as well as provide some sample TSQL to get you started.

Sample Table Setup

In order to run our test scenario let’s create two identical tables with the only difference being that one with be stored the traditional way on disk and the other will be stored using the new feature in memory.

-- Table creation logic for disk-based storage
CREATE TABLE testtable_regular ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL);
-- Table creation logic for in-memory storage                        
CREATE TABLE testtable_inmemory ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON);

Now that we have a couple of tables let’s run our first test. Since the tables are empty we’ll test INSERTs first and put similar data into each table with SQL Profiler running and compare the results. Here is the T-SQL to perform the inserts.

DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   INSERT INTO testtable_regular (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END
GO
DECLARE @val INT
SELECT @val=1
WHILE @val <= 200000
BEGIN  
   INSERT INTO testtable_inmemory (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END
GO

Looking at the results we can see that as one would expect the In-Memory table inserts have almost no disk activity compared to quite a bit of disk activity from the disk-based table. There are however two interesting things to note from this test. First, even though the disk-based table did have to perform much more IO it was only marginally slower in overall duration. Second, the disk-based table actually used less CPU than the In-Memory table to perform the inserts.

Table TypeDuration (ms)CPU (ms)ReadsWrites
Disk Based1172601007810143171364
In Memory1126931126631

Next we’ll take a look how some SELECT statements perform. We’ll look at querying both a single record from each table as well as querying a range of records. Here is the T-SQL for each of these test cases.

SELECT * FROM testtable_regular WHERE [col1]=11493
SELECT * FROM testtable_regular WHERE [col1]=51943
SELECT * FROM testtable_regular WHERE [col1]=101873
SELECT * FROM testtable_regular WHERE [col1]=131320
SELECT * FROM testtable_regular WHERE [col1]=181623
GO
SELECT * FROM testtable_regular WHERE [col1] BETWEEN 11493 AND 51943
SELECT * FROM testtable_regular WHERE [col1] BETWEEN 131320 AND 181623
GO
SELECT * FROM testtable_inmemory WHERE [col1]=11493
SELECT * FROM testtable_inmemory WHERE [col1]=51943
SELECT * FROM testtable_inmemory WHERE [col1]=101873
SELECT * FROM testtable_inmemory WHERE [col1]=131320
SELECT * FROM testtable_inmemory WHERE [col1]=181623
GO
SELECT * FROM testtable_inmemory WHERE [col1] BETWEEN 11493 AND 51943
SELECT * FROM testtable_inmemory WHERE [col1] BETWEEN 131320 AND 181623
GO

As with the first test we run these statements with SQL Profiler running. Looking at the results below we see that in every aspect the In-Memory table outperformed the disk-based table. The only measure that was a little bit close was the overall duration of the query, but this probably can be explained by the fact the query has to return the data and both queries return the same number of records.

Table TypeQuery TypeDuration (ms)CPU (ms)ReadsWrites
Disk BasedSingle Row2080150
In MemorySingle Row0000
Disk BasedMultiple Row88711017960
In MemoryMultiple Row6906200

Finally, let’s take a look at some UPDATE and DELETE statements and see how they compare. Below are some T-SQL statements to perform each of these operations.

UPDATE testtable_regular SET col3=col3+10 WHERE [col1]=21493
UPDATE testtable_regular SET col3=col3+10 WHERE [col1]=61943
UPDATE testtable_regular SET col3=col3+10 WHERE [col1]=111873
UPDATE testtable_regular SET col3=col3+10 WHERE [col1]=141320
UPDATE testtable_regular SET col3=col3+10 WHERE [col1]=191623
GO
UPDATE testtable_inmemory SET col3=col3+10 WHERE [col1]=21493
UPDATE testtable_inmemory SET col3=col3+10 WHERE [col1]=61943
UPDATE testtable_inmemory SET col3=col3+10 WHERE [col1]=111873
UPDATE testtable_inmemory SET col3=col3+10 WHERE [col1]=141320
UPDATE testtable_inmemory SET col3=col3+10 WHERE [col1]=191623
GO
DELETE FROM testtable_regular WHERE [col1]=12494
DELETE FROM testtable_regular WHERE [col1]=31944
DELETE FROM testtable_regular WHERE [col1]=91874
DELETE FROM testtable_regular WHERE [col1]=131321
DELETE FROM testtable_regular WHERE [col1]=171624
GO

DELETE FROM testtable_inmemory WHERE [col1]=12494
DELETE FROM testtable_inmemory WHERE [col1]=31944
DELETE FROM testtable_inmemory WHERE [col1]=91874
DELETE FROM testtable_inmemory WHERE [col1]=131321
DELETE FROM testtable_inmemory WHERE [col1]=171624
GO

Looking at the SQL Profiler results from these statements we again see that the In-Memory table outperforms the disk-based table in every single category.

Table TypeQuery TypeDuration (ms)CPU (ms)ReadsWrites
Disk BasedUPDATE50250
In MemoryUPDATE3000
Disk BasedDELETE50450
In MemoryDELETE3000

Summary

It’s easy to see from these simple test results why the “In-Memory OLTP” feature of SQL Server 2014 has received so much attention. With the performance benefits we realized above the only question left is how much memory can I get into my server?

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *