|
Home > Archive > MS SQL Data Warehousing > August 2005 > lot of CXPACKET wait types...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
lot of CXPACKET wait types...
|
|
|
| Hi again,
my second problem is the CXPACKET wait type.
during the execution of complex queries when the same table is used multiple
times, I have some CXPACKET waits.
My server is SQL 2000 Ent. SP4 with 4 proc. (with HT, so windows see 8
processors)
my HD subsystem is bad and I can't do anything here :-(
using the maxdop option sometimes helps sometimes doesn't helps.
when I use maxdop = 1, then I see 1 processor to work, then another work,
then a third...
its not only the same processor used during the entire query process.
but forcing maxdop = 1 cause the query to be slower
for 1 query this is my statistics (trace on):
maxdop = 1
time to process= 1'54
Duration= 180
CPU= 138
Reads= 45
No CXPACKET waits
maxdop = 8
time to process= 0'50
Duration= 152
CPU= 187
Reads= 193
a lot of CXPACKET waits displayed using the sp_who1
so there is 193 reads instead-of 45 when I use all my processors, but the
overall result takes only 50sec.
but for another query its the oposite result, maxdop = 1 improve the
performance !
any guide? or idea?
thanks
jerome.
| |
| Mike Epprecht (SQL MVP) 2005-08-10, 11:23 am |
| Have a look at
http://groups.google.ch/group/micro...cc7be9
73
--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jéjé" wrote:
> Hi again,
>
> my second problem is the CXPACKET wait type.
>
> during the execution of complex queries when the same table is used multiple
> times, I have some CXPACKET waits.
>
> My server is SQL 2000 Ent. SP4 with 4 proc. (with HT, so windows see 8
> processors)
> my HD subsystem is bad and I can't do anything here :-(
>
> using the maxdop option sometimes helps sometimes doesn't helps.
> when I use maxdop = 1, then I see 1 processor to work, then another work,
> then a third...
> its not only the same processor used during the entire query process.
>
> but forcing maxdop = 1 cause the query to be slower
>
> for 1 query this is my statistics (trace on):
> maxdop = 1
> time to process= 1'54
> Duration= 180
> CPU= 138
> Reads= 45
> No CXPACKET waits
>
> maxdop = 8
> time to process= 0'50
> Duration= 152
> CPU= 187
> Reads= 193
> a lot of CXPACKET waits displayed using the sp_who1
>
> so there is 193 reads instead-of 45 when I use all my processors, but the
> overall result takes only 50sec.
>
> but for another query its the oposite result, maxdop = 1 improve the
> performance !
>
> any guide? or idea?
>
> thanks
>
> jerome.
>
>
>
| |
|
| the maxdop option doesn't help too much,
this reduce the waittype, but the query is longer to execute.
what I have to monitor to identify the bottleneck?
"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:4E1C3446-2973-4850-A17A- 1A5619334A92@microso
ft.com...[color=darkred]
> Have a look at
> http://groups.google.ch/group/micro...cc7be9
73
> --
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
>
>
> "Jéjé" wrote:
>
|
|
|
|
|