2

I have been looking at the following post and using the query to look at my top wait stats:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

My top wait stat is CXPACKET and according to the total wait time I have a number which converts to about 700 days. I know that the server has only been up 58 days so how can this be accurate?

Does CXPACKET total for each thread? In which case how do I know if this is a problem?

Improve this question
10
  • What is the MAXDOP setting on your server ?
    – Kin Shah
    Commented Oct 29, 2014 at 15:42
  • 2
    CXPACKET accumulates waits for every logical processor SQL Server uses in any parallel queries. So, you can't think about it as clock time. Also, high CXPACKET waits aren't necessarily a problem (something will always be your highest wait). Is the CXPACKET wait specifically contributing to a performance problem? How do you know this? Are there other telling waits (like latch waits) coming in second or third?
    – Aaron Bertrand
    Commented Oct 29, 2014 at 16:04
  • Tom CXPACKET wait are actually symptom that something is causing parallel threads which were initiated for a task to merge with delay. You should focus on wait just below the cxpacket wait because its highly likely that one below is causing cxpacket wait
    – Shanky
    Commented Oct 29, 2014 at 17:43
  • CXPACKET waits are measured when a thread inside the parallel plan completes and "waits" on the other threads (by the managing thread). This could be caused by many things that aren't related to parallelism. Blocking, statistics, non-uniform distribution among parallel threads, etc. @AaronBertrand already hit most of the notes. I would take a look at some of the parallel plans on the system to see if in fact it's a plan issue or an overall parallelism issue (for example a bad maxdop setting).
    – Sean Gallardy
    Commented Oct 29, 2014 at 19:49
  • @Shanky actually usually if a wait is highly correlated with a CXPACKET wait, it will be something along the magnitude of (CXPACKET wait)/DOP, because the CXPACKETs will accumulate waits at clock time * DOP but the waiting thread will only accumulate clock time. But that is really hard to correlate anyway unless there is only one process on the system (or you are tracking waits for a specific session, which you can do with XEvents but not through the generic wait stats DMV).
    – Aaron Bertrand
    Commented Oct 29, 2014 at 20:37

1 Answer 1

Reset to default
2

It's strange how many "problems" CXPACKET is causing :)

You can find my answer to similar question at this link: https://dba.stackexchange.com/a/141399/28611

But I will re-post my answer here as well

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple aspects of the CXPACKET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  • Do not set MAXDOP to 1, as this is never the solution

  • Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice,
    as it could be just the exception in the system that is normally
    working correctly

  • Check the indexes and statistics on tables used by the query and make sure they are up to date

  • Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

  • Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
    case than the MAXDOP value should be lowered to fit your hardware

  • Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
    the case, then parallelism is not the bottleneck. Troubleshoot those
    wait stats to find the root cause of the problem and solution

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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