Post reply

CXPACKET queries

  • December 18, 2012 at 6:59 am

    #393811

    Hi all

    Looking at my waits I can see lots of CXPACKETS, and I am wondering what the best way is to capture the queries that cause these waits so I can have a look at tuning them, or even looking at altering the cost threshold. At present I am just thinking about querying sys.dm_exec_requests and logging queries that have a wait type of CXPACKET.

    I have also read somewhere you can use the sys.dm_exec_cached_plans DMV to identify which queries are performing parrallel processing, but not sure how I can do this.

    What are peoples thoughts?

  • December 18, 2012 at 7:07 am

    #1569557

    Hi,

    I'd say getting the answer out of the plan cache was your best bet. There seems to be a good article on it here:

    http://www.sqlskills.com/blogs/jonathan/post/tuning-e28098cost-threshold-for-parallelisme28099-from-the-plan-cache.aspx

    Thanks,

    Simon


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • December 18, 2012 at 7:54 am

    #1569585

    Hi there - wow that query is excellent, thanks.

  • December 19, 2012 at 4:25 am

    #1569981

    1) 5 is universally a bad number for cost threshold for parallelism. You get WAY too many parallel queries, especially in OLTP scenarios.

    2) MAXDOP on the server should almost NEVER be zero and should NEVER be greater than the number of physical cores in each NUMA node (most modern hardware is NUMA).

    3) Outside of those two, the vast majority of CXPACKET waits I see is from poor indexing and wrong datatypes and functions around columns. Each lead in their own way to bad query plans and/or reading/hashing a LOT more data than is actually required.

    4) A few hours or few days with a good professional tuning consultant will likely provide you with many man-months worth of work to address a wide variety of issues.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 19, 2012 at 4:37 am

    #1569985

    Indeed - I am currently pulling out queries that are using parallelism and looking to tune them as a start. Then hopefully I will get a good idea of what the threshold cost should be.

    There is a lot of churn in the the plan cache though it seems so finding all the plans may prove difficult.

    I have also read about finding the thread that doesn't have a CXPACKET wait to see what the actual cause of the waits are, and am having a think about how to actually capture this, it will no doubt invlove writng something to query sys.dm_exec_requests.

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

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