Connection vs Session_ID

Each session is associated with to connection. A session_id, or SPID, is assigned to each connection. The SPID remains constant to that connection for its lifetime.

You can see the connections it the dynamic management view: sys.dm_exec_connections.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Connections and sessions

SELECT session_id,connect_time,protocol_type,num_reads,num_writes,last_read,last_write,client_net_address
FROM sys.dm_exec_connections;
go
session_id  connect_time            protocol_type                            num_reads   num_writes  last_read               last_write              client_net_address
----------- ----------------------- ---------------------------------------- ----------- ----------- ----------------------- ----------------------- ------------------------------------------------
51          2008-05-18 17:02:58.840 TSQL                                     30          30          2008-05-18 17:03:02.770 2008-05-18 17:03:02.770 <local machine>
52          2008-05-18 17:03:10.620 TSQL                                     12          11          2008-05-18 17:06:25.357 2008-05-18 17:06:08.350 <local machine>

(2 row(s) affected)

The import points are:

  • num_reads with the last_read: was the session idling?
  • num_writes with the last_write: was the session idling?
  • client_net_address: IP address of the client