An Overview of SQL Server Task Scheduling

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks.

On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads.

You can view the schedulers on your SQL Server by running this code:

[sourcecode language=’sql’]
SELECT * FROM sys.dm_os_schedulers;
[/sourcecode]

Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there:

• VISIBLE ONLINE (DAC) – thread scheduler used to manage the DAC (Dedicated Administator Connection)
• VISIBLE ONLINE – user thread schedulers
• HIDDEN ONLINE – internal operation thread schedulers

So depending on how many CPUs you have in your SQL Server, you will see from this DMV that there can be quite a few schedulers available.

More CPUs = more schedulers = more work can get done 🙂

There are some other status’ which you might see:

• HOT_ADDED – schedulers created in response to a CPU which was added whilst the server was online (hot added CPU)
• VISIBLE OFFLINE – user schedulers which are mapping to CPUs which are offline in the affinity mask.
• HIDDEN OFFLINE – schedulers assigned to internal operations mapped to CPUs which are offline in the affinity mask.

Thread status

If you’ve ever looked at your process list in SQL Server either by using sp_who2 or activity monitor, you may have noticed that there is a column in there called “Status” (activity monitor in Management Studio 2012 calls this “Task State”)

There are some status’ which help you to understand how the queries execute, they are RUNNING, SUSPENDED and RUNNABLE

RUNNING – the thread is actively running on the CPU.

SUSPENDED – thread is waiting for a resource to become available. Thread is on the “waiter list”.

RUNNABLE – resources are now available to the thread but as only one thread can execute on a CPU at any given time, the thread has to wait its turn. It goes to the bottom of the “RUNNABLE queue” and finally returns to a RUNNING state when it is its turn to run as controlled by the CPU scheduler.

It goes to the bottom of the “RUNNABLE queue”

Please note that this behaviour can change if using RESOURCE GOVERNOR (Enterprise Edition)

The size of the “RUNNABLE queue” for each scheduler can be seen by looking at the value for the “runnable_tasks_count” column from the sys.dm_os_schedulers DMV.

Threads can transition around the different states until their work is completed. When on the waiter list, a thread can wait for some time before it changes state. There is no set order in which they are then changed from SUSPENDED to RUNNABLE.

In order to view what each thread is waiting for, you can look at the sys.dm_os_waiting_tasks DMV:

[sourcecode language=’sql’]
SELECT * FROM sys.dm_os_waiting_tasks;
[/sourcecode]

 Summary points

• Each CPU (logical or physical) is assigned a scheduler.
• Only one thread can be running on a CPU at any given moment.
• Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
• Use sys.dm_os_schedulers to view schedulers on your SQL Server.
• Use sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available.