INF: SQL NT Thread Pooling

  - Microsoft SQL Server, version 4.2

SUMMARY

Microsoft SQL Server uses a concept called thread pooling to optimize performance when very large numbers of clients are connected.

MORE INFORMATION

Normally, a separate operating system thread is spawned for each client connection. This thread-per-connection architecture consumes less system overhead than the process-per-connection approach of some other products. However, at very high connection numbers (usually hundreds), even using a thread-per-connection can consume operating system overhead. Therefore, SQL Server can use a technique called thread pooling, whereby a pool of worker threads services a larger number of client connections.

By default, this worker thread limit is 255, and can be viewed or changed with sp_configure, as "max worker threads."

When this limit is reached, the SQL Server kernel message:

   The working thread limit of 255 has been reached

Below this point, a new thread is spawned for each user connection. Above this thread limit, new connections will share the existing pool of worker threads. Since there are then more connections than threads, each new request is handled by the next worker thread that completes its current task. This process is quite efficient, as it is uncommon to have more than 255 (or whatever the max worker thread setting is) pending requests, even on a system with hundreds of clients.