Read Session-Specific data from the memory-optimized temp tables

In the article Use Memory Optimization for Faster temp table performance, we explored how you can leverage memory-optimized tables to replace the SQL Temp tables for improved performance with faster execution, Zero logical reads.

The memory-optimized temp tables are stored only in memory and have no disk component. It does not involve IO activity. These tables are created at the deployment time since it takes advantage of compile-time optimizations.

The regular SQL Temp table is created in a specific session, and its existence is at the session or batch level. However, the memory-optimized temp tables do not restrict session-specific activity. Therefore, you might read data from those memory tables even if your session does not perform any inserts, updates, or deletes?

How do we convert memory-optimized temp tables into session-specific memory temp tables? Let’s check it out in this article.

Before going further, you should create the memory-optimized filegroup data file, as shown in the previous article

Previously, we created the memory-optimized temp table with the following query. It uses the DURABILITY as SCHEMA_ONLY option for persisting table schema even after restart.

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

For the session-specific table data, we can create a table-valued function that filters data on @@SPID. You can use this function in the SCHEMA_ONLY tables to convert them into session temporary tables.

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid;

Next, we drop the existing memory-optimized table and create it with an additional column [SpidFilter] and Constraint to filter the SPID for the specific session.

Drop Table dbo.[MemoryOptTempTable]  
Go
CREATE TABLE dbo.[MemoryOptTempTable]  
(  
    [ID] INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED ,
               [First_Name] nvarchar(10) NULL,
               [Last_Name] nvarchar(10) NULL,
    SpidFilter  SMALLINT NOT NULL DEFAULT (@@spid),  
               INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    CONSTRAINT CHK_soSessionC_SpidFilter  
    CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
       (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);  
go  

We also require a security policy with the following query that adds a filter predicate on the existing memory table.

CREATE SECURITY POLICY dbo.MemoryOptTempTable_SpidFilter_Policy 
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.MemoryOptTempTable
    WITH (STATE = ON);  
go  

Let’s test whether a user from a different session can read the data inserted or updated from a different session in the memory temp table.

Open two query windows in SSMS and run the queries. For my demo, the session ids are 63 and 52.

Session ID 63
Select @@SPID
Go
Insert into MemoryOptTempTable(first_name,last_name) values ('Rajendra','Gupta') 

Select @@SPID
Go
Insert into MemoryOptTempTable(first_name,last_name) values ('Test','Value')
Go

At this point, my memory-optimized table has two records inserted in the different sessions. Let’s select the value from each session and see what value it returns.

As shown below, the query returns values only inserted during the current session.

Note: The query filters the records as per SPID. Therefore, if you close the session id and SQL Server assign some other session the same SPID, it will also read the previous session values. Therefore, you should use delete statements(DELETE FROM MemoryOptTempTable) in sessions to clear the data in temp tables once your work is finished.