In-Memory OLTP: Using Memory-Optimized Tables to Replace SQL Server Temporary Tables
By: Simon Liew | Updated: 2017-01-12 | Comments | Related: More > In-Memory OLTP
In my previous tip, I have shown how cached temporary tables can alleviate DDL contention in tempdb system tables and thus allows for higher workload throughput. In this tip, I will show one option how the throughput and performance can be further improved using a memory-optimized table.
A brief description on the terminology used in this tip: In-Memory OLTP and memory-optimized table.
- In-Memory OLTP is a new database engine component fully integrated into SQL Server. In-Memory OLTP encompass features such as memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.
- Memory-optimized tables refer to tables created with the new data structures added as part of In-Memory OLTP. Memory-optimized tables do not have data pages or extents, there are just "data rows" written to memory sequentially in the order the transactions occurred.
If your organization is using SQL Server 2014 Enterprise Edition and higher, you can leverage the In-Memory OLTP features in many ways. The good news is that the programmability surface area including the In-Memory OLTP engine is now made available for SQL Server 2016 Standard, Web and Express with the introduction of SQL Server 2016 Service Pack 1.
If you have read my previous tip on SQL Server Temporary Table Caching, this tip will show how to a utilize memory-optimized table as an alternative for temporary tables to further improve workload throughput and performance.
We will create three non-natively compiled stored procedures to benchmark the workload throughput and performance:
- Stored Procedure 1: Creates a cached temporary table and performs a small data insertion
- Stored Procedure 2: Utilizes a memory-optimized table to replace the temporary table, but includes additional code to simulate an equivalent function
- Stored Procedure 3: Uses similar code as stored procedure 2 except using a disk-based permanent table
The tests are performed on a SQL Server 2016 Developer Edition Service Pack 1 on a virtual machine with 2 vCPU and 8GB of RAM. SQL Server max memory is set to 6GB.
Steps below outline the creation of the test configuration and benchmark.
Step 1 - Database Creation
Test database [MemOptDB] is created with a memory-optimized filegroup. The database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON is used to alleviate the need to have a table hint on memory-optimized tables.
CREATE DATABASE [MemOptDB] GO ALTER DATABASE [MemOptDB] ADD FILEGROUP MemOptFG CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE [MemOptDB] ADD FILE (name='MemOptFG_dat1', filename='D:\SQLDATA\MemOptFG_dat1') TO FILEGROUP MemOptFG ALTER DATABASE [MemOptDB] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GO
Step 2 - Table Creation
Memory-optimized table is optimized for run-time because the table will be compiled to a DLL. The DLL is not something that you would want to create on the fly in a loop. So we will create a permanent memory-optimized table with SCHEMA_ONLY with additional logic to simulate the function similarly to the temporary table in this test.
Note the following characteristics of memory-optimized table with SCHEMA_ONLY:
- Is stored only in memory
- Has no component on disk
- Involves no I/O activity
- Involves no tempdb utilization
- Table schema will be durable, but data rows will not be persisted when the SQL Server instance is restarted
- Do not require transaction logging and the checkpoint process
CREATE TABLE [MemOptDB].[dbo].[MemOptTemp] ( [SPID] INT NOT NULL, [A] [bigint] IDENTITY(1,1) NOT NULL, [val] CHAR(29) NULL, CONSTRAINT [PK_ MemOptTemp] PRIMARY KEY NONCLUSTERED ( [SPID], [A] ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ) GO
Memory-optimized table which uses SCHEMA_ONLY can participate in a transaction like any user defined table.
BEGIN TRAN INSERT INTO [MemOptDB].[dbo].[MemOptTemp] (SPID, val) VALUES (@@SPID, 'A'), (@@SPID, 'B'), (@@SPID, 'C'), (@@SPID, 'D') ROLLBACK TRAN SELECT * FROM [MemOptDB].[dbo].[MemOptTemp] ----------------------------------------- (4 row(s) affected) (0 row(s) affected)
Step 3 - Disk Based Table Creation
As a control step, a disk-based permanent table is created to be compared against the memory-optimized table.
CREATE TABLE [MemOptDB].[dbo].[PermTemp] ( [SPID] INT NOT NULL, [A] [bigint] IDENTITY(1,1) NOT NULL, [val] CHAR(29) NULL, CONSTRAINT [PK_PermTemp] PRIMARY KEY NONCLUSTERED ( [SPID], [A] ASC ) ) GO
Step 4 - Non-Natively Compiled Stored Procedures
Three non-natively compiled stored procedures are created for performance benchmarking.
- Stored procedure pr_CachedTempTable creates a cached temporary table and performs a 4 row insertion.
- Stored procedure pr_MemOptTempTable has a new column [SPID] added in the code so that this can carry the function of having the multiple stored procedure accessing the same memory-optimized table and to filter the retrieve records local to the stored procedure based on session id.
- Stored procedure pr_PermTempTable contains code similar to pr_MemOptTempTable, but uses a disk-based permanent table.
USE MemOptDB GO CREATE PROCEDURE dbo.pr_CachedTempTable -- Stored Procedure 1 AS BEGIN SET NOCOUNT ON; CREATE TABLE #T (A INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, val char(20)) INSERT INTO #T (val) VALUES ('A'), ('B'), ('C'), ('D') END GO CREATE PROCEDURE dbo.pr_MemOptTempTable -- Stored Procedure 2 AS BEGIN SET NOCOUNT ON; INSERT INTO MemOptTemp (SPID, val) VALUES (@@SPID, 'A'), (@@SPID, 'B'), (@@SPID, 'C'), (@@SPID, 'D') DELETE FROM MemOptTemp WHERE [SPID] = @@SPID END GO CREATE PROCEDURE dbo.pr_PermTempTable -- Stored Procedure 3 AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[PermTemp] (SPID, val) VALUES (@@SPID, 'A'), (@@SPID, 'B'), (@@SPID, 'C'), (@@SPID, 'D') DELETE FROM [dbo].[PermTemp] WHERE [SPID] = @@SPID END GO
Step 5 - Stored Procedure Testing
The three stored procedures are executed 10,000 times each serially with a delay of 2 seconds between stored procedure. Performance monitor is already configured and running in the background and captures two performance counters every second – CPU and Log Bytes Flushed/sec as shown below.
EXEC MemOptDB.dbo.pr_CachedTempTable GO 10000 WAITFOR DELAY '00:00:02' GO EXEC MemOptDB.dbo.pr_MemOptTempTable GO 10000 WAITFOR DELAY '00:00:02' GO EXEC MemOptDB.dbo.pr_PermTempTable GO 10000
Performance counters and execution durations from the three stored procedures are as below. It is not surprising that even with the DELETE query on a memory-optimized table, it still executes approximately 3 times faster than pr_CachedTempTable which performs a rows insertion only into a cached temporary table. Stored procedure pr_PermTempTable performs the worst in this test and it is due to the additional overhead when the DELETE query also needs to update system pages.
|Run 1 (seconds)||Run 2 (seconds)||Run 3 (seconds)||Avg. CPU||Avg. Log Flushed/sec|
Memory-optimized table can be very beneficial to handle transient data such as a temporary table or table variable. Memory-optimized tables allows a much higher throughput given the same set of server resources because it is no longer constraint by any locks, latches or spinlocks.
SQL Server implements an optimistic multi-version concurrency control when accessing memory-optimized tables. Understanding the behavior and differences in In-Memory OLTP is important towards adopting this technology because it will have an impact on aspects of your system.
- Read about SQL Server Temporary Table Caching
- Read about Requirements for Using Memory-Optimized Tables
- More about Memory-Optimized Tables
- Read more SQL Server In-Memory OLTP tips.
Last Updated: 2017-01-12
About the author
View all my tips