Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CXPACKET queries Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 6:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 598, Visits: 1,628
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?

Post #1397774
Posted Tuesday, December 18, 2012 7:07 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 705, Visits: 2,191
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
Post #1397779
Posted Tuesday, December 18, 2012 7:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 598, Visits: 1,628
Hi there - wow that query is excellent, thanks.
Post #1397812
Posted Wednesday, December 19, 2012 4:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 4,406, Visits: 6,270
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 at GMail
Post #1398296
Posted Wednesday, December 19, 2012 4:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 598, Visits: 1,628
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.
Post #1398301
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse