In our last post, we discussed the advantages and disadvantages of SQL Server stored procedures. Today, let’s see how SQL server determine which sessions/connections are long connections.
How Does SQL Server Determine Which Sessions/Connections Are Long Connections?
In a database, there can be both long connections and short connections. A long connection refers to maintaining the connection state between the client and server for an extended period, in contrast to the typical short connection. If we don’t consider the application side, how can we identify which sessions in SQL Server are long connections?
In SQL Server, there are concepts of connections and sessions. Generally, a connection is a physical concept, while a session is a logical concept. They are two different levels of description for the same thing. Simply put, a connection is the physical communication link between the client and the server, while a session is the logical interaction between the user and the server. For example, on your computer, when you use the SSMS client tool to connect to the database, you can open multiple sessions, but there is only one connection.
To determine long and short connections, we need to use the login time of the connection rather than the login time of the session. In SQL Server, the login_time in sys.dm_exec_sessions indicates the time the session was established, while the connect_time in the DMV view sys.dm_exec_connections represents the timestamp when the connection was made. Therefore, to determine whether a database connection is long or short, you can use the connect_time field from sys.dm_exec_connections. For example, if a connection has been established for more than 5 minutes (this value can be adjusted based on actual situations or requirements), it can be classified as a long connection. You can use the following SQL query to retrieve information about long connections:
select s.login_name
, s.host_name
, c.client_net_address
, c.local_net_address
, c.connect_time
, s.session_id
, datediff(mi, connect_time, getdate()) as connect_duration_min
from sys.dm_exec_connections c
left join sys.dm_exec_sessions s on c.session_id =s.session_id
where c.session_id > 50 and c.local_net_address is not null
and datediff(mi, connect_time, getdate()) >5
order by connect_duration_min;
Gather information about the devices/terminals with long connections.
select distinct c.client_net_address,
(select host_name from sys.dm_exec_sessions s where s.session_id = c.session_id) as host_name
from sys.dm_exec_connections c
where c.session_id > 50 and c.local_net_address is not null
and datediff(mi, c.connect_time, getdate()) >5
order by client_net_address,host_name;
