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

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

Amit Banerjee — Fri, 15 Apr 2011 14:18:12 GMT

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.

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

Adam Machanic — Fri, 15 Apr 2011 14:26:40 GMT

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!

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

Earnest Grass — Fri, 15 Apr 2011 15:51:45 GMT

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

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

Adam Machanic — Fri, 15 Apr 2011 15:58:50 GMT

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 :-)

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

TheSmilingDBA — Fri, 15 Apr 2011 18:33:27 GMT

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

Thanks again - Thomas

Seeing All of the Waits (A Month of Activity Monitoring, Part 16 of 30)

Adam Machanic — Sat, 16 Apr 2011 14:06:15 GMT

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

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

Amit Banerjee — Mon, 18 Apr 2011 10:28:39 GMT

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.

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

Adam Machanic — Mon, 18 Apr 2011 14:32:59 GMT

Hi Amit,

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

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

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 15:45:12 GMT

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

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

Reiner — Tue, 12 Jul 2011 10:10:31 GMT

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

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

Adam Machanic — Tue, 12 Jul 2011 14:46:15 GMT

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

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

Reiner — Wed, 13 Jul 2011 13:34:42 GMT

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