Memory-Optimized TempDB Metadata

Since memory (RAM) is already for some years relatively cheap, in-memory data is one of the greatest ways to improve performance in computing today. First support for in-memory data in MS SQL Server was introduced under the code name “Hekaton” and it was presented in MS SQL Server 2014.

In-memory data is cool. Some of the main benefits are:

  • Stored procedures can be compiled to native code and run much faster
  • If you don’t need to persist the data, you can have RAM tables only
  • If enabled, the data is persisted on disk for durability - data is read from disk during database recovery
  • You can greatly improve the performance of many application systems
  • It stores data differently - the entire tables reside in memory, rows in the table are read and written from/to memory
  • Eliminates waits (latch)

Soon I will write a detailed article about the In-memory data, for now I’ll just stop here.

Temporary database (TempDB) is one of the biggest sources of latency in SQL Server. Requests for temporary data structures and maintaining its metadata is one of the most significant bottlenecks in SQL Server. Memory-Optimized TempDB solve this issue by writing TempDB metadata into memory. As a result, it greatly improves the performance of any workload that heavily use temporary data structures.

Memory-Optimized TempDB Metadata:

  • Key tempdb system tables become SCHEMA_ONLY in-memory optimized tables
  • Just metadata is written in the memory - NOT user data!
  • It is a server feature – you must RESTART the server (SQL service)!
  • The result is latch and lock free

This is the way how you can turn on and off this feature:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Don’t forget that the feature will be applied only after you restart the SQL server (service).

You can check if the feature is turned on/off if you use this command:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

As a result you will get the number 0/1 if the feature is turned off/on.

Off course take care that there are certain limitations for this feature. So, before you turn it on be sure to know your data and database structure well.

Limitations for Memory-Optimized TempDB Metadata:

  • columnstore indexes – You cannot create columnstore indexes on temporary tables when Memory-Optimized TempDB Metadata is enabled
  • sp_estimate_data_compression_savings - sp_estimate_data_compression_savings does not work if you have a columnstore index in the table for which you want to calculate the storage savings. This is because the calculations are made in TempDB and as stated in the first limitation, you cannot create columnstore indexes on temporary tables
  • transactions with memory-optimized tables - transactions that involve a Memory-Optimized table will not be able to access TempDB system views

Ok, now when we know more about the theory, let’s see some examples.

Example 1

First test will consist of selecting some data from one table to a temporary table and after that check what objects (tables) are created in TempDB on a storage (disk).

SELECT * INTO #orders FROM WideWorldImporters.Sales.Orders;
SELECT * FROM tempdb.sys.tables;

When the feature is turned off in the output, we will see something like this:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysmultiobjrefs'. Scan count 4, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 39, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 5, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalnames'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalvalues'. Scan count 2, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

You can see that there is a total of 8 objects. Now, let’s turn the feature on, restart the server and repeat the same test. The output will be like this:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalnames'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalvalues'. Scan count 2, logical reads 4, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Now you can see that only 4 tables are used, there are significantly less objects (most of them are in-memory objects).

Example 2 - columnstore index on temporary table

Let's see the first limitation when the feature is turned on.

We will create a temporary table and try to create a columnstore index on that table.

CREATE TABLE #t (id INT, c1 NVARCHAR(50));
INSERT INTO #t SELECT object_id, LEFT(name, 50) FROM sys.tables;
CREATE COLUMNSTORE INDEX idx1 ON #t(c1);

And, as a result an exception is shown.

"Msg 11442, Level 16, State 1, Line 87
Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled."

You cannot create columnstore indexes on temporary tables when Memory-Optimized TempDB Metadata is enabled.

Example 3 - sp_estimate_data_compression_savings

The second limitation is implicated from the first limitation. We can't use the procedure sp_estimate_data_compression_savings if the feature is turned on and we have a columnstore index on that table.

CREATE TABLE [dbo].[TestCI](
	[TestColumn] [nvarchar](256) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredColumnStoreIndex] ON [dbo].[TestCI] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'TestCI', NULL, NULL, 'ROW' ;  
GO

The error message and the number of the error are the same as in the previous example.

"Msg 11442, Level 16, State 1, Line 96
Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled."

Example 4 - access TempDB system views from Memory-Optimized table

Let's try to access TempDB system views from Memory-Optimized table.

DROP TABLE IF EXISTS dbo.MemoryOptimizedTable;
CREATE TABLE dbo.MemoryOptimizedTable
(
	id int NOT NULL
	, name nvarchar(50) COLLATE Latin1_General_100_CI_AS NOT NULL
	, PRIMARY KEY NONCLUSTERED HASH (id) WITH (BUCKET_COUNT = 1024)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

IF EXISTS(SELECT 1 FROM tempdb.sys.tables WHERE name LIKE N'#x[_]%') DROP TABLE #x;
CREATE TABLE #x (id INT, c1 NVARCHAR(50));
INSERT INTO #x SELECT object_id, LEFT(name, 50) FROM sys.tables;
INSERT INTO dbo.MemoryOptimizedTable SELECT id, c1 FROM #x;

It failed because we try to access the tempdb.sys.tables and this is a system view.

"Msg 41317, Level 16, State 0, Line 116
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master."

Lucky for us, we can write this query in a different way:

IF OBJECT_ID('tempdb..#x') IS NOT NULL DROP TABLE #x;
CREATE TABLE #x (id INT, c1 NVARCHAR(50));
INSERT INTO #x SELECT object_id, LEFT(name, 50) FROM sys.tables;
INSERT INTO dbo.MemoryOptimizedTable SELECT id, c1 FROM #x;

And, this will work.

Example 5 - Stress test

We will create a simple procedure that creates a temporary table in a loop with 50 repetitions. We will run this procedure for 50 consecutive times and using 100 parallel threads. We are testing this with a small exe named Ostress.exe that is part of the “RML Utilities for SQL” that you can download here.

Test procedure:

CREATE OR ALTER PROCEDURE dbo.P
AS
DECLARE @cnt INT = 0;
WHILE @cnt < 50
BEGIN
	CREATE TABLE #T (id INT); INSERT INTO #T(id) VALUES(1); DROP TABLE #T;
	SET @cnt+=1
END

How we start the test (from command prompt):

Ostress.exe -E -dTestDb -Q"EXEC dbo.P" -MSSQLSERVER -r50 -n100 -q

While it is running, we will from time to time check for waits using this query:

SELECT 
	r.wait_time
	, r.wait_type
	, r.total_elapsed_time
	, r.cpu_time
	, st.text
	, c.client_net_address
	, c.num_reads
	, c.num_writes
FROM 
	sys.dm_exec_requests r 
	INNER JOIN sys.dm_exec_connections c ON (r.connection_id = c.connection_id) 
	OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE 
	r.wait_type NOT IN ('SP_SERVER_DIAGNOSTICS_SLEEP','WAITFOR')
	AND r.database_id = DB_ID()
ORDER BY 
	r.wait_time DESC

Results:

a) Feature turned off:

Total time:

Waits:

b) Feature turned on: 

Total time:

Waits:

The test shows us great improvement in total execution time and when the feature was turned on it was almost impossible to catch any wait occurring on the system.

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (8.86 kb)