Breaking Down TempDB Contention (part 2)
I wrote a somewhat popular script and blog post a while back called Breaking Down TempDB Contention. This post explains how to identify tempdb contention. This past Friday, Jonathan Kehayias (blog|@SQLPoolboy) contacted me about the script. Jonathan said that he thought the math was off just a little bit in the script. The original script has the PFS pages repeating every 8088 pages and the GAM and SGAM pages repeating every 511232 pages. That is almost right.
The first PFS page is page 1 (which is actually the second page in the file as the header page is page 0). The 2nd PFS page is 8088 exactly, not 8088 pages later. After the first page, it does repeat every 8088 pages.
Likewise, the first GAM page is page 2, the second GAM page is page 511232 and then repeats every 511232 pages. The first SGAM is page 3, the second SGAM page is 511233 and then repeats every 511232 pages.
The old incorrect formula for determing which type of page on which the contention is occurring was:
GAM: (Page ID – 2) % 511232
SGAM: (Page ID – 3) % 511232
PFS: (Page ID – 1) % 8088
The new and improved forumala is:
GAM: Page ID = 2 or Page ID % 511232
SGAM: Page ID = 3 or (Page ID – 1) % 511232
PFS: Page ID = 1 or Page ID % 8088
Proving the New Formula
I wanted you to be able to prove for yourself that the new forula was correct. I threw together a script to check the page types of the suspected allocation pages. The first thing i want to do is grow the tempdb data file to a size large enough that I can check multiple instances of the allocation files. The GAM and SGAM pages reoccur every 4 GB, so this script will work just fine with a smaller amount than I’ve chosen. I grow the tempdb data file to 20 GB.
-- Resize tempdb main data file to 20 GB Declare @FileSize int; Select @FileSize = size * 8 / 1024 From tempdb.sys.database_files Where name = N'tempdev'; If @FileSize < 2048 Begin Alter Database tempdb Modify File ( Name = N'tempdev', Size = 20480MB) End Go
Now that the tempdb is large enough, I can select some choice pages. We know for a fact that page 1 is PFS , page 2 is GAM, and page 3 is SGAM. If we look at the page with DBCC PAGE, we will see a page type of 11, 8, and 9 respectively. We also check several other pages to see if they return the page types we expect.
-- Check page type of Page type of -- page IDs of 1, 8088, 16176 Declare @DBCCPage Table ( ParentObject nvarchar(255), Object nvarchar(255), Field nvarchar(255), Value nvarchar(255)) -- Read pages 1, 8088, 16176 Insert Into @DBCCPage Exec sp_executesql N'DBCC traceon (3604); DBCC Page(tempdb, 1, 1, 1) With TableResults; DBCC Page(tempdb, 1, 2, 1) With TableResults; DBCC Page(tempdb, 1, 3, 1) With TableResults; DBCC Page(tempdb, 1, 8088, 1) With TableResults; DBCC Page(tempdb, 1, 16176, 1) With TableResults; DBCC Page(tempdb, 1, 511232, 1) With TableResults; DBCC Page(tempdb, 1, 511233, 1) With TableResults; DBCC Page(tempdb, 1, 1022464, 1) With TableResults; DBCC Page(tempdb, 1, 1022465, 1) With TableResults;'; -- Parse page numbers and types (11 = PFS page Select m_pageId, m_type From (Select Object, Field, Value From @DBCCPage Where Field In ('m_type', 'm_pageId')) As Pvt Pivot (Min(Value) For Field In ([m_type], [m_pageId])) As Pvt2;
Jonathan also sent a version of the previous script that he thought would fit the bill. I changed it up a little bit to use a CTE simply because I think it is easier to read and understand with a CTE than with a derived table.
With Tasks As (Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, PageID = Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) From sys.dm_os_waiting_tasks Where wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%') Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, ResourceType = Case When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page' When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page' When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page' Else 'Is Not PFS, GAM, or SGAM page' End From Tasks;