sys.dm_os_workers – Day 14 – One DMV a Day

Hello Geeks and welcome to the Day 14 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.

Today I will be covering another OS related DMV sys.dm_os_workers. In the last blog I have explained that a task needs to worker to complete its work. So this DMV gives you more detail information related to Worker.

Worker is a structure which defines the capability of work. It is bound to a windows thread to do any work. A worker can move between schedulers when the CPU affinity is changed dynamically. The workers are ramped up when the SQL Server starts up and stay in work queue for further use. SQL Server does not kill the workers as creating them and destroying those needs resources and is not efficient.

You can manage the number of workers by changing the configuration parameter ‘max worker threads’. If this parameter is set to 0, which is default, the number of workers is calculated using the below formula.

32 Bit: No of Workers = (256 + (<processors> -4) * 8)
64 Bit: No of Workers = (256 + (<processors> -4) * 8) * 2

It is not preferable to change this parameter unless you are sure of the higher or lower number of worker threads. It should be well tested and proper reasoning with the work load and usage before implementing in Production.

Coming back to sys.dm_os_workers, this DMV exposes few interesting details about the workers. It can tell you when the worker was created, when was the task bound to this worker, when it went into RUNNABLE queue and when it started waiting, total worker quantum, current task quantum, number of tasks processed. Sys.dm_os_workers also provides many other details like if it is waiting on spin lock, any exception and its details, number of context switches, pending IO etc.

Yesterday I mentioned that the scheduling of SQL Server is non-preemptive. But there are few cases when the tasks can be preemptive. These can be the tasks which needs to be performed outside SQL Server. SQL Server handles preemptive tasks a bit differently. It marks them as preemptive and they wait on special waittypes like PREEMPTIVE_OS_xxx.

One good example is getting authentication from a domain controller which may be slow. SQL Server does not starve other tasks by not yielding a scheduler. So the thread places an OS request and pushes this worker back to waiting state. The interesting thing to observe is the state of this task will always be RUNNING. Other tasks switching to perform their task also do some additional work to check if any pending requests are complete (Cooperative). They help the task waiting in preemptive state to wake up when the request is complete from OS. If no other thread is active on a scheduler there will be an idle task which does this operation.

To understand this in more detail I have to write a whole series of blogs on SQL OS. I will do it some time. You can still get a peek from my presentation on SQL OS at Hyderabad event on Jan 25, 2014. 🙂

sys.dm_os_workers - SQL OS by Manohar Punna

Sys.dm_os_workers may not make much sense when run directly. So I will be joining it with sys.dm_os_tasks and sys.dm_os_sys_info. The first one is covered in my last blog and the second one is a simple DMV which give a record with all miscellaneous details about system information. Run this query and observe the output details. I have purposely filtered out is_preemptive as the end_quantum would be close to infinity.

SELECT ot.session_id,
	 ow.pending_io_count,
	 CASE ow.wait_started_ms_ticks
		  WHEN 0 THEN 0
		  ELSE (osi.ms_ticks - ow.wait_started_ms_ticks)/1000 END AS Suspended_wait,
	 CASE ow.wait_resumed_ms_ticks
		  WHEN 0 THEN 0
		  ELSE (osi.ms_ticks - ow.wait_resumed_ms_ticks)/1000 END AS Runnable_wait,
	 (osi.ms_ticks - ow.task_bound_ms_ticks)/1000 AS task_time,
	 (osi.ms_ticks - ow.worker_created_ms_ticks)/1000 AS worker_time,
	 ow.end_quantum - ow.start_quantum AS last_worker_quantum,
	 ow.state,
	 ow.last_wait_type,
	 ow.affinity,
	 ow.quantum_used,
	 ow.tasks_processed_count
FROM sys.dm_os_workers ow
INNER JOIN sys.dm_os_tasks ot
ON ow.task_address = ot.task_address
CROSS JOIN sys.dm_os_sys_info osi
WHERE ot.session_id > 50
AND is_preemptive = 0

Now you know what a worker is and how to use sys.dm_os_workers and a lot more about scheduling. 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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

One Comment on “sys.dm_os_workers – Day 14 – One DMV a Day”

Leave a Reply

Your email address will not be published. Required fields are marked *