The DMV diaries: Worker, task, request and session state

We have all been there. You believe that a certain status (e.g. is the order shipped?) lives in a simple database column, only to find that it comes from a view built on a view with all kinds of creative CASE statements. And it may look ugly, but at the end of the day, you have to admit that it successfully serves the purpose of exposing business data in the way that users expect to see it.

Guess what: The “V” in “DMV” exists for a similar reason. Today I’ll be whizzing through the various ways in which the status of a running piece of work is exposed to us in sys.dm_os_workers, sys.dm_os_tasks, sys.dm_exec_requests, and sys.dm_exec_sessions.

sys.dm_os_workers: state

The Worker class has an enumeration member used in SQLOS worker management and scheduling, and this enum has four defined value:

• 0 – Init
• 1 – Running
• 2 – Runnable
• 3 – Suspended

As such, sys.dm_os_workers directly exposes a class member and there is no translation involved – this DMV tells it like it is.

One of the many flags within the Worker class that also gets exposed in sys.dm_os_workers is is_sick – if you’re interested, this is one of the bits within the opaque member we know as status. As I have previously described in coverage of spinlocks, when a worker keeps trying unsuccessfully to acquire a spinlock, after a very long time it goes to sleep without passing scheduler ownership to a sibling worker. However, before it does, it self-certifies as “sick” by setting this flag. We’ll see this flag rearing its head in sys.dm_os_tasks below.

sys.dm_os_tasks: task_state

This DMV starts to show its “view” nature, bringing together data from different sources in a way that preserves familiarity with the old sysprocesses, but moves away from literally exposing members of a single underlying class. Internally, an SOS_Task only has three states, although these aren’t exposed in this form in a DMV:

• 0 – pending
• 1 – in progress
• 2 – done

When the task is in the pending or done states, there is no worker bound to it, and those states are exposed in the DMV. However, once the task is bound to a worker, we expose the underlying worker’s state, which is more or less the same as we see in sys.dm_os_workers.state.

“More or less” gets one modification though: if the worker is in the running state but also has the sick flag set, we report a task state of spinloop instead of running.

sys.dm_exec_sessions: status

This metric is completely disjunct from the above ones, and mostly reflects attributes of a CSession class instance. The respective values are derived through the following decision tree:

• If the internal Boolean member m_fIsConnReset is set, return dormant
• Else if a flag living outside of CSession itself is set, return preconnect (I’ll touch on the source of this mystery flag below)
• Else if a flag within the CSession itself is set (indicating that it has been provided some work to do) return running
• Else return sleeping

What is interesting here is that we have the name running used by different DMVs to mean different things. It’s worth remembering that there is no relationship between those meanings of “running”.

sys.dm_exec_requests: status

This is the Big One, pulling in data from all the above sources. Its data source is based on a subset of sessions, specifically the ones that have requests (and hence tasks) bound to them. For this reason, it covers only those sessions that would show up with a status of running in sys.dm_exec_sessions.

The data source for the derivation of a status value is a combination of the CSession instance and the primary SOS_Task executing the current request, plus the same mystery member that yielded preconnect above. Derivation goes as follows:

• If the session’s m_fBackground flag is set, report background
• If a flag is set in the “mystery member”, return rollback
• Otherwise, using the combination of task and underlying worker state, return the exact same value that would come from sys.dm_os_tasks

The above implies that sys.dm_exec_requests should provide a superset of the statuses exposed by sys.dm_os_tasks, although documentation omits a few.

Mystery members, mental models and DMV construction sets

Okay, so there is one member whose provenance I’m unsure of, though I hope to figure it out soon. What I can tell you is how these things get presented to the two dm_exec DMVs.

There exists a helper class CDiagnosticsBase, with subclass CDiagThreadSafe, in sqllang. Alongside further helper classes, this serves as a construction set for DMVs, allowing some shared code where different DMVs expose the same data. Its iterator helper glues together classes like CSession, CSQLSource, CDbAndSetOpts and CDbccInputBuffer to provide a unified view of that problematic beast we like to think of as a “spid”. The flags that indicate Preconnect and Rollback come from one member pulled into CDiagnosticsBase.

This little exploration has helped to clarify one thing for me. Whenever you think you know how something really works, there is a good chance that you are only being showed a model that may not reflect the current implementation, but is rooted in a need for mental compatibility with a legacy implementation.

Sysprocesses is dead, long live sysprocesses!