Ewald Cress looks at a number of DMVs and how they expose query status:
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
It’s interesting to see how something so very similar can have so many different understandings.