DMV for User Connections

There are 3 specific DMVs for user connections namely

sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_exec_requests

Let us each of the same in detail:

sys.dm_exec_sessions
This basically contains one row per session and is identified by session_id,this is equivalent to a SPID in SQL 2000

Also , in SQL 2000 system Session can be identified by SPIDs whose number fall below 50, but in SQL 2005 thats not the case and system sessions can be identified by column is_user_process
and has value zero if its a system session.And the Session_ID recorded in SQL Error log has 's' suffixed like spid43s in error logs

sys.dm_exec_connections
This can be called as a subset of the above DMV where this view contains only User connections filtered from the DMV sys.dm_exec_sessions.Each row in this table is uniquely identified by column connection_id which is a unique identifier.

Also,SQL 2005 supports MARS(Multiple Active Result Sets) where for each result set Ssession_ID is the same however connection_id is different , to identify the connection which resulted the result set can be identified using parent_connection_id column.

And one more nice value which is captured is the protocol used by the client to connect to SQL server which can be TCP/IP,Named Pipes,Shared Memory

sys.dm_exec_requests
This view reports the current active connections and the major information which is present in sysprocesses are present in this view.There exists a column named sql_handle if the value is not null then the query is still executing.

Once the session_ID is available we can find the underlying SQL command running using the DMV sys.dm_exec_sql_text

Use the below command to find the text
declare @sql_handle varbinary(64)
select @sql_handle = sql_handle from sys.dm_exec_requests where session_ID = 25
select * from sys.dm_exec_sql_text(@sql_handle)