SQL Server Performance

Worker Threads

Discussion in 'General DBA Questions' started by CanadaDBA, Oct 28, 2004.

  1. CanadaDBA New Member

    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
  2. CanadaDBA New Member

    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
  3. satya Moderator

    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.
  4. CanadaDBA New Member

    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
  5. satya Moderator

    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.
  6. Twan New Member

    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

Share This Page