SQL Server – Waits – LATCH_EX

Introduction

We noticed a SQL Server Instance that is a bit slow in responding to Client requests.  We decided to check the Wait Stats.  And, noticed that one of biggest obstacle is LATCH_EX waits.

 

Causes

  • SQL Server Profiler
  • Lumigent Server Components

Causes – Database Activity Monitoring

An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance
http://support.microsoft.com/kb/929728/en-us
When you run SQL Profiler against an instance of SQL Server 2000 or of SQL Server 2005, each active SPID of the instance requires an exclusive latch (LATCH_EX) to write the event to SQL Profiler. The SPID writes the event to the local memory buffer first. Then, the event is transferred to SQL Profiler. If the local memory buffer is full, the SPID discards the event. After the events in the local memory buffer are transferred to SQL Profiler, the SPID can write new events to the local memory buffer. If other SPIDs are waiting to write events to SQL Profiler, these SPIDs wait in a queue.

Because SQL Profiler is a process that is separate from the SQL Server process, transferring each event to SQL Profiler takes some time. If you run SQL Profiler on a different computer, this process takes longer. This process takes longer because of network latency and because of the performance of the computer that is running SQL Profiler. Therefore, the instance of SQL Server performs slowly.

Wait Types

Query – Correlate Wait Types

select 
	  lastwaittype
	, count(*) as '#ofOccurrences'
	, sum(physical_io) as physicalIO
	, sum(cpu) as cpu
	, sum(memusage) as memusage
from   master.dbo.sysprocesses

group by 
	  lastwaittype

order by
	  count(*) desc

And, you might see something that looks like this:

AggregateWaitTypesOrderbyNumberofOccurrences

Explanation:

  • waittype => Miscellaneous has high number of occurrences
  • waittype => NetworkIO has high physical_io and cpu
  • waittype => LATCH_EX has high cpu; as we have a LATCH_EX bottleneck
  • waittype => SLEEP has high cpu; as many processes are waiting
  • waittype  => WRITELOG has high number of occurrences and Physical IO & CPU

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s