Wait Events - Sys.dm os latch stats

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Monitoring & Tuning - Wait Events

SQL Server 2005 exposes a wealth of "under-the-hood" information through dynamic management views and dynamic management functions, often abbreviated as DMV's and DMF's. DMV's and DMF's display a snapshot of the server's state so their output can vary widely from one execution to next. DMV's are particularly useful for monitoring and tuning memory and CPU pressures.

Sys.dm_os_latch_stats



This DMV returns additional statistics about latch waits organized by latch class. The following table documents the columns returned by this DMV:

Column Explanation
Latch_class Name of the latch class.
Waiting_requests_count Number of waits for this latch class.
Waiting_time_ms Total wait time for this latch class measured in milliseconds. Note that this column is updated every five minutes during a latch wait.
Max_wait_time_ms Maximum wait time any request had to wait for this latch class, measured in milliseconds.

For example, the following query returns latch wait statistics for buffer latches: 

SELECT * FROM sys.dm_os_latch_stats  WHERE latch_class = 'buffer'

Results: 

latch_class       waiting_requests_count  wait_time_ms         max_wait_time_ms 
----------------- ----------------------- -------------------- --------------------  
BUFFER            430                     36383                411

Much like sys.dm_os_wait_stats DMV, sys.dm_os_latch_stats is also aggregated since SQL Server instance was last started. You can re-set statistics by executing the following statement: 

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR)

With SQL Server 2000 you could obtain wait events information by executing DBCC SQLPERF statement as follows: 

DBCC SQLPERF('WAITSTATS')

Results (abbreviated): 

  
Wait Type              Requests          Wait Time           Signal Wait Time  
--------------------   ---------------   ----------------    ------------------  
MISCELLANEOUS          0.0                  0.0              0.0  
LCK_M_SCH_S            0.0                  0.0              0.0  
LCK_M_SCH_M            0.0                  0.0              0.0  
LCK_M_S                1.0               1873.0              0.0  
LCK_M_U                0.0                  0.0              0.0  
LCK_M_X                0.0                  0.0              0.0  
LCK_M_IS               0.0                  0.0              0.0  
LCK_M_IU               0.0                  0.0              0.0  
LCK_M_IX               0.0                  0.0              0.0

Although the same DBCC statement works with SQL Server 2005, you can retrieve additional information by querying DMV's.

Retrieved from "http://sqlserverpedia.com/wiki/Wait_Events_-_Sys.dm_os_latch_stats"