04 July,2012 by Jack Vamvas
Latches are synchronization objects for in-memory access to any portion of a log\data file.
Locks in SQL are different to latches . A latch deals with the multithreaded nature of SQL Server – for example, latches are used when a thread reads from disk to create a memory structure. Whereas a lock deals with the multi user aspect i.e isolating user activity as part of transaction management, and is usually maintained as part of any potential rollback situation.
There is a correlation between Locks and latches – and it’s normal for latches to increase as IO increases. At a certain point latches could begin to block as one IO makes a request which hinders the response of another IO. This creates latch waits.
More of this discussion in a future post. Diagnosing latch contention requires some in-depth analysis , these queries are a good starting point
For further reading - SQL Server SLEEPING MODE , locks and transactions
--View latch contention for patches and tress SELECT * FROM sys.dm_db_index_operational_stats (DB_ID('M_DB'), NULL, NULL, NULL) ORDER BY [page_latch_wait_in_ms] DESC, tree_page_latch_wait_in_ms DESC SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%' --view Patch Latch waits SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGELATCH%'