Setting "max worker threads"

The number of connections isn't really going to tell you if
you need to change the setting or not. Something like
connections over 255 doesn't mean you need to adjust this.
That's not really how the worker threads operate.
You can monitor how worker threads are being utilized with
dbcc sqlperf(umsstats)
Also, you should have warnings in your logs when you reach
the limit in the pool.
You can find more information on the subject of worker
threads and UMS in the following article:
<a style='text-decoration: underline;' href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/Sqldev_02252004.asp" target="_blank">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql.../html/S</a>

-Sue

On 15 Feb 2005 11:11:45 -0800, "annhong"
<annhong.RemoveThis@comcast.net> wrote:

 >Before changing this configuration from default (255), I like to check
 >user connection high water mark. Could someone tell me how to ckeck?
  >>From SQL Server 2000 class book it says:
 >Use teh SQL Server:General Statitics \User Connection object counter to
 >determine the number of users.
 >I still do not know where to find General Statitics ...
 >
 >Thanks in advance<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Setting &quot;max worker threads&quot; 

One spid with more than one ecid is from parallel execution
and you'll see that when you have more than one resource to
perform the task - physical device, processor, controller,
etc. It's nothing really that unusual and generally results
in better performance (but there can be exceptions). And no
that doesn't correspond to having 30 workers on a scheduler
or the workers on a scheduler increasing by 30 for whichever
scheduler that spid ended up on when it connected.

The number of workers on a scheduler isn't automatically set
to the max - if I have a single processor box and start it
up, the worker threads is not set to 255. The maximum would
be 255 but it doesn't hold the workers at 255. I don't see
where lowering it would really buy you much of anything. And
if you want to increase this, you really should be seeing if
it is necessary by monitoring the ums stats. Needing to
increase this would be unusual and can often result in
performance degradation. I'm not real sure why the course
book is telling you to change the settings. There is also a
difference between the number of users and the number of
workers for a scheduler. I've seen some articles mention
increasing the max worker threads if the number of user
connections is over 255. But again, that's not necessarily
good advice. Users in the ums stats does not equal workers.
Users are just the number of user connections associated
with that scheduler and it's the workers you would want to
monitor in the ums stats.
In most cases, its better to just leave the defaults as they
are. The following provides some more information on SQL
Server settings:
How to determine proper SQL Server configuration settings
<a style='text-decoration: underline;' href="http://support.microsoft.com/?id=319942" target="_blank">http://support.microsoft.com/?id=319942</a>

-Sue

On 15 Feb 2005 12:12:37 -0800, "annhong"
<annhong.DeleteThis@comcast.net> wrote:

 >Thanks very much for your reply. I did run dbcc sqlperf and supprised
 >to see our Sql server has 8 schedulers with no work request. We
 >usually only have a few users on the system, but I saw one query invoke
 >30 contexts (one user process ID with 0 to 30 context ID). I am
 >wondering if it means 30 worker threaders?
 >
 >My intention to change this configuration to save the memory as SQL
 >Server class book says. I am not sure how much whould save.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Setting &quot;max worker threads&quot; 

Thanks. Your explaination and the artical are very helpful.

Ann
 >> Stay informed about: Setting &quot;max worker threads&quot;