Microsoft – SQLServer – Wait Types – Latch

Microsoft – SQLServer – Wait Types – Latch

Microsoft defines latches as:

http://msdn.microsoft.com/en-us/library/ms175066.aspx
A latch is a lightweight synchronization object that is used by various SQL Server components. A latch is primarily used to synchronize database pages.

Here are some latch wait types:

ACCESS_METHODS_DATASET_PARENT

  • http://henkvandervalk.com/maximizing-sql-server-2008-r2-table-scan-speed-from-dsi-solid-state-storage
    On the “ACCESS_METHODS_DATASET_PARENT”  latch wait type isn’t much information available, but think of this as a memory semaphore that is used to distribute the pages scanned from disk to the operators downstream in the query plan, synchronizing child dataset access to the parent dataset during parallel operations.
    From several experiments I have noticed that this type of  latch wait time doesn’t expose a direct relation to the overall duration of the query execution while table scanning a single table , so lets call it SQL internal housekeeping.

DBCC_MULTIOBJECT_SCANNER

  • http://support.microsoft.com/kb/919155
    This issue occurs because a time-out occurs when SQL Server traverses the Index Allocation Map (IAM) chains. The latch that is mentioned in the error message is used to prevent other threads from accessing a list. This list is being built by a thread that traverses the IAM chains for all indexes that are associated with a given table. If the table is large enough that traversing these IAM chains takes more than 5 minutes, you may experience the latch time-out. Additionally, this issue is typically worse when disk I/O is slow.

PAGELATCH_EX
PAGELATCH_UP

ACCESS_METHODS_DATASET_PARENT
ACCESS_METHODS_SCAN_RANGE_GENERATOR

  • We have a fairly expensively query and we added MAXDOP=1 HINT and this wait types went away.
  • Once we removed the MAXDOP=1 HINT this wait type re-asserted itself
  • During parallel operations, this latch is used to synchronize child dataset access

BACKUP_RESULT_SET
BACKUP_TAPE_POOL
BACKUP_LOG_REDO
BACKUP_INSTANCE_ID
BACKUP_MANAGER
BACKUP_MANAGER_DIFFERENTIAL
BACKUP_OPERATION
BACKUP_FILE_HANDLE

  • Backup

 

References:

  • Error message when you run the DBCC CHECKDB statement on a database that contains one or more very large tables in SQL Server 2005: “Timeout occurred while waiting for latch”
    http://support.microsoft.com/kb/919155

 

One thought on “Microsoft – SQLServer – Wait Types – Latch

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s