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
Showing posts with label node. Show all posts
Showing posts with label node. Show all posts
Friday, February 17, 2012
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.org
Basically, 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
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
Basically, 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
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
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
Tuesday, February 14, 2012
CustomReportItem
Hi,
I am trying to find out information on how to use the CustomReportItem node.
The online documentation (and RDL specification) talks about accessing a
RichTextControl as an example but does not give details on full
implementation required. If CustomReportItem is currently supported
(searching this board seems to imply it is not supported in current release?)
can someone point me in the direction of a code example or guide on how to
use this node if one exists.
Thanks,
RichardThe structure/implementation for CustomReportItem will change significantly
in RS 2005. The functionality will be available in the upcoming RS 2005 Beta
3 (actually is already in the last community preview releases of SQL 2005).
Not sure if the documentation will be already contained in the RS 2005 Beta
3 BOL release though.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"R.Shiels" <R.Shiels@.discussions.microsoft.com> wrote in message
news:6D369D32-9B26-43C7-A1D7-48BEA896B75B@.microsoft.com...
> Hi,
> I am trying to find out information on how to use the CustomReportItem
node.
> The online documentation (and RDL specification) talks about accessing a
> RichTextControl as an example but does not give details on full
> implementation required. If CustomReportItem is currently supported
> (searching this board seems to imply it is not supported in current
release?)
> can someone point me in the direction of a code example or guide on how to
> use this node if one exists.
> Thanks,
> Richard
>
I am trying to find out information on how to use the CustomReportItem node.
The online documentation (and RDL specification) talks about accessing a
RichTextControl as an example but does not give details on full
implementation required. If CustomReportItem is currently supported
(searching this board seems to imply it is not supported in current release?)
can someone point me in the direction of a code example or guide on how to
use this node if one exists.
Thanks,
RichardThe structure/implementation for CustomReportItem will change significantly
in RS 2005. The functionality will be available in the upcoming RS 2005 Beta
3 (actually is already in the last community preview releases of SQL 2005).
Not sure if the documentation will be already contained in the RS 2005 Beta
3 BOL release though.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"R.Shiels" <R.Shiels@.discussions.microsoft.com> wrote in message
news:6D369D32-9B26-43C7-A1D7-48BEA896B75B@.microsoft.com...
> Hi,
> I am trying to find out information on how to use the CustomReportItem
node.
> The online documentation (and RDL specification) talks about accessing a
> RichTextControl as an example but does not give details on full
> implementation required. If CustomReportItem is currently supported
> (searching this board seems to imply it is not supported in current
release?)
> can someone point me in the direction of a code example or guide on how to
> use this node if one exists.
> Thanks,
> Richard
>
Subscribe to:
Posts (Atom)