Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait events.

The system consists of 3 processing SQL Servers which do alot of number crunching and calculations and then feed the data into a central cluster server. The processing servers can have up to 6 jobs running each at any one time. These wait stats are for the central cluster which I think is causing a bottlneck. The central cluster server has 16 cores and 64GB RAM. MAXDOP is set to 0.

I guess the CXPACKET is from the multiple parallel queries running however I am not sure what the LATCH_EX wait event is indicating. From what I have read this could be a non-buffer wait?

Can anyone suggest what the cause of these kind of waits stats would be and what course of action I should be taking to investigate the root cause of this performance issue?

The top query results are the total wait stats and the bottom query result is the stats over the 1 hour period SQL Wait sample

share|improve this question
3  
Have you had a look at Paul Randal's blog on Latch waits? sqlskills.com/blogs/paul/… There's quite a bit of useful information in determining what the Latch Wait means by selecting from sys.dm_os_latch_stats –  Mark Sinkinson Mar 6 at 9:54
    
CXPacket are when the main thread of the query is waiting on the parallel threads to return. For a good explanation and some ways to reduce it see Brent Ozar's blog entry on the subject brentozar.com/archive/2013/08/… –  Wind Raven Mar 6 at 13:53
add comment

2 Answers 2

Read Diagnosing and Resolving Latch Contention on SQL Server, is the most comprehensive paper on the topic. You'll have to dig into sys.dm_os_latch_stats and see what latch type is the contention on.

See if reading How to analyse SQL Server performance helps you in any way.

share|improve this answer
add comment

In addition to reading the links provided above and most likely changing your "Max Degree of Parallelism" setting from 0 to something like 8, you'll want to narrow down which of your queries are going parallel and what their cost is.

After seeing the impact of this change you can also consider modifying your "Cost Threshold for Parallelism" to fine tune what will go parallel.

Here's a great video from Brent Ozar that will help you: Mastering the Art of CXPACKET and MAXDOP

Your goal is for <= 50% percent wait time for CXPACKET. Good Luck!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.