August 2, 2004 at 1:36 am
Hi All
Can anybody elaborate on what the value '0x0208' might mean for the WAITTYPE coulmn when selecting from the 'sysprocesses' table. The associated lastwaittype is 'CXPACKET' Also there is an entry with a waittype of '0x0046' and lastwaittype value of 'MISCELLANEOUS '.
We had a situation where a trace that was monitoring just 'deadlock' events seems to have a blocked another process. This process showed up the above values. The minute I stopped the trace, the waits were gone.
Any assistance will be highly appreciated.
Kamal
August 2, 2004 at 7:34 am
CXPacket waits are usually down to parallelism, I've found they can be an indicator of poor sql, select into statements and out of date stats can produce lots of cxpacket waits where a spid spawns lots of threads. I'm sure there is an article explaining the waits somewhere on the site.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 2, 2004 at 7:58 am
Hi Colin
Thanks for the advice. I will definitely followup.
Regards
Kamal
August 3, 2004 at 5:37 am
Not always poor SQL...
If your CPUs are hyperthreaded (Xeon 2Ghz+, top end P4 etc) then SQL will parallel across all CPUs - physical and logical, when it shouldn't really.
HTT is there to minimise CPU idle, not provide 2 actual CPUs in one package
An example of mine:
Server:
2 x 2.8 Xeon (= 4 CPUs), 3Gb RAM, disk arrays galore etc, Enterprise edition
An archiving job (100,000s of rows, 4 multi-million row tables, temp tables) etc took 3hr 40 with load of CXPACKET waits.
Setting "affinity mask" to 3 (CPUs 0 and 1, the "physical" ones) takes 1 hour with minimal CXPACKET waits.
I've since set the affinity mask back to 0 and set "max degree of parallism" to 2 to allow SQL to use "all 4" CPUs but only 2 at any one time
August 3, 2004 at 5:45 am
A lot depends upon many things and I have heard of Hyperthreading causing problems - however, my server is a 4 way P4 zeon, with hyperthreading turned on and so far I've not encountered those type of problems, perhaps I'm lucky < grin > however, so far all of the cxpacket stuff has been resolved by removing select into's ( it used to be a fairly common way to create and populate temp tables here - I've hopefully shown the developers the error of their ways now !! ) and usually by efficient indexing and updated stats - it's worth pointing out that etl tasks especially can often be prone to out of data stats due to data loads and cuts etc. etc. Don't always assume the auto-update and create stats will do what you might expect.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 3, 2004 at 5:57 am
Oddly enough (or not...) the archiving routine is for a 3rd party app and it has "SELECT INTO...", I've no control over the code.
I trawled google for stuff on SQL and HTT and that above is my condensed summary and my interpretation and so far it plays out well.
I'm lucky to have another server (2x3.06, 2Gb, Standard) that's not yet live and allows to me to play with these settings... I'm probly going to set maxdop at the server level and leave the affinity mask at zero.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply