January 5, 2009 at 12:00 pm
I understand CXPACKET waits are related to parallelism. Currently our SQL Server is set to use all available processors (4 dual core CPU's, which show up as eight processors.) With duel core processors how should I adjust the MAXDOP, by core or physical processor?
Thanks,
Tom
January 5, 2009 at 12:41 pm
It very much depends upon the kind of workload that you are doing and how queries, inserts, updates, deletes are handled.
I had the same issue using a MAXDOP of 8. I had an insert/data rollup/query process that was taking > 20 hours to complete, with a CXPACKET wait and all the CPU's sitting there doing nothing. I changed the MAXDOP to 4 and the entire process completed in around 3 hours with those CPU's working pretty hard.
I have no idea why. Just sharing the experience.
January 5, 2009 at 12:47 pm
Thanks for the information. The system is a data warehouse, not OLTP. We are looking to improve query performance overall, reducing CXPACKET waits came up as one possibility.
SQL Server 2000 Ent. SP3a, 32GB ram, SAN controlled storage (approximately 1TB).
January 5, 2009 at 1:04 pm
We had this issue sometime back and changing the max degree of parallelism to 1 had increased performance a lot. Mine was a 8 processor and 4 dual core cpu server. By default its set to " 0 " and uses all processors for executing queries. By setting to one, I assured that at least I am not preventing other queries executions.
Comments from a Microsoft support:
SQL Server does not have any form of resource governor yet. There is a SET option called QUERY_GOVERNOR_COST_LIMIT but it is practically useless. And it prevents query from executing based on the cost rather than controlling resources. For now, the only way is to make sure you disable parallelism or set it to the optimum MAXDOP value and optimize your query so that the execution plans are simpler.
SQL DBA.
January 5, 2009 at 1:04 pm
I'd try dropping the MAXDOP to 4 and see how it goes. Since it's a warehouse (big queries) I wouldn't take it to 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2009 at 4:13 pm
I agree with Gail. My gut feeling for a DW with 4x2 cores is that MAXDOP should be between 2 and 4 (i.e., 2, 3, or 4).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 16, 2010 at 9:32 pm
Hi Gail & Rbarry,
We are also experiencing the same CXPACKET wait.
We have Share Point 2007 SP1 databases on SQL Server 2005 EE x64 with SP3.
We have 2 quadcore processors and system showing 8 processors. Max degree of parallelism is set to default i.e 0
This CXPACKET blocking was not there initially, but now the Content database is 20 GB and I'm seeing this CXPACKET blocking continuously from 8 am to 5 pm (in Spot light monitoring tool)
please see the attachment
Is CXPACKET blocking normal? If its not normal, then what are the steps to avoid this CXPACKET blocking??
How to detect this from SQL Server side? like using what DMVs??currently, I'm seeing this from Spotlight monitoring tool.
Many thanks
July 17, 2010 at 4:07 am
Please post new questions in a new thread. It's unlikely that many people will look at this because it looks, from the number of posts, as if it's answered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply