Queries for latches

 To look at the system in real-time run the following query on a system to join the sys.dm_os_wait_stats, sys.dm_exec_sessions and sys.dm_exec_requests DMVs. The results can be used to determine the current wait type for sessions executing on the server. 

SELECT wt.session_id, wt.wait_type 

, er.last_wait_type AS last_wait_type 

, wt.wait_duration_ms 

, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description 

FROM sys.dm_os_waiting_tasks wt 

JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id 

JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id 

WHERE es.is_user_process = 1 

AND wt.wait_type <> 'SLEEP_TASK' 

ORDER BY wt.wait_duration_ms desc 

 

The following query will return information for all non-buffer latches: 

select * from sys.dm_os_latch_stats where latch_class <> 'BUFFER' order by wait_time_ms desc; 

 

 

The following script uses the resource_description column to isolate which index was causing the PAGELATCH_EX contention: 

 

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms            

, s.name AS schema_name            

, o.name AS object_name            

, i.name AS index_name            

FROM sys.dm_os_buffer_descriptors bd  

JOIN (            

  SELECT * 

    --resource_description           

  , CHARINDEX(':', resource_description) AS file_index             

  , CHARINDEX(':', resource_description, CHARINDEX(':', resource_description)+1) AS page_index   

  , resource_description AS rd            

  FROM sys.dm_os_waiting_tasks wt            

  WHERE wait_type LIKE 'PAGELATCH%'                       

  ) AS wt            

    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)            

    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index+1, 1) --wt.page_index)            

    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index+1, LEN(wt.rd)) 

JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id 

JOIN sys.partitions p ON au.container_id = p.partition_id 

JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id 

JOIN sys.objects o ON i.object_id = o.object_id  

JOIN sys.schemas s ON o.schema_id = s.schema_id 

order by wt.wait_duration_ms desc; 

 

 

The following sample script will query sys.dm_os_waiting_tasks and return latch waits ordered by wait duration: 

-- WAITING TASKS ordered by wait_duration_ms 

SELECT wt.session_id, wt.wait_type 

, er.last_wait_type AS last_wait_type 

, wt.wait_duration_ms 

, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description 

FROM sys.dm_os_waiting_tasks wt 

JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id 

JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id 

WHERE es.is_user_process = 1 

AND wt.wait_type <> 'SLEEP_TASK' 

ORDER BY wt.wait_duration_ms desc; 

 

 

The following script queries buffer descriptors to determine which objects are associated with the longest latch wait times. 

 

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] like '#WaitResources%') DROP TABLE #WaitResources; 

CREATE TABLE #WaitResources (session_id INT, wait_type NVARCHAR(1000), wait_duration_ms INT, 

                             resource_description sysname NULL, db_name NVARCHAR(1000), schema_name NVARCHAR(1000), 

                             object_name NVARCHAR(1000), index_name NVARCHAR(1000)); 

GO 

declare @WaitDelay varchar(16), @Counter INT, @MaxCount INT, @Counter2 INT 

SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'-- 600x.1=60 seconds 

 

SET NOCOUNT ON; 

WHILE @Counter < @MaxCount 

BEGIN 

   INSERT INTO #WaitResources(session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name) 

   SELECT   wt.session_id, 

            wt.wait_type, 

            wt.wait_duration_ms, 

            wt.resource_description 

      FROM sys.dm_os_waiting_tasks wt 

      WHERE wt.wait_type LIKE 'PAGELATCH%' AND wt.session_id <> @@SPID 

--select * from sys.dm_os_buffer_descriptors 

   SET @Counter = @Counter + 1; 

   WAITFOR DELAY @WaitDelay; 

END; 

 

--select * from #WaitResources; 

 

   update #WaitResources  

      set db_name = DB_NAME(bd.database_id), 

         schema_name = s.name, 

         object_name = o.name, 

         index_name = i.name 

            FROM #WaitResources wt 

      JOIN sys.dm_os_buffer_descriptors bd 

         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description)) 

            AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) +1 ) - CHARINDEX(':', wt.resource_description) - 1) 

            AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) +1 ) + 1, LEN(wt.resource_description) + 1) 

            --AND wt.file_index > 0 AND wt.page_index > 0 

      JOIN sys.allocation_units au ON bd.allocation_unit_id = AU.allocation_unit_id 

      JOIN sys.partitions p ON au.container_id = p.partition_id 

      JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id 

      JOIN sys.objects o ON i.object_id = o.object_id 

      JOIN sys.schemas s ON o.schema_id = s.schema_id; 

 

select * from #WaitResources order by wait_duration_ms desc; 

GO 

 

• Measure average page latch wait time with the Performance Monitor counter MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time or by running the sys.dm_os_wait_stats DMV.