How to Identify Page Latch contention

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