How to Identify and Fix Tempdb Contention

Tempdb is a system database used heavily in SQL Server for many different activities and operations. If you run a workload with high concurrency and heavy use of tempdb, then you might suffer from tempdb contention.



There are two types of tempdb contention: Object Allocation Contention and Metadata Contention. Instead of explaining these concepts myself, I am referring you to a bit old but great article from the SQL Server Tiger Team. It's called TEMPDB – Files and Trace Flags and Updates, Oh My!


Now that you understand these two types of contention, you might want to check if your SQL Server instance suffers from one of them or maybe from both. I wrote a script for you that you can use just for that. The script checks for any waiting tasks, which are waiting for latches on pages in tempdb. It distinguishes between the two types of contention and adds additional information - if it's object allocation contention, then it displays the allocation page type (PFS, GAM, or SGAM), and if it's metadata contention, then it displays the corresponding metadata system table.


The script is available for download in our Madeira Toolbox.


This script will only run on SQL Server 2019 or above, and on Azure SQL Managed Instance. It uses the new system function - sys.dm_db_page_info.


Notice that the script displays current waiting tasks. If you run it at a specific point in time when there is no or little contention, then you might get to the conclusion that you have nothing to worry about. But timing is important here, and it can be misleading. So either you run the script multiple times during heavy workload, or run from a scheduled job (e.g. once a minute), capture the results, and then analyze them.


If you identified that your instance suffers from object allocation contention, then there are plenty of things you can do, depending on your SQL Server version. Here is a great article that summarizes (almost) everything you need to know: Recommendations to Reduce Allocation Contention in SQL Server Tempdb Database.


I wrote "almost", because there is a new enhancement in SQL Server 2019 that is not covered in that article. The new enhancement is called Concurrent PFS Updates. It eliminates contention on PFS pages by allowing them to be updated under a shared latch rather than an exclusive latch. This behavior is on by default in all databases (not just tempdb) starting with SQL Server 2019.


Another similar improvement is expected in SQL Server 2022. In the new version of SQL Server, the same behavior of Concurrent PFS Updates will be used for GAM and SGAM pages as well.


If you identified that your instance suffers from metadata contention, then there is a great new feature in SQL Server 2019 called Memory-Optimized Tempdb Metadata. It converts tempdb system tables to memory-optimized tables and eliminates this type of contention altogether. It's not enabled by default, and it requires a restart of your SQL Server instance. Also, there are some limitations and things to consider. Read all about it here.

0 comments

Recent Posts

See All

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!