SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CXPACKET queries


CXPACKET queries

Author
Message
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 1821
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?
s_osborne2
s_osborne2
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1697 Visits: 2292
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
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 1821
Hi there - wow that query is excellent, thanks.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30745 Visits: 8668
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
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 1821
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search