Breaking Down TempDB Contention (part 2)

Tempdb Contention via Idera Diagnostic Manager

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;

the Results

New Script

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;