Understanding sys.dm_exec_sessions in SQL Server

Question

Couple of questions on dm_exec_sessions. When I run the query

select * from sys.dm_exec_sessions 

it shows me among other columns a status and a transaction_isolation_level column.

It is my understanding that sys.dm_exec_sessions returns a row per authenticated session. In my understanding several queries/transactions can be run using this session.

1. What is the meaning of transaction_isolation_level that is returned per session? Is it the isolation level of the last transaction that was run on the database using that connection?

2. There are some (a couple) sessions that have a status of sleeping. What does that mean? Should we be worried about this? Are these transactions from the web server that have failed to rollback?

Answer 1

In my understanding several queries/transactions can be run using this session.

This is incorrect. You will always be able to execute at most one query and have at most one user active transaction in a session, never more than one of either.

A connection can have multiple sessions, but that is a different story. sys.dm_exec_connections

If you discover SERIALIZABLE sessions and you wonder why, then remember that using new TransactionScope() Is Considered Harmful

Answer 2

The session itself has a transaction_isolation_level. And batch/request running in that session will use that transaction_isolation_level unless it explicitly changes it.

A status of Sleeping just means that the session is idle and not currently running a batch/request. (I.E., it's waiting for it's client connection to send it a command to execute). It's not normally anything to worry about.

The official doc for sys.dm_exec_sessions is here: http://msdn.microsoft.com/en-us/library/ms176013.aspx