What does the status column of a session mean in SQL Server?

You might notice there is a status column when you look at active executing requests in either sys.dm_exec_requests, sys.dm_exec_sessions, or sysprocesses.  The status column indicates a general status of what the session is doing.


Usually these sessions aren’t a concern.  Not much interesting happening as they are typically housekeeping tasks.


You’ll see this status while a session that was executing a DML request was rolled back.  You might see this in a rollback state for an extended period of time if the update that modified a lot of rows.  If you kill a session you might see this pop up since killing the session will rollback changes.


This means a query is actively running and consuming CPU.  This is the period of time when a query is not yielding.  SQL Server has sessions yield to prevent a single thread/session from hogging a CPU (AKA non yielding scheduler).  SQL Server typically yields every 4 milliseconds.


This indicates the query is yielding CPU and the session is in the runnable queue.  This means the session isn’t waiting on anything other than to get a turn at CPU.  If you see a lot of sessions in a runnable state you might want to check if your server is under CPU pressure.  If sessions are spending a lot of time yielding it’s an indicator that there are a lot of sessions that need a turn at using CPU.


This is exactly what is sounds like.  The session is doing nothing.  This is not uncommon if you are pooling connections.  For the most part you should not see this in user sessions (sys.dm_exec_sessions.is_user_process=0 or session_id>50).  Typically these aren’t a concern, but this may be of interest if you are diagnosing a blocking issue and the root blocker is a session in a sleeping status.  When this happens it’s either a really busy app server holding a transaction open or it’s someone walked away for a cup of coffee while they didn’t commit a transaction in SSMS.


When a query executes it does one of two things: consume CPU or wait.  When a query waits on something it goes into a suspended status.  When you see this you should be looking at the wait type associated.  Some examples of waits would be lock waits (LCK*), physical IO waits (PAGEIOLATCH*), memory wait (RESOURCE_SEMAPHORE*).  There are a lot of different wait types and how you tune them depends on the wait type.