October 28, 2004 at 8:16 am
Yesterday, we encountered a slow respond from SQL-Server. First I thought there is a lock but there wasn't. Then I noticed in EM that there are 341 users connection. Worker Threads are set to 255 (default). We have Access 2000 application as interface and SQL-Server as data source.
The production server has 3.5GB RAM but SQL-Server Standard version is installed. I believe it uses only 2GM at most.
Should I increase the number of Worker Threads? If yes, what is the best for my case? 350? Any disadnatage?
October 28, 2004 at 1:06 pm
If the number of users exceeds the number of worker threads then sql server will start to pool the threads this can lead to contention among user processes requiring access to sql server.
To decide whether you need to increase this you should check the maximum number of user connections the server experiences and set it to this plus 5 to achieve best performance. However this is dependant there being enough RAM in your server. If you have less than 255 connections then leave the default.
If you don't have extra RAM available, then adding more worker threads can hurt SQL Server's performance. In this case, allowing SQL Server to use thread pooling offers better performance. This is because thread pooling uses less resources than not using it. However you may suffer from contention issues as noted earlier.
The long and short is you have to experiment to find out if adding extra threads hurts or improves your performance. I tend to use target server memory and actual server memory to help guide me on this i.e. if the target is not larger than what sql server has got it is not short of memeory but this is agan by no means exact and trial and error tends to provide the answer to what you should do.
hth
Dave
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply