SQL Server MeMORY Optimized Table Variables for faster performance

SQL Server memory-optimized tables are a great way to improve performance for table variables. In the previous articles, Use Memory Optimization for Faster temp table performance and Read Session-Specific data from the memory-optimized temp tables, we explored how you can leverage the memory optimization feature in SQL Server to improve SQL Server Temp Table performance. These tables are known as disk-based table variables. These memory tables do not use TempDB. Therefore, it removes IO activity for quick data processing.

Similar to the SQL Server Temp Tables, Table variables also use TempDB database storage. We can memory-optimized table variables for much faster performance.

Before you use a memory-optimized table variable, create a memory-optimized filegroup, as shown in the article Use Memory Optimization for Faster temp table performance.

Suppose you define a table variable with the following query. This query syntax for the table variable is called inline. The inline table variables do not support memory optimization.

DECLARE @tableVariable TABLE 
    ( 
               ID INT   NOT NULL,  
               [Name] VARCHAR(10)
);

We need to do the following steps to use the memory-optimized table variable.

Create a TYPE definition with MEMORY_OPTIMIZED=ON.

CREATE TYPE dbo.SampleTbVariable 
    AS TABLE  
    (  
   ID INT   NOT NULL INDEX id_ix1,   
    [Name] VARCHAR(10)
    ) WITH  
     (MEMORY_OPTIMIZED = ON);  ; 
go  

Declare a table variable that uses memory-optimized TYPE and use it as a regular SQL Server table variable.

SET NoCount ON;  
DECLARE @SampleTbVariable dbo.SampleTbVariable;  
INSERT INTO @SampleTbVariable (ID,[Name]) values (1,'AB');  
SELECT * from @SampleTbVariable;  
go  

Note: The TYPE definition with the first go-delimited batch persists even after the SQL server is restarted. After the first go delimiter, the declared table variable persists only until the next go is reached and the batch ends.

Performance comparison between Disk-Based and SQL Server Memory-optimized table variables

Let’s compare the performance of the table variable (disk-based) or the memory-optimized table variable. For this purpose, we will use the SQLQueryStress tool that can simulate multiple iterations and the number of threads.

Firstly, let’s run the following code 1,00,000 times with the table variable using the memory-optimized table types.

SET NoCount ON;  
DECLARE @SampleTbVariabledbo.SampleTbVariable  
;  
INSERT INTO@SampleTbVariable (ID,[Name]) values (1,'AB')  
;  
SELECT * from @SampleTbVariable;  

It took 40 seconds for 1,00,000 iterations with the memory-optimized table variables.

Let’s drop the table type and re-create it with disk-based table variables. The disk-based table variable uses the TempDB database.

DROP TYPE IF EXISTS dbo.SampleTbVariable;  
go
CREATE TYPEdbo.SampleTbVariable 
     AS TABLE  
     (  
    ID INT   NOTNULL INDEX id_ix1,   
     [Name] VARCHAR(10)
     )   ;  
go  

Now, run the following insert and select code for 1,00,000 iterations. It took 3.07 minutes for completion compared to 40 seconds for execution using memory-optimized table variables.

SET NoCountON;  
DECLARE @SampleTbVariabledbo.SampleTbVariable;  
INSERT INTO@SampleTbVariable (ID,[Name]) values (1,'AB');  
SELECT * from @SampleTbVariable;  
go