Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Identify Page Latch contention

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer