Use Memory Optimization for Faster temp table performance

Read this article to learn how to optimize your SQL code by using memory-optimized tables instead of SQL Server temporary tables.

Temp tables are widely used for storing temporary data in stored procedures, functions, or T-SQL statements. SQL Server stores these temp tables in the TempDB database. Sometimes, we might see a performance bottleneck for temp tables due to heavy IO activity in TempDB. Is there any way to create and use Temp tables without touching the TempDB database? Yes, let’s find it out in this article.

If you use temporary tables, you can consider converting them to leverage memory-optimized tables for improving performance with minimal code changes. SQL Server memory-optimized tables store data in memory for best data access performance without touching the TempDB.

SQL Server memory-optimized tables consist of two durability types:

• SCHEMA_DATA: It preserves data and schema, so you do not have the risk of losing data.

• SCHEMA_ONLY: It preserves only the table schema. Therefore, if you restart SQL Server, you lose the data. However, the table schema is re-created. Therefore, you create the table at deployment time, not at runtime.

I use the [DBBlogger] database for hosting memory-optimized temp tables for the demonstration. We need the following things to enable memory-optimized features in a database.

Add a new MEMORY_OPTMIZED_Data FileGroup

ALTER DATABASE DBBlogger 
    ADD FILEGROUP FgMemOptimized  
        CONTAINS MEMORY_OPTIMIZED_DATA;  
go  

Add a new data file in the MEMORY_OPTMIZED_Data FileGroup

ALTER DATABASE DBBlogger 
    ADD FILE  
    (  
        NAME = N'DBBlogger_memoryoptmized',  
        FILENAME = N'C:\SQL\DBBlogger_memoryoptmized_mod'  
     )   TO FILEGROUP FgMemOptimized;  

You can verify the filegroup and its file using the database properties -> FileGroups and Memory OPTIMIZED DATA section.

The following script creates a Memory-Optimized Table with option MEMORY_OPTIMIZED and Durability as SCHEMA_ONLY to recover only table schema in case of a crash. Due to the compile-time optimizations creating a memory-optimized temp table takes a little longer than a regular temp table.

Let’s insert a few records in this memory-optimized temp table and view the logical\physical reads using SET STATISTICS IO ON.

CREATE TABLE [MemoryOptTempTable]
(
 [ID] INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED ,
 [First_Name] nvarchar(10) NULL,
 [Last_Name] nvarchar(10) NULL,
 )
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Insert record into the [MemoryOptTempTable].

Set statistics IO ON
Insert into MemoryOptTempTable(first_name,last_name) values ('A','B')
Go

As shown below, the message tab does not return any logical or physical IO information because these tables exist in memory and do not use the TempDB database for disk-relate activity.

Which one is faster – Regular Temp Table or Memory Optimized Temp Table?

We will compare the performance of both regular Temp Table and memory-optimized tables(temp). To compare the performance, we create two stored procedures.

Create a stored procedure with Regular Temp Table

CREATE or ALTER PROCEDURE RegularTempTableTest 
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Temp1
(
 [ID] INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED  ,
 [First_Name] nvarchar(10) NULL,
 [Last_Name] nvarchar(10) NULL,
 )
 INSERT INTO #Temp1 (First_Name,Last_Name) VALUES ('Test','Data')
 DROP TABLE #Temp1
 END
GO

Create a stored procedure with memory-optimized Temp Table

As shown below, we did not define the memory-optimized temp table creation in the procedure. We cannot create it during runtime, and you need to create it during compilation time. Here, we will use the memory-optimized table created earlier.

CREATE PROCEDURE MemoryOptTest
AS
BEGIN
SET NOCOUNT ON;
 INSERT INTO [MemoryOptTempTable] (First_Name,Last_Name) VALUES ('Test','Data')
 END
GO

We will use the SqlQueryStress tool to check the stored procedure performance in multiple iterations. You can download the SqlQueryStress tool from the GitHub https://github.com/ErikEJ/SqlQueryStress/releases or the Windows store. Once downloaded, extract the folder, launch SqlQueryStress.exe and run as follows.

Execute regular Temp Table Stored procedure 20,000 iterations

It took 55 seconds to execute the stored procedure [RegularTempTableTest] with 4 logical reads per iteration and 0.0043 CPU seconds per Iteration.

Similarly, if we increase the iterations to 50000 with 2 threads, the query completes in 1.41 minutes with 3 logical reads per second and 0.0199 CPU seconds per Iteration.

Execute memory-optimized Temp Table Stored procedure with 20,000 iterations

The SqlQueryStress tool shows that 20000 iterations for memory-optimized temp table stored procedure finished in 10 seconds without any logical reads and 0.0154 CPU seconds per Iteration

Similarly, the 50,000 iterations with 2 threads took only 45 seconds without logical reads and 0.0104 CPU Seconds per Iteration.

In summary, the performance comparison data is below. As shown optimized, the memory optmized temp tables Optimized are having less elapsed time and no logical reads. These tables do not impact due to high IO or TempDB contention issues.

​Stored Procedure execution	Number of executions	Threads	Elapsed time	Logical Reads/Iteration
With Temp Table	20000	1	55 seconds	4
With Memory Optmized temp table	20000	1	10 seconds	NA
With Temp Table	50000	2	101 seconds	3
With Memory Optmized temp table	50000	2	45 seconds	NA

Note: You can run the Delete statement to clear the memory-optimized table content. It does not clear it automatically until you restart SQL Server. Truncate table statement is not supported.

Let’s restart SQL Server and test if SQL Server recreates the memory-optimized temp table schema. As shown below, SQL Server retains schema for these tables. You can either drop these tables or delete data from them to reuse it.

This article explored the performance improvement for the SQL Server temp tables using a memory-optimized feature that avoids any performance issues due to TempDB. You can evaluate your requirements and workloads and see memory-optimized temp table is suitable for you.