SQL Server Performance

Worker threads

Discussion in 'General DBA Questions' started by GopalMynam, Aug 23, 2006.

  1. GopalMynam New Member


    How do I know how many worker threads are in use in a server?

    If the setting for "Max Worker Threads" is default that is config value = 255 and Run value = 255

    Can there be more worker threads (> 255) in use?


  2. FrankKalis Moderator

    Quoted from "Inside SQL Server 2000":

    max worker threads SQL Server uses the operating system's thread services by keeping a pool of worker threads (or fibers) that take requests off the queue. It attempts to evenly divide the worker threads among the UMS schedulers so the number of threads available to each UMS is the setting of max worker threads divided by the number of CPUs. With 100 or fewer users, there are usually as many worker threads as active users (not just connected users who are idle). With more users, it often makes sense to have fewer worker threads than active users. Although some user requests have to wait for a worker thread to become available, total throughput increases because less context switching occurs.

    The max worker threads setting is somewhat autoconfigured. The default is 255, which does not mean that 255 worker threads are in the pool. It means that if a connection is waiting to be serviced and no thread is available, a new thread is created if the thread total is currently below 255. If this setting is configured to 255 and the highest number of simultaneously executing commands is, say, 125, the actual number of worker threads will not exceed 125. It might be even less than that because SQL Server destroys and trims away worker threads that are no longer being used. You should probably leave this setting alone if your system is handling 100 or fewer simultaneous connections. In that case, the worker thread pool will not be greater than 100.

    Even systems that handle 4000 or more connected users run fine with the default setting of 255. When thousands of users are simultaneously connected, the actual worker thread pool is usually well below 255 because from the back-end database perspective most connections are idle, even though the user might be doing plenty of work on the front end.

    Frank Kalis
    Microsoft SQL Server MVP
  3. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by GopalMynam</i><br />How do I know how many worker threads are in use in a server?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It should be equal to the number of user connections, if it is less than the configured Max Worker Threads value.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Can there be more worker threads (&gt; 255) in use?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No. you can only change it to a value less than 256<br /><br />Disclaimer : I am not an expert in this area <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  4. GopalMynam New Member

    Hi Thomas,

    Thanks for your inputs.
    Are there any links to this subject?
    I would like to know more about this aspect.

    Few days back ther was a situation where the default setting was for "max worker threads" and number user connections were 1400.

  5. ghemant Moderator

    Max worker threads can be set to maximum 1024 according to * Maximum Capacity Specification * in BOL and refer * max worker threads Option,Administering SQL Server * in BOL for more.


    Hemantgiri S. Goswami
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  6. mmarovic Active Member

    I think the passage from Inside SQL Server says it all you need to know.

Share This Page