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.