By: Ben Snaidero | Comments (6) | Related: > In Memory OLTP
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 Type | Duration (ms) | CPU (ms) | Reads | Writes |
---|---|---|---|---|
Disk Based | 117260 | 10078 | 1014317 | 1364 |
In Memory | 112693 | 11266 | 3 | 1 |
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 Type | Query Type | Duration (ms) | CPU (ms) | Reads | Writes |
---|---|---|---|---|---|
Disk Based | Single Row | 208 | 0 | 15 | 0 |
In Memory | Single Row | 0 | 0 | 0 | 0 |
Disk Based | Multiple Row | 887 | 110 | 1796 | 0 |
In Memory | Multiple Row | 690 | 62 | 0 | 0 |
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 Type | Query Type | Duration (ms) | CPU (ms) | Reads | Writes |
---|---|---|---|---|---|
Disk Based | UPDATE | 5 | 0 | 25 | 0 |
In Memory | UPDATE | 3 | 0 | 0 | 0 |
Disk Based | DELETE | 5 | 0 | 45 | 0 |
In Memory | DELETE | 3 | 0 | 0 | 0 |
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
- Read other tips on SQL Server Performance Tuning
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips