CXPACKET and max degree of parallelism

Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org