sys.dm_os_schedulers – Day 16 – One DMV a Day

Hello Geeks and welcome to the Day 16 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Last two days I have covered sys.dm_os_workers and sys.dm_os_threads and a bit more insight into scheduling. Toay I will be talking about one more important DMV related to OS, sys.dm_os_schedulers. This is one DMV which can be used for multiple purposes and is very informative.

Before we further discuss what information does sys.dm_os_schedulers provide, let’s discuss some more about the SQL OS. From 1993 – 1998 of SQL Server life the versions 4.x to 6.x used DataServer Semaphore for scheduling its requests. In 1998 with SQL 7.x Microsoft introduced User Mode Scheduler for SQL Server. This was there till SQL 2000. With SQL Server 2005 they have completely redesigned the engine and introduced SQL Servers own OS which does lot more than just scheduling.

Do you know that SQL OS was designed on a RDBMS research paper written by Michael Stonebraker!!!

The primary reasons why SQL OS was written are very clear.

1. SQL Server developers (the product developers at Microsoft) know what RDBMS needs.
2. Minimize SQL Server preemption.
3. Manage its own Scheduling, memory and IO.
4. Abstract layer that makes OS usage consistent for developers.

SQL OS is an engine having two DLLs. Till 2008 R2 it was part of the SQLServr.exe. But with SQL 2012 they made two separate DLLs called SQLMIN.DLL and SQLLANG.DLL. There are two more DLLs called SQLDK.DLL and SQLOS.DLL. They mainly contain the below code.

SQLMIN.DLL – E.g.: Checkpoint, LazyWriter, LockMonitor.
SQLLANG.DLL – E.g.: QueryProcessing Engine.
SQLDK.DLL – E.g.: Scheduling Services, Memory Services, Monitors, Xevent.
SQLOS.DLL – The main program for SQL OS.

These all can be viewed when you run windbg on any SQL Dump or attach it to SQL process using public symbols. Below is the screenshot from the session I gave on SQL OS.

So the main part of the SQL Server code which handles scheduling is in SQLDK.DLL. A scheduler is mapped to a CPU at the Windows level and based on the SQL Server affinity it will use the schedulers accordingly. Sys.dm_os_schedulers returns one record for each scheduler in SQL OS.

Sys.dm_os_schedulers exposes the information like status of scheduler, switches count for each category of tasks, workers count details, yield_count which is used internally by scheduler monitor to determine non-yielding scheduler and other generic information.

The status of a scheduler can be HIDDEN or VISIBLE, OFFLINE or ONLINE and two specific cases for DAC and HOT ADDED. HIDDEN schedulers can be used only for internal code of SQL OS. VISIBLE is used for scheduling user tasks. OFFLINE are the schedulers mapped to CPUs which are offline in the affinity mask. ONLINE are the available schedulers for SQL Server with online in affinity mask. DAC is dedicated for DAC connection and used only when DAC connection is made and hence only one connection is possible. HOT ADDED are for schedulers which are added when a hot add CPU event occurs.

The work_queue_count, runnable_task_count, current_workers_count and active_workers_count help us determine the contention with workers. The work_queue_count column should be zero for good workers availability. If this queue is non zero and increasing it indicates more tasks are coming in or a potential problem with one worker blocking others to complete.

Let’s create a scenario and see the output of sys.dm_os_schedulers where we will see a non-zero value in the work_queue_count.

I will start by reducing my workers to 255 and enabling DAC.

SP_CONFIGURE 'remote admin connections', 1
GO
RECONFIGURE WITH OVERRIDE
GO

SP_CONFIGURE 'max worker threads', 255
GO
RECONFIGURE WITH OVERRIDE
GO

Start a transaction with lock on a table.

BEGIN TRAN
DELETE FROM WorkersClog_tbl

Start 300 more batches to use up all workers. I will be using the ostess tool from RML utilities to do this. First save the simple select as below in a file e.g.: select.sql.

SELECT * FROM WorkersClog_tbl

Run the below command to start 300 batches of the above select.

"C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -E –S<InstanceName -iselect.sql -n300 -r10 -q

Now connect to SQL using a DAC and run the below queries.

SELECT work_queue_count,
       scheduler_id,
       current_tasks_count,
       runnable_tasks_count,
       current_workers_count,
       active_workers_count
FROM sys.dm_os_schedulers
ORDER BY 1 DESC

And below query shows that there are tasks waiting on THREADPOOL wait type.

SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type = 'THREADPOOL'

That concludes the contention on workers and we have successfully identified it using sys.dm_os_schedulers. All you need to do now is find out the reason for the THREADPOOL wait which in our case would show up as blocking and once you clear the blocking all the workers will be released. So you can use sys.dm_os_schedulers in multiple scenarios as it exposes loads of internal information happening with schedulers.

Tomorrow I will talk about another OS related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook