Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to Identify Page Latch contention

04 July,2012 by Tom Collins

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
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 
--view Patch Latch waits
SELECT * FROM sys.dm_os_wait_stats 


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to Identify Page Latch contention | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer