Microsoft® SQL Server™ uses Microsoft Windows® threads, and sometimes fibers, to execute several concurrent tasks efficiently. SQL Server version 7.0 always runs several threads for system processes: one or more threads for each server Net-Library, a network thread to handle login requests, and a signal thread for communicating with the service control manager.
SQL Server has an internal layer that implements an environment similar to an operating-system for scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This internal layer can schedule fibers as effectively as it works with threads. SQL Server maintains a pool of either threads or fibers for user connections. The maximum size of this pool is controlled by the max worker threads server configuration option.
The server configuration lightweight pooling option controls whether SQL Server uses threads or fibers. The default is for lightweight pooling to be set to 0, in which case SQL Server schedules a thread per concurrent user command, up to the value of max worker threads. If lightweight pooling is set to 1, then SQL Server uses fibers instead of threads. This is called running in fiber mode. In fiber mode, SQL Server allocates one thread per CPU, then allocates a fiber per concurrent user command, up to the max worker threads value. SQL Server uses the same algorithms to schedule and synchronize tasks when using either threads or fibers. SQL Server Desktop Edition does not support fibers.
A SQL Server batch is a set of one or more Transact-SQL statements sent from a client to SQL Server for execution as a unit. As SQL Server receives batches from clients, it associates each batch with an available free thread or fiber from the worker pool. If there are no free threads or fibers and the max worker threads value has not been reached, SQL Server allocates a new thread or fiber for the new batch. If there are no free threads or fibers available and the max worker threads value has already been reached, the new batch blocks until a thread is freed. After a thread or fiber is associated with a batch, it remains associated with the batch until the last of the result sets generated by the batch has been returned to the client. At that time, the thread or fiber is freed and can be scheduled to the next available batch.
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. The worker pool only needs to be large enough to service the number of user connections that are actively executing batches at the same time. 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.