Metadata Contention on TempDB

TempDB contention is not fully resolved in SQL 2022

Sep 30, 2024

In SQL Server 2022, there is a huge misconception that TempDB contention is fully resolved. This is NOT true. Only one type of contention(allocation contention) is resolved but not the other type of contention(metadata contention) which needs to be manually addressed.

Allocation Contention on GAM and SGAM pages is the one we manually address in versions earlier than SQL 2022, by adding more number of TempDB datafiles, so there will be more sets of PFS, GAM, SGAM pages to help with allocations. Now, this contention is resolved by default in SQL 2022. (Contention on PFS page was addressed in SQL Server 2019)

But metadata contention still exists on TempDB and needs to be manually addressed even on SQL 2022 version.

In this article, we will see:
a. What metadata contention is
b. How to identify metadata contention
c. How to resolve metadata contention

What is Metadata Contention:

When you have DDL operations such as CREATE/DROP temp tables, these operations need to check and insert metadata into system metadata tables. To do so, these operations need to acquire a latch on system metadata tables to gain access and modify the page. A latch means a lightweight lock in a database system.
Under normal circumstances, you have a very few CREATE/DROP tables, but in case of TempDB, when there are high volume of CREATE/DROP operations on temporary objects, all these too many sessions are waiting to acquire a latch on these system metadata tables, thus causing contention. And this is called metadata contention.

If you look at this picture, see how all the deletes are trying to access sysobjvalues system table, causing contention on the table.

Image: https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my

There are actually 12 of these metadata tables which the DDL operations need to gain access to, just like the one above. Here is the list of 12 system tables where contention occurs due to DDL operations.

To summarize, we saw what metadata contention is and how it occurs. Let’s jump to the next topic on how to identify it.

How to identify metadata contention:

Run the following query on SSMS. This query gets information from sys.dm_exec_requests which displays only active requests, and this when used in conjunction with sys.dm_exec_sql_text displays the sql text that is running. sys.fn_PageResCracker and sys.dm_db_page_info are two functions introduced in SQL 2019 to provide detailed information about pages to troubleshoot page related waits.

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,
CASE 
WHEN page_info.page_type_desc IN('SGAM_PAGE','GAM_PAGE', 'PFS_PAGE') 
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'ALLOCATION CONTENTION'
WHEN page_info.page_type_desc IN ('DATA_PAGE', 'INDEX_PAGE') 
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'METADATA CONTENTION'
END AS allocation_type
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 /*database ID, file ID, page ID SQL2019 */
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info /*replace dbcc page SQL2019*/
WHERE er.wait_type like 'PAGELATCH%'
GO

When this query is run, you will find PAGELATCHes on system object pages as in the screenshot below.

First column: Wait type is Exclusive PAGELATCH.
Second column: Waiting on resource 2:9:8. Read DB id: Datafile id: Page number
2 is always TempDB database id. Datafile id is the file id it is waiting on, if there are 12 data files, this number could be anywhere from 1 to 12. In this case, it is the 9th datafile.
Third column: Object name is a system object page which is sysallocunits.

So, when you see wait_type PAGELATCH on TempDB database id 2 and on a system object table, then it is metadata contention.
Now that we know how to identify it, let’s take a look at how to resolve it.

How to Resolve Metadata Contention:

There are 3 ways to mitigate metadata contention.

Don’t drop temporary tables: Dropping temporary table is a DDL operation. Any DDL operation needs to acquire a latch on system object tables to check and insert metadata. When there are too many drop operations, then these DDL operations need to wait in line to acquire a latch on system object tables, causing contention. In order to prevent this, Microsoft has suggested to not drop temporary tables after the session is complete and instead leave it up to SQL Server to cleanup. What SQL Server does behind the scenes is, it uses the Optimistic Latching Algorithm to drop the tables asynchronously, where it ends up performing operations based on the number of concurrent CREATE/DROP operations, so as to not cause contention on TempDB.
Cache temporary objects: Caching temporary objects reduces a lot of PAGELATCHes. There are rules to cache temporary objects and to find out how and why caching is important, follow the link here I’ve written in detail about this
https://gohigh.substack.com/p/importance-of-caching-temporary-tables
In-memory tempdb metadata feature: This is a new feature introduced in SQL 2019 as part of in memory OLTP umbrella. When this feature is enabled, it moves all the heavily utilized system objects to memory, thus making them latch free and reduces contention. This feature needs to be manually turned ON and it is OFF by default. SQL services need to be restarted for it to take effect. Make sure you are on a minimum of CU2 on SQL 2019 feature, since only from SQL 2019 CU2 all 12 metadata tables are moved to memory.
```
/*ENABLE MEMORY-OPTIMIZED TEMPDB METADATA */
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;
```
Read here in detail to understand how this new feature works.
https://gohigh.substack.com/p/in-memory-tempdb-metadata-feature

To summarize, we have seen different ways to address and resolve metadata contention on TempDB.

Summary:

In SQL Server 2022, there is a huge misconception that TempDB contention is completely resolved. This is NOT true. Only allocation contention is resolved but not metadata contention, which needs to be manually addressed. We have seen how to identify and resolve metadata contention on TempDB. When you see PAGELATCHes on system object pages, then it is metadata contention. This can be mitigated by caching the temporary objects and not dropping the temporary objects and finally taking advantage of 'in-memory TempDB metadata feature' introduced in SQL 2019.

Resources:

TempDB: The Good, The Bad, and The Ugly by Pam Lahoud
https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my

Thanks for reading,
Haripriya

Go High

Discussion about this post