Post reply

how to reduce high CXPACKET

  • June 4, 2012 at 8:46 am

    #258967

    Hi guys ,

    I am dealing with high CxPACKET Issue in out production environment .

    We have OLTP env . DB size is approx 600GB .

    So far i have seen when ever UPDATE or INSERT happens the CXPACKET is goes high and that makes waiting Tasks very high.

    we have set 'Cost Theresold for parallelism' - 10

    and MXDOP - 0

    Any suggestions how to deal with it?

  • June 4, 2012 at 8:53 am

    #1496538

    First thing, resist the urge (and ignore any advice) that tells you to set maxdop to 1.

    Chapter 3: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2012 at 8:58 am

    #1496543

    Yes i have figured out by reading posts that by setting MXDOP to 1 won't really helps and creates other problem..

    Thanks for advise and i am sure for not to change it to 1.

    but if you can advise other steps would be fine.

  • June 4, 2012 at 9:06 am

    #1496552

    logicinside22 (6/4/2012)

    but if you can advise other steps would be fine.

    Did you read the chapter I recommended?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2012 at 1:35 pm

    #1496714

    CXPacket as you've already realized is merely parallelism waiting on certain threads. The CXPacket is rarely the cause of the issue, but a symptom. What you'll need to do is watch the spid/task and figure out what it's REALLY waiting on and get that cured. Look for the wait in sysprocesses that isn't CXPacket and troubleshoot from there.

    On rare occassions the parallelism itself is the problem, but usually only on short and small queries.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply