CXPACKET queries

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » CXPACKET queries

CXPACKET queries

Rate Topic

Display Mode

Topic Options

Author

Message

Kwisatz78

Posted Tuesday, December 18, 2012 6:59 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:41 AM
Points: 541, Visits: 1,399

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

s_osborne2

Posted Tuesday, December 18, 2012 7:07 AM

SSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:15 AM
Points: 612, Visits: 1,867

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

Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Post #1397779

Kwisatz78

Posted Tuesday, December 18, 2012 7:54 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:41 AM
Points: 541, Visits: 1,399

Hi there - wow that query is excellent, thanks.

Post #1397812

TheSQLGuru

Posted Wednesday, December 19, 2012 4:25 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574, Visits: 5,112

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

Kwisatz78

Posted Wednesday, December 19, 2012 4:37 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:41 AM
Points: 541, Visits: 1,399

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 »

Permissions