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%'
