Sometimes SQL Server feels slow even when CPU and memory look perfectly fine. In many such cases, the hidden culprit is tempdb contention. Since tempdb is heavily used for internal operations, any bottleneck there can impact overall performance. The real challenge is identifying this contention quickly. In this part of the article series, we will use sp_WhoIsActive to detect tempdb related waits. Let's start.
Figure–1: Detecting the TempDB Contention
What is tempdb
tempdb is a system database of SQL Server. As the name suggested, it stores temporary objects like tables, variables, and intermediate result sets. It also supports internal operations like sorting, versioning for row modifications, and maintaining worktables for certain queries. Every time SQL Server instance is started, tempdb is recreated.
Why tempdb Contention
tempdb contention occurs when multiple sessions simultaneously try to access and use tempdb resources, leading to bottlenecks. Since tempdb is heavily used for temporary objects, sorting, hashing, and version store operations, high concurrency can quickly create pressure. A common cause is contention on allocation pages like PFS, GAM, and SGAM, resulting in waits such as PAGELATCH_UP and PAGELATCH_EX. Insufficient tempdb data files and poorly optimized queries can further worsen the situation. As a result, even if CPU and memory look normal, overall system performance can degrade significantly.
Common contentions
- Object Allocation Contention – This is contention for metadata pages those manage space allocation.
- Metadata Contention – This is contention for metadata pages tracking the creation and destruction of temporary tables and deallocating them.
- Temp Table Cache Contention – Temporary table cache helps metadata and object allocation for reusing of temp tables.
Simulating tempdb Contention
Execute Query Snippet-1 to monitor current utilization of your tempdb (Figure-2).
use tempdb
SELECT
-- Determining the amount of free space in tempdb
SUM(unallocated_extent_page_count) AS [Free Pages]
,(SUM(unallocated_extent_page_count)*1.0/128) AS [Free Space (MB)]
-- Determining the amount of space used by the version store
, SUM(version_store_reserved_page_count) AS [Version Store Pages Used]
,(SUM(version_store_reserved_page_count)*1.0/128) AS [Version Store Space (MB)]
-- Determining the amount of space used by internal objects
, SUM(internal_object_reserved_page_count) AS [Internal Object Pages used]
,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Internal Object Space (MB)]
-- Determining the amount of space used by user objects
, SUM(user_object_reserved_page_count) AS [User Object Pages Used]
,(SUM(user_object_reserved_page_count)*1.0/128) AS [User Object Space (MB)]
FROM tempdb.sys.dm_db_file_space_usage;
-- Query Snippet-1
Figure–2: tempdb utilization before testing
Now create a stored procedure using Query Snippet-2.
CREATE OR ALTER Proc proc_TempDB_Test
As
CREATE TABLE #T (
Name NVARCHAR(500)
)
INSERT INTO #T
SELECT [FirstName] + ' ' + [MiddleName] FROM [Person].[Person]
-- Query Snippet-2
Connect the SQLQueryStress to your test database and put Query Snippet-3. It will generate artificial load (Figure-3).
DECLARE @i INT;
SET @i = 1;
WHILE @i <= 100
BEGIN
exec proc_TempDB_Test;
SET @i = @i + 1;
END
-- Query Snippet-3
Figure–3: Database in stress using SQLQueryStress
Run Query Snippet-1 again. You will see that free pages, spaces are reducing and user object pages and object spaces are increasing as shown below (Figure-4).
Figure–4: tempdb utilization after testing
Now run sp_WhoIsActive, you will see lots of PAGELATCH_EX wait types in tempdb as shown below (Figure-5).
Figure–5: Detecting tempdb contention using sp_WhoIsActive
Final Words
tempdb contention is often hidden, but its impact is very real. sp_WhoIsActive gives you the clarity to detect it early by exposing the sessions and waits that matter most. As a DBA, the goal is not just to react, but to diagnose accurately and act with confidence.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.