June 6, 2011 at 11:53 am
Hi,
We have MOSS 2007 SP2 databases on SQL Server 2005 EE SP3.
We are experiencing the CXPACKET wait type as the top wait type. But with zero wait time (most of the times) and some time 300 MS wait time. After CXPACKET wait type, I can see some locks are happening and causing frequent blocking problem and it lasting for up to 10 to 30 mins and users are unable to access the site completely.
We are getting complaints from users that the Share Point application is running slow.
How can we prove that the slow performance is due to CXPACKET wait type?
Or How can we prove that the slow performance is NOT due to CXPACKET wait type?
Please advice
Thanks
June 7, 2011 at 5:09 am
You have to approach towards any performance issue systematically. First of all you need to ensure that it's a wait at SQL end, eliminating the waits at front end or at network. You can use SQLDiag to capture the performance counters.
Once you establish that SQL is the culprit and then CXPACKET is the top wait event: it has to be the culprit. Typically CXPACKET wait event is due to parallelism. How many CPUs do you have in your SQL Box and what is the max degree of parallelism? Is there any hint like MAXDOP being used in the queries causing the issue?
June 8, 2011 at 3:14 am
Hi,
Same issue on my server also.
cpu-8 and max degree of parallelism is zero
Cost threshold for parallelism is 5
one CXPACKET wait type creating many blocking due to that site is getting very slow
how can i reduce CXPACKET
June 8, 2011 at 5:44 am
Apparently Microsoft "strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server 2010 databases".
http://technet.microsoft.com/en-us/library/cc298801.aspx
I don't know whether or not this applies to MOSS 2007 but it might be worth changing the setting (at least on a temporary basis) to see if things improve in your environment.
Chris
June 9, 2011 at 7:59 am
1) cost threshold for parallelism of 5 is almost universally too low. I recommend 10-15 for oltp-type boxes and 25-40 for olap boxes, then monitor for cxpacket waits.
2) max dop 0 is univerally bad. set to 1/4 physical cpu count or numa core count if on numa hardware. then monitor for cxpacket waits
3) have you done a fileio stall analysis? are you also observing pageiolatch_.. waits?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 25, 2012 at 12:00 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply