Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Understanding SQL Server Schedulers, Workers and Tasks


By:   |   Last Updated: 2016-08-11   |   Comments (1)   |   Related Tips: 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


Last Updated: 2016-08-11


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

  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. 


Learn more about SQL Server tools