SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – LATCH_EX AND how to reduce it

13 January,2013 by Jack Vamvas

SQL Server Books online defines the SQL wait type LATCH_EX as “Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.”

 Note:

1)The definition mentions “does not include buffer latches or transaction mark latches”.  This suggests this wait type is not related to IO or data.

How to reduce it

1) Investigate the DMV sys.dm_os_latch_stats  for latch contention source. Most sources are esoteric and there is very little in-depth information available on most latch classes. A DBA with deep knowledge of SQL Server Internals can interpret the results .

2) Investigate for administration procedures which may cause an underlying contention. Latches are internal SQL Server and there is very little impact by the DBA. Latches are short lived and it’s difficult to find the correlation between processes and latch contention

One method of investigating is to monitor the sys.dm_exec_requests  DMV while running a DBA procedure you think may cause latch contention. Monitor the wait_type column

 Read More

Timeout occurred while waiting for latch - SQL Server DBA

SQL Server – Pageiolatch_sh and how to reduce it - SQL Server DBA

Identify Page Latch contention - SQL Server DBA

 


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