Anyone can explain the meaning of session states in SQL Server, such as sleeping, suspended, running etc

Question

Hi,

I'm confused about the session states of SQL Server sessions. Anyone can explain it please? Thanks.

Answer 1

If you refere to ASP.NET Session State using SQL Server then a session string is stored in a table in SQL Server. This is makes it possible to use the same session across multiple webservers. Otherwise when using ASP.NET InProc (In Process) session state, the system is storeing the session in a process on a singel maschine and can therefor not be used in a webfarm.

Answer 2

From MSDN:

• dormant = SQL Server is resetting the session.
• running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).
• background = The session is running a background task, such as deadlock detection.
• rollback = The session has a transaction rollback in process.
• pending = The session is waiting for a worker thread to become available.
• runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum.
• spinloop = The task in the session is waiting for a spinlock to become free.
• suspended = The session is waiting for an event, such as I/O, to complete.