Flowchart to guide you resolve TempDB Contention

The moment you see contention(PAGELATCH) on TempDB, don’t end up assuming you need to add more datafiles. It could be metadata contention too where you need to work on caching temporary objects and avoid dropping temporary objects or enable in-memory tempdb metadata feature in SQL 2022.

To help guide you through these scenarios, I’ve drawn a flowchart to walk you through the steps to address TempDB Contention on a SQL server. I suggest having this flowchart handy.

Run this SQL query to check for TempDB Contention.

After you run the query, now go through the flowchart.

I’ve drawn this exactly how my mind processes when I see pagelatches. This will help you decide what type of TempDB contention you’re seeing and how to get to the resolution.
Ignore my drawing ability, just try to get the idea. :D

• Flowchart Overview

  • Green Box (Allocation Contention): This type of contention has been resolved by default in SQL Server 2022.

  • Below the Green Box (Metadata Contention): Even in SQL Server 2022, metadata contention requires manual intervention.

Here are links for each of those items for you to address-

• When to add more datafiles

• Cache #temp

• Don’t drop #temp

• In-memory tempdb metadata feature

• Metadata Contention

• What are PFS, GAM and SGAM pages

I really hope this guide and flowchart helps you.

Resources:

I’ll quote the resource as myself ..haha.. since I came up with the idea of drawing a flowchart to address/resolve TempDB contention and haven’t seen in the internet yet.

Thanks for reading,
Haripriya.