MSSQL SERVER: Schedulers and Threads

Microsoft SQL Server is a Service that runs inside of an OS (no longer exclusively on Windows – i.e. SQL Server on Linux). However, it has its own mechanisms for handling memory management, IO and thread scheduling. This is done through access to the host OS API in most cases, but SQL is still the one actively managing these resources.


 A thread is the basic unit to which the operating system allocates processor time. A thread can execute any part of the process code, including parts currently being executed by another thread. Threads are the smallest units of processing that can be executed by an operating system, and allow the application logic to be separated into several concurrent execution paths.

SQL Server Worker Thread

Also known as worker or thread, is a logical representation of an operating system thread. When executing serial requests, the SQL Server Database Engine will spawn a worker to execute the active task (1:1).


When an operating system executes an instance of an application, it creates a unit called a process to manage the instance. The process has a thread of execution. This is the series of programming instructions performed by the application code.


In the scope of SQL Server, a request is the logical representation of a query or batch. A request also represents operations required by system threads, such as checkpoint or log writer. Requests exist in various states throughout their lifetime and can accumulate waits when resources required to execute the request are not available, such as locks or latches.


A task represents the unit of work that needs to be completed to fulfill the request. One or more tasks can be assigned to a single request.


A scheduler, also known as SOS scheduler (meaning SQL OS scheduler but never actually referred to as such), manages worker threads that require processing time to carry out work on behalf of tasks.


The amount of time that a thread is permitted to run on the processor before it must yield to another thread and return to the Runnable Queue or the Wait List.

Scheduler Components

At a high and somewhat abstracted level, schedulers have three main components: the Processor, Waiter List and Runnable Queue.

The Processor is the logical processor core which processes the threads one at a time. Each scheduler is mapped to a single processor.

The Waiter List represents the list of threads that are waiting for resources (such as latches and locks).

The Runnable Queue represents the group of threads that are not waiting on resources (as they have already acquired them) and are instead are waiting for their turn to run on a processor.

The thread of a process will move between these three states as they get executed. The threads are always either doing work on the processor, waiting for a particular resource, or they have a resource and are waiting in line to run on the  processor again. Once a thread is running on the processor, it will continue to do so until either it has finished its work and needs to again wait for another resource OR it reaches its maximum allotted time on the processor (called the OS quantum). This Quantum value is 4ms is non configurable.

Thread States

At any given time a thread can be in one of three states: RUNNING, SUSPENDED, or RUNNABLE.

RUNNING is the state where the thread is actually executing on the processor and utilizing CPU spins. Only one thread per scheduler can have this state at a time.

During execution, if the thread needs to wait for a particular resource in SQL (such as a lock or latch), it yields its time on the processor and moves to the Waiter List. When here the state of the thread changes to SUSPENDED while it waits for the resource to be acquired.

Once the resource has been acquired the thread is again ready to use the processor again but must wait its turn behind the other threads waiting to execute so it moves to the Runnable Queue and its state is changed to RUNNABLE.


If a RUNNING thread reaches the end of its 4ms quantum and doesn't need to wait to acquire another resource, voluntarily yields for the next thread and enters the Runnable Queue where its state changes back to RUNNABLE.

This dance continues until the thread(s) complete all the work assigned to them. This is called cooperative or non-preemptive scheduling. It relies on all of the processes at play voluntarily yielding when they are supposed to. For a comparison see this post on Preemptive vs Cooperative Multitasking.

Transactions and ACID

A database transaction is a unit of work performed against a database that is independent of other transactions. The purpose of a transaction is two-fold:

  1. To provide a reliable record and means to recover in case of failure when performing work.
  2. To provide isolation between applications or processes that are accessing data concurrently.

In any good relational database management system, transactions adhere to a set of strict rules or properties abbreviated as ACID:

  • Atomicity
  • Consistency
  • Isolation
  • Durability


Guarantees that either all operations in the transaction succeed or they all fail. This prevents partial updates that could leave a database in a questionable or incomplete state. For instance, imagine your database transaction was a financial transaction that transferred money from one account to another. That would consist of two operations: a deduction from account A and a deposit into account B. This is an obvious example where we want both operations to succeed or both to fail. If only one operation was successful we would either be destroying money or inventing new money from nothing (something apparently only the federal reserve is allowed to do).


Is similar to atomicity but has more to do with referential integrity. This is where constraints come into play, such as a primary key. If you have an ID column used to track rows based on a unique identifier, you wouldn’t want to allow multiple rows to have the same value for ID; if there were the database would be in an inconsistent state. A transaction that preserves consistency ensures that the database is in a consistent state when the transaction begins and when the transaction ends.


This protects transactions from the effects of other transactions via mechanisms like locks and row versioning. Database management systems typically employ different levels of isolation. For instance, in Microsoft SQL Server, the READ UNCOMMITTED isolation level allows transactions to read data that other transactions are modifying, while the stricter SERIALIZABLE isolation level will lock ranges of rows or entire tables to prevent the reading of data until the transaction modifying data completes and commits.


A transaction is considered durable when it commits and stays committed. This is accomplished by flushing the data to a stable/nonvolatile media (think disk vs in-memory) although as storage mediums continue to advance and innovate, the implementation may change. The important thing is that the work is saved and is not prone to being lost or rolled back without intervention.

Microsoft SQL Server, Oracle, MySQL, and PostgreSQL are some of the most popular database systems which follow ACID properties by default.