DaveWentzel.com            All Things Data

Types of Latches and Their Meanings

 

Latches are short-term synchronization objects.  They have become ubiquitous and are a common source of blocking, yet difficult to resolve since documentation on them is scarce. 
 
Types:
  • PAGEIOLATCH:  usually indicates that the IO subsystem cannot keep up with the IO load.  Typically associated with disk to memory transfers.  sys.dm_io_pending_io_requests can be used to determine where the hold up is.  If the IO subsystem is overloaded you will usually see many short waits.  Check your memory and buffer pool since PAGEIOLATCH can point to those problems as well.  Table scans can cause this too if you think about it. sys.dm_exec_query_stats and sys.dm_db_index_operational_stats can help with this. Another cause is high concurrent activity on certain pages (hotspots).   Try moving RAID 5 to 10 or looking at other SAN optimization opportunities. 
  • PAGELATCH_UP/PAGEIOLATCH_UP:  usually these point to issues with the PFS, GAM, and SGAM.  These pages are used for tracking the allocation status of pages with each file.  Contention can often be reduced by adding more files to the file group.  This is very much the case with tempdb. 
  • PAGELATCH_xx:  isn't related to physical IO.  Memory contention including the buffer pool. 
  • LATCH_xx:  isn't related to physical IO.  Contention for internal resources or structures other than the buffer pool.  Typical when accessing heaps and BLOBs. 
  • SIGNAL WAITS:  indicate excessive CPU pressure.  Fewer signal waits indicate the specific wait type resource is the bottleneck.  Understanding signal waits will help you to understand if the bottleneck is the actual thing we are waiting on or an overloaded processor. 
 
Latches have other usages too, separated into latch classes.  The full list of latch classes can be found in sys.dm_os_latch_stats which holds the statistical information as well. 
 
Some classes to note:
  • FCB(and others):  related to database file management.  File Control Block. 
  • VERSIONING:  related to row versioning. 
  • TRACE:  used with Profiler.  Contention can be reduced by limiting trace activity.
  • LOG_MANAGER:  contention is reduced by sizing the log file appropriately.  

sys.dm_db_index_operational_stats shows page_latch_wait_count and page_latch_wait_in_ms which can find indexes that are experiencing significant latch waits.  

Add new comment