By: Daniel Farina | Updated: 2016-08-11 | 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
- You can get more information about schedulers and a few examples on using sys.dm_os_schedulers system view on the following tip: Detect SQL Server CPU Pressure.
- You don’t know what Dynamic Management Views are? This tip will explain this to: Dynamic Management Views.
- For more, check out the SQL Server Dynamic Management Views and Functions Tips category.
About the author
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
Article Last Updated: 2016-08-11
