Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Breaking Down TempDB Contention

Breaking Down TempDB Contention 

What is tempDB contention?
 
From the outside looking in, tempDB contention may look like any other blocking. There are two types of contention that tends to plague tempDB's, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.). For the purpose of this blog, I want to focus on latch contention on the allocation pages.
 
What are allocation pages?
 
Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types of allocation pages that can experience contention and bring a server to a slow crawl.
 
Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.
 
Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.
 
Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.
 
Finding Latch Contention on Allocation Pages
 
You can use the dynamic management view (DMV) sys.dm_os_waiting_tasks to find tasks that are waiting on a resource. Tasks waiting on PageIOLatch or PageLatch wait types are experiencing contention. The resource description points to the page that is experiencing contention, and you can easily parse the resource description to get the page number. Then it's just a math problem to determine if it is an allocation page.
 
The Resource Description (sample):
 
The resource description will be in the form of <database ID>:<file ID>:<page number>. The tempDB is always database ID of 2. A sample resource description may look like 2:3:18070499. We want to focus on the page ID of 18070499.
 
The formula for determining the page type is as follows:
 
GAM: (Page ID - 2) % 511232
SGAM: (Page ID - 3) % 511232
PFS: (Page ID - 1) % 8088
 
If one of these formulas equates to 0, then the contention is on the allocation pages.
 
The query
 
Yes, I love posting queries as you may have noticed. Here is my query to check for allocation page contention in tempDB.
 

Select session_id,

wait_type,

wait_duration_ms,

blocking_session_id,

resource_description,

      ResourceType = Case

When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'

            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'

            When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'

            Else 'Is Not PFS, GAM, or SGAM page'

            End

From sys.dm_os_waiting_tasks

Where wait_type Like 'PAGE%LATCH_%'

And resource_description Like '2:%'

Comments

Posted by Gethyn Ellis on 6 March 2010

Nice post, great query for finding contention in the allocation pages of tempdb. I guess the first place to start if you are getting TEMPDB contention like this is to configure tempdb using the best practice described above?

Posted by Robert Davis on 6 March 2010

Thanks Gethyn!! I absolutely agree!! If someone experiences this problem, I would definitely start at making sure tempDB is configured to BP.

Posted by Jason Brimhall on 10 March 2010

Thanks for a nice post on the topic.  I agree on the BP as well.  Following BP has helped in increasing performance for us - in regards to tempdb.

Posted by Robert Davis on 10 March 2010

Thanks Jason!!

Posted by GDI Lord on 10 March 2010

Hi Robert

I'm a DBA-by-Accident and I'm trying to create contention on my tempdb by [code]select * into tempdb.dbo.tbl_test

from myTableWith125KRecords[code]. Is creating tempdb contention under test conditions (a local SQL Server 2008 instance that I use for my program development) a relatively simple matter that you could post code for? If so, please post some code :-)

Thank you

Posted by GDI Lord on 10 March 2010

Grr! Where's the edit button for me to reformat my code?

Posted by Robert Davis on 11 March 2010

Hi GDI Lord.

Creating tempDB contention would generally require generating a lot of concurrent transactions. You're not going to do it with a single query or even a few queries. You'll also need a poorly set up tempDB.

Posted by Marco Kleinert on 9 June 2010

Great query to find the contention for page allocation in tempdb!

I've done some investigation and customization...

Select session_id,

wait_type,

wait_duration_ms,

blocking_session_id,

resource_Description,

Descr.*

From sys.dm_os_waiting_tasks as waits inner join sys.dm_os_buffer_Descriptors as Descr

on LEFT(waits.resource_description, Charindex(':', waits.resource_description,0)-1) = Descr.database_id

and SUBSTRING(waits.resource_description, Charindex(':', waits.resource_description)+1,Charindex(':', waits.resource_description,Charindex(':', resource_description)+1)- (Charindex(':', resource_description)+1)) = Descr.[file_id]

and Right(waits.resource_description, Len(waits.resource_description) - Charindex(':', waits.resource_description, 3)) = Descr.[page_id]

Where wait_type Like 'PAGE%LATCH_%'

Now you'll find some more useful informations.

Posted by Robert L Davis on 9 June 2010

Interesting addition Marco. Do you have a blog where you have posted this? If so, please feel free to include a link to it.

Posted by Bradley B on 19 July 2010

Great Article and excellent information Thanks for the query as well!

Posted by Robert L Davis on 20 July 2010

Thanks for the feedback btb1977!!

Posted by David Benoit on 14 February 2011

Thanks for the script Robert, very helpful. I altered this a little so that the charindex statement is dynamic in case you wanted to look at databases other than tempdb, i.e. databases with id's greater than single digits.

SELECT session_id,

wait_type,

wait_duration_ms,

blocking_session_id,

resource_description,

ResourceType = CASE

                WHEN Cast(RIGHT(resource_description, Len(resource_description) - Charindex(':', resource_description, LEN(resource_description)-CHARINDEX(':', REVERSE(resource_description), 1))) AS NVARCHAR) - 1 % 8088 = 0 THEN 'Is PFS Page'

                WHEN Cast(RIGHT(resource_description, Len(resource_description) - Charindex(':', resource_description, LEN(resource_description)-CHARINDEX(':', REVERSE(resource_description), 1))) AS NVARCHAR) - 2 % 511232 = 0 THEN 'Is GAM Page'

                WHEN Cast(RIGHT(resource_description, Len(resource_description) - Charindex(':', resource_description, LEN(resource_description)-CHARINDEX(':', REVERSE(resource_description), 1))) AS NVARCHAR) - 3 % 511232 = 0 THEN 'Is SGAM Page'

                ELSE 'Is Not PFS, GAM, or SGAM page'

              END

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%'

AND resource_description LIKE '2:%'

One note, the other script provided by Marco is nice in that it pulls description, but it is also pretty heavy for those that have a lot pages in the buffer pool.

Thanks again for sharing.

Posted by stanleyjohns on 19 July 2011

@David. You need to modify the last line 'AND resource_description LIKE '2:%', as this will filter the results for only tempdb. Nice post by Robert Davis. I look forward to using the script.

Posted by linkbuilding123 on 26 July 2011

I’m really impressed together with your article, such great information you pointed out here, interesting discussing and waiting to visit your future posts.

Posted by New2SQL-343122 on 21 November 2012

Possible to modify the query for SQL 2K compatible ?

Posted by csaharoy on 30 September 2013

Thanks for the great post!!! as you mentioned 1 PFS page can track 1/2 GB(i.e. 512MB ==> 65536 pages) of data file then the next PFS page should apper after 65536 pages rather than 8088 pages. Can you throw some light on it??

Posted by Luke Keenan on 8 July 2014

the check for PFS etc is incorrect ref: www.sqlsoldier.com/.../breakingdowntempdbcontentionpart2

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;

Posted by karthik babu on 21 July 2014

So what will be the next step after finding the reason... tune the query or alter the settings of tempdb...??

Leave a Comment

Please register or log in to leave a comment.