Memory-Optimized TempDB Metadata feature in SQL Server 2019

Memory optimized tempdb metadata was introduced in 2019 to solve the metadata contention problem we have in Tempdb.

Before going to learn about taking the advantage of this feature, lets learn what’s the metadata contention is.

Metadata contention

Metadata contention happens when there is a contention on the system pages which stores the data about the objects inside the tempdb. Each time you create, update or delete any object in tempdb, we need to update these system tables. As you know tempdb is being used by all the applications and databases in the server and there is only one tempdb per instance, metadata contention can occur when these system tables are updated while creating the temporary objects in tempdb.

Metadata contention was the issue since SQL Server 2000. In SQL Server 2005, they introduced temp cable caching to reduce the meta data contention. For the inserts, caching the metadata for the temp tables we have for the stored procedures helped reduce the metadata contention for many years. Regularly, the metadata of these cached objects needs to be removed from the cache. While the cleaning up process happens, we need to find and delete the related rows from our metadata tables. As more and more table metadata was added with the new versions of the SQL Server, the contention was observed while deletes happen from the cache. These cached objects needs to be deleted when ever there is a memory pressure or when the cached objects are invalidated due to altering the temp tables after they are created.

To solve this problem, three main changes were added before implementing the memory optimized tempdb metadata feature. I will be explaining briefly those changes here.

  1. Cleanup process has been changed from the synchronous to asynchronous. Cleanup process use to be in synchronous previously as the temp tables use be to be dropped when the stored procedures ends. With the asynchronous process, dropping doesn’t occur and treated as NO OPERATION no matter if the table is explicitly dropped by using the drop table syntax or if the cached object is invalidated by adding index after creating the temp table. These objects instead will be moved to the deleted list where a cleanup thread taken care by the cache clock hand sweep process will cleanup all these deleted list and the data related to the deleted list objects from the system tables as well.
  2. Only one helper thread to cleanup, one per NUMA node. previously, once the objects are sent to the deleted list were deleted by the multiple threads that comes to actually create the temp tables. Any threads that comes to create the temp tables has to first go and delete these cached objects from the deleted list, find and delete the metadata from system tables as well before moving ahead. This logic has changed later to only one thread dedicated to the cleanup process. One helper thread is used for one NUMA node.
  3. Latching algorithm has changed. The old algorithm used to delete the metadata rows from the system tables of tempdb when deleting the cached object from the cache which holds the exclusive lock on the pages which is known as PAGELATCH_EX. This exclusive lock will be hold for the entire process. Once the process is done cleaning up the meta data rows from system files, the exclusive lock is released. This has changed to the new latching algorithm where a shared lock is acquired until the cleanup process finds the metadata rows in the system tables. Once it find the rows to delete, then the exclusive lock is placed and the rows are deleted. If the rows are not found, then the shared lock will be released.

In SQL Server 2019, Microsoft introduced a new feature called “Memory-Optimized TempDB Metadata feature” which can store the Metadata of temp tables stored as non-durable memory optimized tables. Let’s see how the PAGELATCH_EX wait types look like in general. We are running the below stored procedure that we create without enabling the feature (default)

USE [AdventureWorks]
GO
alter PROC TEST
AS
BEGIN
CREATE TABLE #table1
(
  [departmentid] INT, 
  [name] INT);
INSERT INTO #table1
       SELECT TOP 1000 [DepartmentID], 
                     [Name]
       FROM [HumanResources].[Department] WITH(NOLOCK);
END

--use SQLQuerystress and run the below query. I used 50 iterations and 100 threads and see the results.
DECLARE @ID INT;
SET @ID = 1;
WHILE @ID <= 100
    BEGIN
        EXEC TEST;
        SET @ID = @ID + 1;
    END

Check for the pagelatch waits by running below query

--credits: Pam lahoud
source: https://github.com/microsoft/sql-server-samples/blob/master/samples/features/in-memory-database/memory-optimized-tempdb-metadata/MemoryOptimizedTempDBMetadata-TSQL.ipynb

use master
go
SELECT 
er.session_id, er.wait_type, er.wait_resource, 
OBJECT_NAME(page_info.[object_id],page_info.database_id) as [object_name],
er.blocking_session_id,er.command, 
    SUBSTRING(st.text, (er.statement_start_offset/2)+1,   
        ((CASE er.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE er.statement_end_offset  
         END - er.statement_start_offset)/2) + 1) AS statement_text,
page_info.database_id,page_info.[file_id], page_info.page_id, page_info.[object_id], 
page_info.index_id, page_info.page_type_desc
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st 
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r  
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
WHERE er.wait_type like '%page%'
GO

You will have to refresh couple of times to see the PAGELATCH_EX wait type. This is a metadata contention. We can know this as a metadata contention by looking at the objectname, sysschobjs is the system table name. To know how the feature works, we need to enable the feature and restart the database.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;
GO

Restart the SQL Server and check if the feature is enabled or not

--should give you the value of 1 if the feature is enabled
SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized; 
GO

Run the same query again using SQLQuerystress for 50 iterations and 100 threads

You will not see any waittypes this time

Summary:

If you have heavy workloads on your tempdb, it is totally worth trying this feature. Though there are some limitations to this feature and that is the reason this feature is not enabled by default. With this feature turned on, it will reduce the metadata contention as there will be no latching or locking of metadata pages

You need tp restart your SQL Server instance once you enable this feature. You cannot use columnstore indexes on #temp tables with this feature turned on.

Thanks for reading!

2 thoughts on “Memory-Optimized TempDB Metadata feature in SQL Server 2019

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s