مشاركة عبر

  • مقالة

SQL Server Batch or Task Scheduling

Each instance of SQL Server is a separate operating system process. Each instance must handle potentially thousands of concurrent requests from users. Instances of SQL Server use Microsoft Windows threads, or if configured, they use fibers, to manage these concurrent tasks efficiently. Each instance of SQL Server always runs several threads for system processes. This includes one or more threads for each server Net-Library, a network thread to handle network I/O, and a signal thread for communicating with the Service Control Manager.

Understanding Scheduling

Each instance of SQL Server has an internal layer that implements an environment similar to an operating system. This internal layer is used for scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This internal layer can also schedule fibers or Windows threads effectively. Each instance of SQL Server maintains a pool of either Windows threads or fibers for processing user queries. The maximum size of this pool is controlled by the max worker threads server configuration option.

To understand how a request or a task is processed, it is helpful to understand the following basic terms:

  • connection
    A connection is established when the user is successfully logged in. The user can then submit one or more Transact-SQL statements for execution. A connection is closed when the user explicitly logs out, or the connection is terminated.

  • batch
    An SQL batch is a set of one or more Transact-SQL statements sent from a client to an instance of SQL Server for execution. It represents a unit of work submitted to the Database Engine by users.

  • task
    A task represents a unit of work that is scheduled by SQL Server. A batch can map to one or more tasks. For example, a parallel query will be executed by multiple tasks.

  • Windows thread
    Each Windows thread represents an independent execution mechanism.

  • fiber
    A fiber is a lightweight thread that requires fewer resources than a Windows thread and can switch context when in user mode. One Windows thread can be mapped to many fibers.

  • worker thread
    The worker thread represents a logical thread in SQL Server that is internally mapped (1:1) to either a Windows thread or, if lightweight pooling is turned ON, to a fiber. The mapping is maintained until the worker thread is deallocated either because of memory pressure, or if it has been idle for a long time. The association of a task to a worker thread is maintained for the life of the task.

Managing User Connections and Worker Thread Resources

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 worker thread per connection can consume enough resources to reduce the efficiency of SQL Server. Additionally, allocating a dedicated worker for each user connection is not required, because most connections actually spend much of their time waiting for batches to be received from the client. Instead, the instance of SQL Server uses a pool of worker threads. The pool of worker threads only has to be large enough to service the number of user connections that are executing batches at the same time in that instance. Leaving the max worker threads option at its default value of 0 allows the instance of SQL Server to effectively map user connections over several worker threads. This ensures that they do not consume too many resources.

Configuring SQL Server for Fibers

The server configuration option lightweight pooling controls whether an instance of SQL Server uses Windows threads or fibers. The default for this option is 0. This indicates that the instance of SQL Server schedules a Windows thread per worker thread, up to the value set in the max worker threads option. If lightweight pooling is set to 1, SQL Server uses fibers instead of Windows threads. This is referred to as running in fiber mode. In fiber mode, an instance of SQL Server allocates one Windows thread per SQL scheduler and then allocates one fiber per worker thread, up to the value set in the max worker threads option. An instance of SQL Server uses the same algorithms to schedule and synchronize tasks when it is using either Windows threads or fibers. SQL Server Express does not support fibers. For more information, see Using the lightweight pooling Option. We do not recommend that you use fiber mode scheduling for routine operation. This is because it can decrease performance by inhibiting the regular benefits of context switching, and because some components of SQL Server cannot function correctly in fiber mode. For more information, see lightweight pooling.

How Batch or Task Scheduling Works

When an application connects to the Database Engine, it is assigned a session ID (SPID). All information that must be maintained for the life of the connection is managed in internal data structures associated with the SPID. As an instance of SQL Server receives batches from clients, it breaks the batch into one or more tasks and then associates each task with an available worker thread from a pool of worker threads. A worker thread is bound to the task for the lifetime of the task. A worker thread runs the request on the associated SQL scheduler. If there are no free worker threads and the max worker threads value has not been reached, the instance of SQL Server allocates a new worker thread 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 of SQL Server blocks the new task until a worker thread is freed.

After a worker is associated with a task, it remains associated with the task until it is completed, for example, until the last of the result sets generated by the batch has been returned to the client. At that time, the worker thread is freed and can be paired with the tasks associated with the next batch.

The Database Engine must actively perform work for a connection only from the time a batch is received until the results have been returned to the client. During this period, there may be times when the batch does not require active processing. For example, there may be times when the Database Engine must wait for a read operation to retrieve the data required for the current query, or wait for another batch to release a lock. The task to worker association is maintained even when the task is blocked on some resource.

Whenever the Database Engine starts to process a task associated with a batch, it schedules the worker thread associated with the task to perform the work. After the worker thread has completed the work for the task, instance of SQL Server dispatches the worker thread to the next task that is ready to work. A SPID remains constant for a connection for the life of the connection. Long-running connections may have their individual batch tasks executed by many different worker threads. For example, the tasks from the first batch may be executed by worker1, but the tasks from the second batch could be executed by worker2. Some statements can be processed in parallel. In this case, a batch may have multiple tasks that are executed by multiple worker threads at the same time.