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 determining 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 formula 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

-- 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

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;

-- 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

m_pageId	m_type
(1:1)	11
(1:2)	8
(1:3)	9
(1:8088)	11
(1:16176)	11
(1:1022464)	8
(1:1022465)	9
(1:511232)	8
(1:511233)	9

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;

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;

^*Reposted with permission from SQLSoldier.com.