sys.dm_exec_connections – Day 29 – One DMV a Day

Hello Geeks and welcome to the Day 29 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Till yesterday I have been writing about the memory related DMVs. Today I am going to start with other set of DMVs which are very useful in performance and other troubleshooting scenarios. I will be starting today with execution related DMVs. The first one will be sys.dm_exec_connections.

Have you ever been asked to check what connections are on the SQL Instance? What protocol are they using? The connection’s address and port number? When did it connected, last read and last written? This is a standard question either someone asks or you need to know when there is an issue reported as a first step. So let us start to see how we can use sys.dm_exec_connections.

SELECT session_id,
	most_recent_session_id,
	connection_id,
	connect_time,
	net_transport,
	protocol_type,
	encrypt_option,
	auth_scheme,
	node_affinity,
	num_reads,
	num_writes,
	last_read,
	last_write,
	net_packet_size,
	client_net_address,
	client_tcp_port,
	most_recent_sql_handle
FROM sys.dm_exec_connections

Almost all columns in sys.dm_exec_connections is very useful in answering above questions. Let us look at each column description. This will help you while troubleshooting scenarios.

session_id – The session which started this connection. This maps to the session_id in sys.dm_exec_sessions
most_recent_session_id – The session id used for most recent request for this connection.
connection_id – The id of the connection. This is used to map connection to request in sys.dm_exec_requests.
connect_time – Timestamp when the connection is made.
net_transport – The physical transport protocol used to connect.
protocol_type – This is the protocol of the payload on this connection. Currently shows TSQL (TDS) and SOAP protocols.
encrypt_type – Specifies if the connection is encrypted. TRUE or FALSE.
auth_scheme – The authentication mode used for this connection. Windows (KERBEROS/NTLM) or Mixed Mode (SQL).
node_affinity – The Memory node to which this connection has affinity. Refer my SQL OS presentation to understand Memory Nodes and affinity.
num_reads – Number of packet reads made on this connection.
num_write – Number of data packets that are written on this connection.
last_read – Timestamp when the last read occurred on this connection. Maps with the request from most_recent_session_id
last_write – Timestamp when the connection has last written the data packets on the target.
net_packet_size – The network packet size of the data and information transfer on this connection.
client_net_address – Host IP address of the connection.
client_tcp_port – port number associated with this connection on the client.
most_recent_sql_handle – SQL Handle associated with the recent request. This is always in sync with the most_recent_session_id.

The other important information sys.dm_exec_connections exposes is related to MARS (Multiple Active Result Set). MARS was implemented from SQL Server 2005. For every connection made to SQL Server from an application it may need multiple result set. SQL Server simulates MARS by opening a logical connection internally. Multiple requests in a batch interleave and perform their task using this connection. The column parent_connection_id is related to the parent of this logical connection. To learn more about how MARS works refer this link.

This is just a start to the execution related DMVs. To start with I used sys.dm_exec_connections, the most simple of all DMVs in this category. Tomorrow I will be covering another execution related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook