Microsoft – SQLServer – Wait Types – Latch
Microsoft defines latches as:
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:
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.
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.
- Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads
- 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
- sys.dm_os_latch_stats (Transact-SQL)
- SQL Server Latching – How it works
- Most common latch classes and what they mean
- Maximizing SQL Server 2008 R2 Table scan speed from DSI Solid State Storage
- 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”