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 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