SQL Server Performance Forum – Threads Archive

Worker Threads

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? Sometimes ago, Satya gave me the following code and as I check, it returns between 290 – 350 during work hours.
SELECT *, counter_name AS Expr1
FROM master..sysperfinfo
WHERE (counter_name = N’User Connections’)
I am just afraid of if I change the number to 350 fall in problem. I can not test it in development server because there is not that much threads. Is it safe to do the change on production? How can I monitor it to find out if the server falls in trouble? Thanks, CanadaDBA

In Microsoft site, I read that an error message like "The working thread limit of 255 has been reached." would be generated if there is a problem with worker thread. I don’t see this error or similar error in SQL error log for yesterday. Does it mean that there was no problem related to Worker Threads yesterday and SQL Server was slow down because of something else? probably a lock on a table or something?
CanadaDBA

Run PROFILER during that slow execution of queries to findout which is the culprit and first point of investigation. Also PERFMON counters would help to asses the issue. Read about worker threads setting in this articlehttp://www.sql-server-performance.com/sql_server_configuration_settings.asp that may help. And check whether SQL Server is set to run in FIBRE mode. Lastly, while threads and fibers are lightweight in their use of resources, they still consume resources. In systems with hundreds or thousands of user connections, having one thread or fiber per connection could consume enough resources to reduce the efficiency of SQL Server. Allocating a thread or fiber for each user connection is also not necessary because most connections actually spend much of their time waiting for batches to be received from the client. Leaving max worker threads at its default value of 255 lets SQL Server effectively map user connections over a number of threads or fibers that do not consume too many resources. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya;
I have no idea about FIBRE mode. Is it mentioned in the link you’ve mentioned? If not, would give me some hints? sp_Configure returned 255 for max worker threads on both Config_value and Run_value columns. Does this mean that I need to increas the amount? CanadaDBA

Yes the FIBER mode is specified in the article and as well in the books online for your information.
FIBER mode relates to the priority boost configuration option can be used to increase the priority of the threads from an instance of SQL Server to 13, which is called high priority. The default setting for max worker threads (255) is best for most systems. And in your case it both values were not changed. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

FIBER mode and priority boost are not really related… priority boost is increasing the priority of the SQL server threads, where as fiber mode is about sql scheduling multiple fibers per thread (and as a result handling its own scheduling of those fibers) fiber mode is only useful for systems with very high cpu utilisation
also, priority boost is not normally recommended as it can starve the OS from using the CPU itself
Cheers
Twan

]]>