Understanding SQL Server Schedulers, Workers and Tasks

By:   |   Comments (1)   |   Related: More > Database Administration


Problem

You are learning about operating system related Dynamic Management Views in SQL Server and you are not sure about the differences between Schedulers, Workers and Tasks. In this tip we will cover those concepts and how they relate to each other.

Solution

If you want to succeed as a SQL Server Database Administrator (DBA) you must understand how SQL Server works. That’s what makes the difference between a good administrator and a great administrator, that’s why it’s important to understand how SQL Server processes its work.

Schedulers in SQL Server DMVs

A scheduler can be described as a piece of software that coordinates the execution of different processes and administers its available resources. SQL Server has its own scheduling mechanism that is implemented in what we know as SQLOS. The reason behind this is that the Windows scheduler cannot satisfy what a relational database needs for working. Windows uses a preemptive scheduling mechanism and assigns a quantum of CPU time to every thread, when a thread consumes its quantum it is sent to a queue and other threads are granted execution.

In opposition, SQL Server uses a cooperative scheduling mechanism when threads can voluntary yield its quantum of time (you can see this behavior when you have a SOS_SCHEDULER_YIELD wait type). This allows SQL Server to optimize CPU utilization, because when a thread is signaled for execution but is not ready to run it can yield its quantum of time in favor of other threads.

SQL Server has one scheduler per CPU core independently if it is a physical core or hyperthreaded. But the schedulers aren’t bound to a CPU unless you define an affinity mask. This means that a scheduler running on CPU 1, after a context switch can end up running on CPU 2.

SQL Server offers us the sys.dm_os_schedulers Dynamic Management View to monitor scheduler status.

Workers in SQL Server DMVs

Above I told you that the SQL Scheduler coordinates the execution of processes, the workers are these processes. You may be tempted to assume that workers are a synonymous with threads, but the concept of workers also includes Windows Fibers. Fibers are units of execution that are created inside a thread with the intention to avoid context switching, because Fibers run in the context of the thread that schedule them. We can think about fibers as mini-threads running inside a thread.

Fiber mode is enabled when you set the “Lightweight Pooling” server option to 1. Usually you won’t need to change this option, but if you do change this option know that there are some features of SQL Server that won’t work like CLR, SQLXML and SQL Mail.

There is a limit to the number of workers that can be handled by a scheduler depending on the “Max Worker Threads” server configuration option and the number of active schedulers.

You can use the sys.dm_os_workers Dynamic Management View to check workers status.

Tasks in SQL Server DMVs

As its name suggest, a task is a piece of work. You maybe thinking that a task is a query, but this is not completely true. When SQL Server runs a query it creates an execution plan that maps logical operations to physical operators like Scans, Lookups and Sorts to name a few. Each of those operators can be considered tasks.

With the help of the sys.dm_os_tasks System View you can see the status of each task on your SQL Server instance and information about what session is the owner of each task as well as the scheduler on which the worker that is performing the task request is running.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, May 12, 2018 - 12:35:35 AM - Ravi Kumar Back To Top (75925)

 

  Good article Daniel Farina. How does SQL OS scheduelr interact with OS scheduler ? After all SQL OS scheduler does not have any control on OS resources. 















get free sql tips
agree to terms