Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simple Experiment with SQL Server In-Memory OLTP is 79 Times Faster


By:   |   Read Comments (2)   |   Related Tips: More > In-Memory OLTP

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem

In-Memory OLTP in SQL Server is a very powerful feature. In one of my previous tips, I suggested three approaches you can follow in order to get an indication whether you can benefit from using this feature for your specific workload. In this tip I will present a simple, yet comprehensive example of workload processing that was optimized using In-Memory OLTP which led to a performance boost up to 79x times faster processing over disk-based execution.

Solution

Let's start this tip by first defining the scenario we are going to use. The scenario is quite simple as the purpose of this tip is to show how easy it is to boost performance when using In-Memory OLTP in SQL Server.

So here's the scenario: A worldwide retail sales organization has 50 million customers. As the organization celebrates its 30 successful years of operation, the Management decided to organize local celebrations in the major countries where the organization operates. As it is not possible to have celebrations in all countries all around the world, the Management requested Information Technology at HQ to generate two reports. The requirements for the reports are:

  1. Generate a list with the top 25 countries based on the number of customers
  2. Generate a list with the top 25 countries based on the total sales per country

Based on the above two reports, the Management will decide in which countries the company will host its events.

The organization uses SQL Server as their data platform solution. However, they have not yet implemented In-Memory OLTP. Information technology sees this as a great opportunity to showcase the benefits of applying In-Memory OLTP within the organization.

Below you can see the "customers" table's (disk-based) definition:

--Disk-Based Table Definition
CREATE TABLE [dbo].[tblCustomers](
   [id] [INT] NOT NULL PRIMARY KEY,
   [code] [CHAR](6) NOT NULL,
   [firstName] [VARCHAR](100) NOT NULL,
   [lastName] [VARCHAR](100) NOT NULL,
   [address1] [VARCHAR](150) NOT NULL,
   [address2] [VARCHAR](150) NULL,
   [zipCode] [CHAR](5) NOT NULL,
   [countryCode] [CHAR](3) NOT NULL INDEX IX_CountryCode NONCLUSTERED,
   [lastPurchaseDate] [DATE] NOT NULL,
   [totalPurchasesAmount] [FLOAT] NOT NULL
);
GO

Transforming a disk-based table to memory-optimized can be very easy. You first need to identify what types of indexes you should use (there is a very useful MSDN article about this) as well as the table's durability. Durability sets whether the memory-optimized table's data will be lost in a case of a server failover or crash (SCHMA_ONLY) or will be permanent (SCHEMA_AND_DATA). As we want to compare apples with apples, the durability for the memory-optimized table will be set to SCHEMA_AND_DATA in order for the data in the table to be permanent. Also, in a similar manner to the disk-based table, we will add a nonclustered Hash index for the primary key and a nonclustered index for the "countryCode" column.

The definition for the memory-optimized table is provided below:

--Memory-Optimized Table Definition
CREATE TABLE [dbo].[tblCustomersMemOpt](
   [id] [INT] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 70000000),
   [code] [CHAR](6) NOT NULL,
   [firstName] [VARCHAR](100) NOT NULL,
   [lastName] [VARCHAR](100) NOT NULL,
   [address1] [VARCHAR](150) NOT NULL,
   [address2] [VARCHAR](150) NULL,
   [zipCode] [CHAR](5) NOT NULL,
   [countryCode] [CHAR](3) NOT NULL INDEX IX_CountryCode NONCLUSTERED,
   [lastPurchaseDate] [DATE] NOT NULL,
   [totalPurchasesAmount] [FLOAT] NOT NULL
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );
GO

Another important part of defining a memory-optimized table is the "BUCKET_COUNT" setting. The recommended values for BUCKET_COUNT must be between 1.5 and 2 times the estimated value of unique values. In this example, the unique values (customers) are 50M. Based on that I should set the BUCKET_COUNT to 75M, but I wanted to save some RAM, so I set it to 70M.

The two queries to be executed against the disk-based table are:

Query 1:

--Clear buffer
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

--Disk-Based Processing: Query 1
SET STATISTICS TIME ON; 
SELECT TOP 25 countryCode, COUNT(*) AS totalCustomers
FROM dbo.tblCustomers
GROUP BY countryCode
ORDER BY 2 DESC;
SET STATISTICS TIME OFF; 

Query 2:

--Clear buffer
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

--Disk-Based Processing: Query 2
SET STATISTICS TIME ON; 
SELECT TOP 25 countryCode, SUM(totalPurchasesAmount) AS totalSales
FROM dbo.tblCustomers
GROUP BY countryCode
ORDER BY 2 DESC;
SET STATISTICS TIME OFF;

The two queries to be executed against the memory-optimized table are:

Query 1:

--Clear buffer
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

--Memory-Optimized Processing: Query 1
SET STATISTICS TIME ON;
SELECT TOP 25 countryCode, COUNT(*) AS totalCustomers
FROM dbo.tblCustomersMemOpt
GROUP BY countryCode
ORDER BY 2 DESC;
SET STATISTICS TIME OFF; 

Query 2:

--Clear buffer
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

--Memory-Optimized Processing: Query 2
SET STATISTICS TIME ON;
SELECT TOP 25 countryCode, SUM(totalPurchasesAmount) AS totalSales
FROM dbo.tblCustomersMemOpt
GROUP BY countryCode
ORDER BY 2 DESC;
SET STATISTICS TIME OFF;

Execution of Query 1 Results for Disk-Based and In-Memory

Disk-Based Execution - Query 1 (result and execution time):

Disk-Based Execution - Query 1 - Result

Disk-Based Execution - Query 1 - Times

Memory-Optimized Execution - Query 1 (result and execution time)

Memory-Optimized Execution - Query 1 - Result

Memory-Optimized Execution - Query 1 - Times

Analysis of Query 1 Results

As you can see from the above screenshots, the results are identical thus proving that both queries produce the same result. If we compare the elapsed execution times, we can see that the disk-based execution's elapsed time was 124975 ms and the memory-optimized execution's elapsed time was 2994 ms. This means that the memory-optimized execution was 41.74 times faster over disk-based execution!

Execution of Query 2 Results for Disk-Based and In-Memory

Disk-Based Execution - Query 2 (result and execution time)

Disk-Based Execution - Query 2 - Result

Disk-Based Execution - Query 2 - Times

Memory-Optimized Execution - Query 2 (result and execution time)


Memory-Optimized Execution - Query 2 - Result

Memory-Optimized Execution - Query 2 - Times

Analysis of Query 2 Results

As you can see from the above screenshots, again, the results are identical thus proving that both queries produce the same result. If we compare the elapsed execution times, we can see that the disk-based processing's elapsed time was 290360 ms and the memory-optimized processing's elapsed time was 3674 ms. This means that the memory-optimized execution was 79 times faster over disk-based execution!

Comparison Graphs

The execution time comparisons for Query 1 and 2 are also plotted on the below graphs:

Query 1 - Exec Times Graph

Query 2 - Exec Times Graph

Conclusion

In-Memory OLTP Engine is a very powerful feature in SQL Server 2014 and later. Especially in SQL Server 2016, there are significant improvements that make it even easier to work with In-Memory OLTP. In this example I have processed 50 million records of data using two queries. The first query executed 41x faster using In-Memory OLTP and the second 79x faster. This simple example gives only a small indication on what you can achieve with In-Memory OLTP in SQL Server.

In-Memory OLTP is seamlessly integrated into SQL Server thus making it easy to use it. Get the Developer Edition of SQL Server 2016 today, it's free. Start experimenting with In-Memory OLTP and you will soon start realizing the performance benefits of using this powerful engine.

Next Steps

Review the following tips and other resources:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server Architect, Software Developer, and a Microsoft Data Platform MVP (SQL Server).

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 20, 2016 - 9:49:35 AM - Artemakis Artemiou Back To Top

Hi,

Thank you for your comment. Yes, you can combine columnstore indexes with memory-optimized tables for real-time operational analytics in SQL Server 2016.It is an interesting thing to try.

I haven't really tried it yet, but I plan to do so soon!


Tuesday, September 20, 2016 - 12:23:24 AM - 王成辉 Back To Top

 How about Columnstore Index?


Learn more about SQL Server tools