July 20, 2007 at 11:30 am
I had some clarification regarding this option in sp_configure. The settings are
| max worker threads | 128 | 32767 | 0 | 0 |
How does this setting impact sql server . Is it good to configure a value or leave it to 0. Any suggestions will be greatly appreciated.
TIA
July 23, 2007 at 1:22 am
For each user connection a thread is linked to it to perform query processing by default it's 255 and one per connection is given until the number of client connections are 255 if it exceeds more then the connections start to share a thread and you get a performance hit.
From BOL:
max worker threads enables SQL Server to create a pool of worker threads to service a larger number of client connections, which improves performance.
The default setting for max worker threads (255) is best for most systems. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 23, 2007 at 9:48 am
So does that mean this setting has configured default value as 255?
| max worker threads | 128 | 32767 | 0 | 0 |
July 23, 2007 at 12:35 pm
0 means dynamic, it configures itself according to the number of CPU's and x86/x64 version.
In general you should leave this value for what it is since the default is usually the best idea.
Only in very specific cases it may be changed but I have not seen too many situations in real life.
July 24, 2007 at 6:16 am
Leave it to the default of 255 unless you find the number of connections going beyond taht.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply