SQL Server Performance

Worker Threads

Discussion in 'Performance Tuning for DBAs' started by ThomBeaux, Oct 10, 2006.

  1. ThomBeaux New Member

    Some articles on this site say:

    SQL Server General: User Connections

    Since the number of users using SQL Server affects its performance, you may want to keep an eye on the SQL Server General Statistics Object: User Connections counter. This shows the number of user connections, not the number of users, that currently are connected to SQL Server.

    If this counter exceeds 255, then you may want to boost the SQL Server configuration setting, "Maximum Worker Threads" to a figure higher than the default setting of 255. If the number of connections exceeds the number of available worker threads, then SQL Server will begin to share worker threads, which can hurt performance. The setting for "Maximum Worker Threads" should be higher than the maximum number of user connections your server ever reaches.


    Other say you should not, because the overhead for those threads reduce performance.

    Thanks,
    Thomas
  2. joechang New Member

    this is way too simple a theory to apply
    many connections are inactive and hence have no need for a worker thread
    besides, you may only have 4-8 processor cores, whats the rush to get more threads

    additional threads and connections are not a direct performance impact, but rather a resources impact, most relevent is address space, on a 32-bit system excess address space usage causes severe problems

    the probable course of action is to determine why connections are high
    suppose some big query causes a slow down across all queries
    most apps servers use connection pooling, which then immediately opens more connections, agravating the original slow down

    so,
    understand what is going on in your system, and take appropriate measures to the true cause

    for some reason, many people disregard truth in preference for blind obedience to rules
  3. babusamuel New Member

  4. joechang New Member

    only one thread per (logical) processor is active at any time period
    it does not matter if it is the OS scheduler or SQL UMS
    also, the above link does not really discuss the worker thread setting
    I have heard MS people say it is ok to go above 255, but without clear explanation of when, and this usually applied to 64-bit superdome type systems
  5. satya Moderator

    Max Worker Threads concept is confusing in some terms, a system can have thousands of user connections (which are essentially multiplexed down to 255 worker threads) and, in general, users do not perceive any delays. If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. I would go with KBAhttp://www.microsoft.com/isapi/gosupport.asp?TARGET=/?kbid=319942 in this case to determin whether is it necessary.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page