THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Seeing the Wait That Matters Most (A Month of Activity Monitoring, Part 15 of 30)

THIS CONTENT HAS BEEN MOVED HERE.

Published Friday, April 15, 2011 11:00 AM by Adam Machanic

Comments

 

Amit Banerjee said:

IMHO CXPACKET waits are always a victim of issues like outdated statistics, poor indexing or no indexes available, lack of statistics on the table etc. which leads to a poor query plan. SQL Server determines on parallel execution primarily based on "cost threshold for parallelism". To optimize a parallel query, you need to start with inspecting the query plan and looking for means to remove the huge number of rows fetched by the inner-most operators in the plan. This could be done either by updating statistics or by adding/modifying indexes or adding query hints. Changing MAXDOP value to a lower value is just a workaround to combat parallelism waits.

April 15, 2011 10:18 AM
 

Adam Machanic said:

Amit, Saying that CXPACKET waits are caused by "poor query plans" is equivalent to saying that parallelism is a bad thing. It is very rare these days to find "pure" OLTP systems of the type where parallelism isn't an appropriate choice for various queries, so this mindset is, in my opinion, a bit outdated. Personally I specialize in larger systems where parallelism is quite welcome. If I'm *not* seeing CXPACKET waits, that's a great indication that there is a major problem!

April 15, 2011 10:26 AM
 

Earnest Grass said:

At what point does CXPACKET sound the alarm for being too high?  Do you use this wait in tuning the MAXDOP settings?

April 15, 2011 11:51 AM
 

Adam Machanic said:

Earnest: 99% of the time, no. I don't bother looking at CXPACKET waits at all. I shared some thoughts on tuning MAXDOP here:

http://sqlblog.com/blogs/adam_machanic/archive/2010/05/28/sql-university-parallelism-week-part-3-settings-and-options.aspx

More in depth thoughts are available if you attend one of my sessions on parallelism at a conference or training event--and some more will be available in print later this year but I can't share specifics quite yet :-)

April 15, 2011 11:58 AM
 

TheSmilingDBA said:

Good explanation. Maybe you can break sp_WhoIsActive into multiple SPs. SP_WhoIsActiveBeginner, SP_WhoIsActiveIntermiediate and Advanced.

Thanks again - Thomas

April 15, 2011 2:33 PM
 

Adam Machanic said:

This post is part 16 of a 30-part series about the Who is Active stored procedure. A new post will run

April 16, 2011 10:06 AM
 

Amit Banerjee said:

My comment was not to indicate that all CXPACKET waits are a problem. There are situations where a query can run more efficiently without a parallel plan or with a parallel plan. It depends completely on the type of query being executed along with the type of indexes/statistis being used.

All I am trying to say here is that CXPACKET waits *cannot always* be ignored. There are situations where parallelism helps query execution and there are situations where parallelism is introduced due to the plan choice but a serial plan with query hints works out better. I have seen environments both ways.

April 18, 2011 6:28 AM
 

Adam Machanic said:

Hi Amit,

Indeed, it cannot always be ignored. Just usually ;-)

See post #16 for how to see CXPACKET waits with Who is Active.

April 18, 2011 10:32 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

Reiner said:

Hi Adam and all,

I'm a bit desperate about my CXPacket Situation. I have a 24 Core HP machine (4x6 NUMA AMD) and incredibly high CXPacket waits. In addition to that, during peak hours I'm seeing the System\Context Switches\s count go to about 250000/s. MAXDOP is set to 6. I'd be really grateful for some hints as to how that situation could be changed.

WaitType Wait_S Resource_S Signal_S WaitCount Percentage

CXPACKET 5826475.42 5404313.02 422162.40 2866188281 89.39

BACKUPBUFFER 228008.21 227000.54 1007.67 4871571 3.50

SOS_SCHEDULER_YIELD 166674.22 158.94 166515.28 199137201 2.56

July 12, 2011 6:10 AM
 

Adam Machanic said:

Hi Reiner,

Looking at that data I'd say you have nothing at all to worry about. You have a lot of parallel queries--nothing wrong with that--and parallel queries mean CXPACKET. The waits themselves are miniscule on average:

5404313 seconds / 2866188281 waits == 0.00188 seconds

High context switches may or may not be a problem. How's performance? That's really all that matters at the end of the day.

--Adam

July 12, 2011 10:46 AM
 

Reiner said:

Hi Adam,

Well, I guess you and all the others saying CXPacket waits are just part of life finally have me convinced. Performance isn't bad considering all databases have several hundred million rows combined. (It's a DWH server). Runtimes are up to 14 hours but that is to be expected. I think it's now all about good SQL statistics, indexes and execution plans. We are going to test 'Cost Threshold for Parallelism' though and push it up to 35 to see whether that'll make a final difference.

Do you think the high signal time per wait makes a difference here?

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms resource_wait_time_ms signal_wait_time_per_wait resource_wait_time_per_wait

REQUEST_FOR_DEADLOCK_SEARCH 120310 597703791 5190 597703791 0 4968 0

XE_TIMER_EVENT 19924 597702917 30194 597702682 235 29999 0

July 13, 2011 9:34 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Privacy Statement