SQL Server Task Scheduling

Each instance of Microsoft® SQL Server™ 2000 is a separate operating system process. Each instance has to handle potentially thousands of concurrent requests from users. Instances of SQL Server 2000 use Microsoft Windows® threads, and sometimes fibers, to manage these concurrent tasks efficiently. Each instance of SQL Server 2000 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.

Each instance of 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. Each instance of 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 an instance of SQL Server 2000 uses threads or fibers. The default is for lightweight pooling to be set to 0, in which case the instance of SQL Server schedules a thread per concurrent user command, up to the value of max worker threads. If lightweight pooling is set to 1, SQL Server then uses fibers instead of threads. This is called running in fiber mode. In fiber mode, an instance of SQL Server allocates one thread per CPU, and then allocates a fiber per concurrent user command, up to the max worker threads value. An instance of SQL Server uses the same algorithms to schedule and synchronize tasks when using either threads or fibers. SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine do not support fibers.

A SQL batch is a set of one or more Transact-SQL statements sent from a client to an instance of SQL Server for execution as a unit. As an instance of 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, the instance of 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 instance blocks the new batch 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 pool of worker threads for an instance of SQL Server only needs to be large enough to service the number of user connections that are actively executing batches at the same time in that instance. Leaving max worker threads at its default value of 255 lets the instance of SQL Server effectively map user connections over a number of threads or fibers that do not consume too many resources.