CXPACKET waittype and max worker threads 1

[mutley1]

Hope you all had a very merry Xmas!!

SQL 2000

Just looking through the wait types and blocking from sysprocesses in master and for a few processes, it regularly turns up with CXPACKET as the waittype. We frequently have between 250 - 350 connections to the SQL server, and CPU usage for SQL is usually running between 5 and 50% throughout the day, spiking occasionally.

I have been reading up on MAX WORKER THREADS option, as ours is currently set to 255. Some sites say it is a good option to change, some say it is a bad idea.

Question is, given the above, what else do I need to check to see if changing the MAX WORKER THREADS option to, say 300, would possibly be detremental to the system? Or should I not even consider doing that?

It's an 8 CPU machine and max degree of parallelism is currently set to 3 CPUs.

TIA,

M.

 

[JayKusch]

Here is a great blog article from one the the great SQL engine gurus who is no longer with us ...

http://blogs.msdn.com/khen1234/archive/2005/11/07/489778.aspx

That being said, I have only been directed by MS SQL engineers once in my many years to adjust the Max Worker Threads.

And that was for an environment that have several thousand connections pushing millions of rows per hour.



Thanks

J. Kusch

 

[mutley1]

Thanks Jay,

Bit more food for thought.

Cheers,

M.

 

[mutley1]

Jay,

If I were to post results from when we were seeing CXPACKET waits, would you be able to help me with a bit of analysis? I can see what is going on but not overly sure of the results - or is that not something you would be able to assist with? I found a script that dumps it to a table and makes it more legible when running a 'dbcc sqlperf(umsstats) with tableresults, no_infomsgs'

Cheers,

M.

 

[JayKusch]

Sure,

drop the info here in this thread.

If I am not able to make heads or tails of it, I KNOW we have a few other top notch folk who will pipe in with their thoughts and experiences.

Thanks

J. Kusch

 

[mutley1]

I definately know there are loads of top knotch folk in this community!!!!

Thanks Jay - please see below.

Scheduler_ID Num_Users Num_Runnable Num_workers Idle_workers Work_queued Context_switches Context_switches_idle
0 30 0 12 10 0 149189680 138810912
1 30 0 12 10 0 61752256 112682456
2 29 0 12 11 0 48486932 77933632
3 29 0 12 11 0 69639136 99675240
4 31 0 12 10 0 34293872 78787000
5 31 1 12 9 0 45118904 71083944
6 30 1 11 9 0 85461040 101500080
7 29 0 11 10 0 51212496 82087128

max degree of parallelism is set to 3 as i mentioned so I assume SQL takes 0, 1, 2.....?

Anyways - is my server rubbish or working fine? LOL

TIA,

M.