1

I have always had a very high CXPACKET wait type, and I've been told that it stems from parallel processing, and I should keep MAXIMUM DEGREE OF PARALLELISM = (Nº Processors / 4), or 1 in my case.

But the CXPACKET waits are still very high, at 32.78% of all waits. Any suggestions?

4 Answers 4

1

Did you clear the wait statistics after you lowered maximum degree of parallelism to 1?

But there is really no valid simple, universal rule of thumb about how this setting should be configured, other than to measure the impact of any changes you make. In addition to the above URLs, I'd recommend reading this post by Paul Randal.

I'd also suggest reconsidering focusing more on the other top wait types -- I often find that CXPACKET waits are a red herring and that they're occurring because of complex queries that get parallelized into simple pieces of work that complete quickly and larger, I/O-bound pieces of work that take a long time to finish. The CXPACKET waits show up when the small, fast pieces of work complete and wait for the larger pieces of work to complete.

If you're looking at tuning specific queries, the execution plan should show you what parts of the query are taking a long time to finish, and then you can tune to address the problem. Out-of-date statistics or poorly chosen indexes are often the culprit there.

1

That doesn't actually mean there's a problem.... parallelism is a good thing, and the CXPacket wait type is showing just because SQL Server has to synchronise between the different pieces of work.

When you switch to maxdop=1, is the run time of that piece of code slower than when it go parallel? This is the real proof of the pudding, so to speak.

There's a lot of contradictory articles out there, but these two are written by SQL people who know what they are talking about! (ok, one of them defected to Oracle!)

http://itknowledgeexchange.techtarget.com/sql-server/cxpacket-isnt-the-cause-it-is-a-symptom/

http://sqlserverpedia.com/blog/sql-server-bloggers/cxpacket-maxdop-and-your-oltp-system/

1

The CXPACKET wait type most probably is not what is causing your problem, but rather a consequence of it.

This wait type means that the worker is waiting for some other operation to complete before it can go on. You should check the session that's responsible for the CXPACKET and see what exactly it's waiting for:

SELECT session_id, wait_type, wait_time, start_time, *
FROM SYS.dm_exec_requests
WHERE session_id > 50
GO

SELECT * 
FROM sys.dm_os_waiting_tasks AS t
WHERE session_id > 50
GO

Only after you've diagnose the real cause of the problem should you take an action to try and mitigate it.

0

Setting MAXDOP blindly on an instance without considering the workload is, IMO, a big mistake. It may not necessarily end up hurting, but it's a Good Idea(tm) to back a change from default configuration with a very good explanation. If you can't explain it, don't do it.


From what I've seen in practice so far, a high percentage of CXPACKET waits is an indication of big parallel table scans.

Use SQL Profiler to analyze the queries running on the instance: there's a good chance they need to be index tuned, or possibly even rewritten to be more efficient. CXPACKET waits are merely a symptom of a (possible) problem.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .