The tempdb’s use as a temporary area space to create and delete temporary objects. The heavy usage of tempdb can lead to latch contention on the pages required to allocate or deallocate. In this blog, we’ll be covering what is tempdb contention and how quickly you can check the tempdb contention issue on your server.

What is tempDB contention?

When a good number of concurrent transactions are creating and dropping temporary objects at a very high rate, SQL Server has to allocate and deallocate data pages for those transactions in the tempdb database. Allocating and deallocating data pages on a high frequency can cause latch contention on the allocation bitmap pages – PFS, GAM, and SGAM.

You might be wondering these pages exist inside of every database but why only tempdb database typically encounters high latch contention issue – It is because of its one of the unique characteristics. It is a shared database at an instance level. So, everyone is using an instance share the same tempdb that makes it a hotspot.

Follow the below graphical presentation to understand how the tempdb contention looks like;

Allocation bitmap pages details;

• PFS – Page Free Space (PFS) tracks allocation and free space information about pages in a database. A PFS interval is 8088 pages or about 64MB. So, If you see “2:1:1” resource contention anywhere, it’s referring to the first page of the first data file in tempdb (DB ID -2). After the first page, it occurs every 8,088 pages.

• GAM – Global Allocation Map (GAM) pages record what extents have been allocated. Each GAM covers 64,000 extents or almost 4 GB of data. However, the first GAM page in a data file is always page number 2, so “2:1:2” would refer to the first GAM page in tempdb. After the second page, it occurs every 511,232 pages.

• SGAM –Shared Global Allocation Map(SGAM) pages tracks which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents or almost 4 GB of data. The first SGAM page in a data file is page 3, so “2:1:3” is tempdb’s first SGAM page. After the third page, it occurs every 511,232 + 1 pages.

Check the Tempdb Contention of your server

The quick way to check tempdb allocation contention is to query sys.dm_os_waiting_tasks. You can use resource_description column to identify resource contention on which dbid, fileid, and pageid.

SELECT t.resource_description,COUNT(*) AS [rowcount]
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.[dm_exec_sessions] AS es 
ON t.session_id = es.session_id
WHERE es.[is_user_process] = 1 
GROUP BY t.resource_description;

For In-deep details on the allocation bitmap, you can refer the Paul’s blog – Inside the Storage Engine