August 6, 2012 at 3:44 am
Hi All
After searching online for troubleshooting TempDb contention issues, I found the following script:
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:%'
Is PFS, GAM, or SGAM pages only found in TempDB
Also, will you only find Latch type Waits in TEMPDB?
Thanks
August 6, 2012 at 4:15 am
My somewhere limited Page Latch experience has always pointed to the IO sub system.
On one occasion, the cause were bad sectors, on other it was misconfiguration of the IO system.
Found this online:
'Diagnosing and Resolving Latch Contention on SQL Server'
http://www.microsoft.com/en-us/download/details.aspx?id=26665
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
August 6, 2012 at 6:04 am
SQLSACT (8/6/2012)
Is PFS, GAM, or SGAM pages only found in TempDB
No. All databases have PFS, GAM, or SGAM pages.
Also, will you only find Latch type Waits in TEMPDB?
No. Latch type Wait can happen in other databases also.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply