July 17, 2010 at 3:03 pm
Hi,
We are experiencing the CXPACKET blocking/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 19, 2010 at 12:10 pm
Is this the issue with Share point?
We are also seeing this CXPACKET blocking in Share point SQL instance only but orher non share point SQL instances do not have CXPACKET wait?
July 20, 2010 at 8:00 am
looks like intraquery parallelism blocking. Usually caused by either bad plans from parameter sniffing or suboptimal indexing (or both). You should do an indexing analysis session. Also consider dropping MAXDOP back to 1/4 to 1/2 of your total PHYSICAL CPU cores and also possibly upping your Cost Threshold for Parallelism. Almost every client I have has under-powered IO subsystem and parallel queries cause CPUs to twiddle their very fast thumbs for way too much time waiting for IO to reach the buffer pool.
Oh, I strongly recommend you get a performance tuning professional on board to help you out here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2010 at 7:14 am
Just looked at our Sharepoint databases and I only see this happening on Search databases when doing a "Crawl". I am still new when it comes to the behavior of Sharepoint databases but it is not causing any issues in our environment and we have over 100 Content DB's on this Cluster.
July 21, 2010 at 1:06 pm
Just looked at our Sharepoint databases and I only see this happening on Search databases when doing a "Crawl". I am still new when it comes to the behavior of Sharepoint databases but it is not causing any issues in our environment and we have over 100 Content DB's on this Cluster
Thank you Twinsoft,
In Our case, we have only Content database and CXPACKET wait is happening for this Content database only.
We used to do Index defrag and SP_UPDATESTATS for this Content database on weekly basis. But from last 3 days, I'm performing Index defrag and SP_UPDATESTATS daily to see whether CXPACKET will go way. But it still happening during business hours.
We have Single mdf file and single ldf file on Different drives for all share point databases including Content database and it's size is 25 GB.
We have 16 GB RAM and 8 Processors (2 quad core processors) --> Not sure Hyperthreding is enabled or not (please advice where to check)
MaxDop is set to default ie. 0
lock pages in memory is enabled
Max memory is set to 12 GB and 4 GB is left for OS
Free Virtual memory is 22%
Target Server memory and Total Server Memory are always equal (12 GB)
Memory Availble MBytes = 2.2 GB
You said that you are having more than 100 Content databases, so all the Content databases have Single mdf file or have Multiple data files?
Please advice
July 22, 2010 at 5:39 am
Yes, they are single mdf and ldf databases! Also split up on seperate volumes as well, my log files are all together while my mdf's are on multiple volumes.
I am considering multiple filegroups and adding more ndf's as databases grow. My largest contentdb is 150GB.
July 22, 2010 at 11:19 am
Yes, they are single mdf and ldf databases! Also split up on seperate volumes as well, my log files are all together while my mdf's are on multiple volumes.
I am considering multiple filegroups and adding more ndf's as databases grow. My largest contentdb is 150GB
If you are having single mdf file, then how it is possible to place single mdf on different volumes?:-D
Multiple filegroups is NOT supported for Content database..
How many CPU's you have and what is the MAXDOP for the SQL instance having Share point databases?
Thanks for your inputs
July 23, 2010 at 12:43 pm
Hi,
We have two quad core CPU's .i.e 8 CPU's.
So in this case, what would be the best value for MaxDop setting?
Thanks
July 23, 2010 at 2:43 pm
I ran the below query to find out the wait types:
and noticed very high values for CXPACKET, SLEEP_TASK,ASYNC_NETWORK_IO
select * from sys.dm_os_wait_stats
where wait_type in('CXPACKET', 'SLEEP_TASK','ASYNC_NETWORK_IO')
Results:
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
ASYNC_NETWORK_IO
102885218 61020562 19953 2899265
SLEEP_TASK
1133090272 131157593 1140 129270359
CXPACKET 584361171 997543609 121140 43274031
(3 row(s) affected)
Please advice
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply