I have used Extented event to monitor the occurances of TempDB contention on Production server . I found there are several entried logged in in 30 mints .

Now I am trying to determin if Tempdb contention on PFS, GAM or SGAM page then I will decide if I need to increase the number of TempDB data files on Production server . Currently , There are 8 TempDB Data files configured on its separate Disks .

There are Page_IDs  I found in the extented events for Tempdb files -

Page_ID  =1 for PFS page

Page_ID = 2 for GAM page

Page_ID =3 for SGAM page

but I found the Below Page_IDs and I know there is a formula that you can use to identify if page is PFS,GAM or SGAM ?Could someone please give me the right formula and will explain me how should I use this formula and what should I look for to determin if page is PFS,GAM or SGAM ? Is there any threshold value for the duration of TempDB contention occured ?  Your help will be apprecited .

Page_ID

16176 , 24264 64704 80880 121320 145584 299256 323520 444840 etc .

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:%'   

Daizy,

You can easily identify contention in Tempdb by running below command

select session_id, wait_duration_ms,   resource_description
from    sys.dm_os_waiting_tasks
where   wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'

Now I can understand that you also want information about page numbers for that Paul Randal has written article

Just read Anatomy of page completely and you would find what page number corresponds to what page.

I guess this is what you were asking do let me know if you require any further information

Hi,

Please refer following article by Jonathan Kehayias.

Optimizing tempdb configuration with SQL Server 2012 Extended Events

Thanks all for your help. I was able to identify that there is table valued function causing tempdb contention , but this function does not cause contention on tempdb page .Most of the time it execute without causing contention ,but if there is heavy load and concurrent session causing contention on tempeh page .in this case , if I run a script manually to check the tempdb contention on what kind of page then I could not able to capture the tempdb contention info because it is hard to know when it occurred so I setup the extended even using Jonathan 's article and I was able to find the tempdb contention page . If content for page ID -1 then PFS , 2 then gAM page , but I have for example xxxxxx page ID and Ido not want to use dbcc page command then what formula should I use to determine if the contention is on PFS , GAM , SGAm page that help me to make decision if add more data files helps with tempdb contention . One more question is it safe to turn on trace to view dbcc page info on production server ? Most of the contention duration below 4000 and a few contention duration greater than 10000 . Is there any reasonable duration value that we can consider as normal for contention ? Thanks for your help in advance.

 If content for page ID -1 then PFS , 2 then gAM page , but I have for example xxxxxx page ID and Ido not want to use dbcc page command then what formula should I use to determine if the contention is on PFS , GAM , SGAm page that help me to make decision if add more data files helps with tempdb contention . One more question is it safe to turn on trace to view dbcc page info on production server ? Most of the contention duration below 4000 and a few contention duration greater than 10000 . Is there any reasonable duration value that we can consider as normal for contention ? Thanks for your help in advance.

IAM,PFS and GAM pages are system pages and when you get page number like xxxx its page storing user data you dont get contention on user page it was there because it was created and then deleted.

See contention is different thing and it only occurs on system pages because when new page is going to be allocated it would look for these system pages to find which extent has new pages and when allocated would update these system pages if apart from system pages you are seeing any page its normal data or index page created and removed in Tempdb