Performance tuning

[UKNavajo]

Are there any guidelines on the number of threads per CPU?
A secret formula perhaps?

Currently running SQL 2000 on a 2.4Ghz P4 with 1Gb RAM.
Have allocated half the RAM to SQL but wondered just how
many threads are possible. Currently set at 255.

UKNavajo

 

[JayKusch]

The default setting for the 'max worker threads' option is 255. If the number of user connections will be less than the 'max worker threads' value, a separate operating system thread will be created for each client connection, but if the number of user connections will exceed this value the thread pooling will be used. For example, if the maximum number of the user connections to your SQL Server box is equal to 50, you can set the 'max worker threads' options to 50, this frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the 'max worker threads' options to 500, this can improve SQL Server performance because thread pooling will not be used.


Thanks

J. Kusch