One of the most important features in SQL Server 2019 is Memory-Optimized TempDB Metadata. This is a server feature, by turning it on, you can significantly improve tempdb performance by eliminating page latch contention for tempdb-heavy workloads. You can learn about it, and see the feature in action in two excellent videos from the SQL Server 2019 Learning Series: https://www.youtube.com/watch?v=LQejtjKERWM and https://www.youtube.com/watch?v=g4aemv5O9as. In this post, I am gonna write about the feature’s limitations.
When we are about to enable a new feature, one of the first things we have to check is whether enabling this feature will break the existing code. Enabling this feature could bring two breaking changes: one is related to columnstore indexes, the other to transactions with memory-optimized tables. Before we start to create and invoke database objects, we have to ensure that the feature is turned on. This query should return 1.
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
If this is not the case, you need to execute the following statement
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
and restart the server.
Enabling and disabling feature that needs a server restart must be considered carefully. Restarting the server, especially manually forced due issues caused by the feature, for some companies can be very painful. Therefore, it is important to consider limitations associated with the feature. Let’s now create a sample memory-optimized table and stored procedure:
USE WideWorldImporters;
DROP TABLE IF EXISTS dbo.Hek;
CREATE TABLE dbo.Hek
(
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
CREATE OR ALTER PROCEDURE dbo.Ins
AS
BEGIN
CREATE TABLE #x (id INT, c1 NVARCHAR(50));
INSERT INTO #x SELECT object_id, LEFT(name, 50) FROM sys.tables;
CREATE COLUMNSTORE INDEX ix1 ON #x(c1);
INSERT INTO dbo.Hek SELECT id, c1 FROM #x;
END
GO
When you invoke this procedure (EXEC dbo.Ins), you’ll get the following error message:
Msg 11442, Level 16, State 1, Procedure dbo.Ins, Line 7 [Batch Start Line 24]
Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled.
So, before you enable this feature, you need to ensure that your code does not create columnstore indexes in temporary tables. This does not limit only you to create columnstore indexes in tempdb, but also database engine itself. All internal implementations and optimizations that rely on temporary columnstore indexes won’t work. I do not know such implementations except the sp_estimate_data_compression_savings system stored procedure, which also won’t work if MEMORY_OPTIMIZED TEMPDB_METADATA is enabled.
The second limitation is related to transactions with memory-optimized tables. When I was checking SQL documentation and found out for the first time this sentence “A single transaction may not access memory-optimized tables in more than one database ” I was disappointed. I though that you have to choose between MO objects in your database and those from tempdb. Fortunately, I was wrong; the limitation is minor. Let’s consider the following procedure:
CREATE OR ALTER PROCEDURE dbo.Ins2
AS
BEGIN
BEGIN TRANSACTION
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.Hek SELECT id, c1 FROM #x;
COMMIT
END
GO
When you invoke this procedure, it will work. It creates and manipulates with temporary tables and inserts rows into a memory-optimized table, and all this within an explicit transaction. All fine – no limitations.
What you cannot do in such transactions is to query system views in tempdb. Querying tempdb views would mean accessing MO objects in tempdb, and we already access MO in the user database. Therefore, the following code won’t work:
CREATE OR ALTER PROCEDURE dbo.Ins2
AS
BEGIN
BEGIN TRANSACTION
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.Hek SELECT id, c1 FROM #x;
COMMIT
END
GO
When you invoke this procedure, you’ll get the following error message:
Msg 41317, Level 16, State 0, Procedure dbo.Ins2, Line 8 [Batch Start Line 35]
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.
OK, it is a limitation, but it is not usual to query tempdb sys views in the user code. The previous code is more common case, and it works, which is very important.
Thanks for reading!