Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am helping troubleshoot some problems on a SQL Server box. An application that used to run without problem is now failing with connection pool errors, and no changes have been made to the application that could cause this new problem.

In investigating possible causes of the connection pool error, I came across this post, which gave the "max worker thread" setting as a possible cause. I checked the production server, and this setting was 54 (it was 89 in dev and 79 in QA).

  1. Is there any way of seeing a log/history of when this setting was changed?
  2. Is there any reason to have the setting this low (this MSDN article recommends a minimum setting of 256 for 32-bit processors, 512 for 64-bit)?
  3. Does the low setting generally seem odd, since someone would have had to consciously set it?
share|improve this question

1 Answer 1

up vote 6 down vote accepted

There is no log of when this was changed. By default, and for 99.9% of systems this should be set to the default value of 0 so that SQL Server can manage this by itself. Set it back to 0, restart the SQL Server instance and be done with it.

share|improve this answer
    
Unless you have a really high number of connections in use, actively doing soething. I've had to set this higher than the default to avoid connection issues in the past. However, as Denny said, the default is likely perfectly fine in your instance. –  Max Vernon Nov 1 '13 at 22:30
    
Can you see any reason why it would be set to 54, 79, or 89? Would it have to have been set to these numbers manually? –  Phil Sandler Nov 2 '13 at 14:27
    
There is no built-in process that changes the max worker threads setting. As Denny said, the system default for all systems is 0, which allows the database engine to manage the setting based on the number of cores, etc. –  Max Vernon Nov 2 '13 at 14:30
    
You would need to find the person that set these values to find out the logic behind it. In 15 years of doing this I've only had to change that number on a few servers (and I touch servers at different companies with different workloads every week). –  mrdenny Nov 4 '13 at 18:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.