Tuesday, January 22, 2013

Tempdb Latch Contention

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
(  SELECT 
          
session_id,
          
wait_type,
          
wait_duration_ms,
          
blocking_session_id,
          
resource_description,
          
CAST(RIGHT(resource_description, LEN(resource_description)
           -
CHARINDEX(':', resource_description, 3)) AS INT) AS PageID
      
FROM sys.dm_os_waiting_tasks
      
WHERE wait_type LIKE 'PAGE%LATCH_%'
        
AND resource_description LIKE '2:%') AS tab;

No comments: