High LATCH_EX wait

585 pts.
Tags:
SQL Server
SQL Server 2008
SQL Server Enterprise Edition
SQL Server performance
What is LATCH_EX wait? Our LATCH_EX wait is very high. What do I need to look at to fix this?

Software/Hardware used:
sql server 2008 enterprise x64

Answer Wiki

Thanks. We'll let you know when a new response is added.

LATCH_EX Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches.

According to a SQL Service Waits and Queues, Best Practice Whitepaper:

  • Latch_x waits can frequently be reduced by solving LOG and PAGELATCH_UP contention.
  • PAGELATCH_UP occurs when a task is waiting for a latch for a buffer that is not in an I/O request. Page latch Update is used only for allocation related pages, and contention on it is frequently a sign that more files are needed. With multiple files, allocations can be distributed across multiple files therefore reducing demand on the per-file data structures stored on these pages. The contention is not IO performance, but internal allocation contention to access the pages. Adding more spindles to a file or moving the file to a faster disk does not help, nor does adding more memory.
  • If there is no LOG or PAGELATCH_UP contention, the other option is to partition the table/index in question in order to create multiple caches (the caches are per-index).

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Subhendu Sen
    LightWeight Synchronization object / latch indicate an inefficient physical design of the database. I am not sure, but u may try to increase the number of files to match the number of CPU on Sql server as well as ensure the size of each file is uniform.
    28,600 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following