The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
For my own edification I have been working on a simplified entity relationship map of some of the more popular DMVs. Yes I know Microsoft already makes this available. But working it out on my own makes it so much more permanent between my ears. Plus the Microsoft one I have only shows the PK and FK – not some of the more interesting columns in those views.
While working on the session related ones I.E. (dm_exec_connections, dm_exec_sessions, dm_exec_requests) questions started popping up in my head. Like what is the difference between a connection and a session? I thought I already knew … a connection is the actual user connection between the client and the server. The session is just what is going on internally to the sql engine. A clear 1 to 1 relationship. Isn’t it ?? According to the Microsoft schema map it isn’t.
The MSDN documentation confuses this a little bit. The dm_exec_connections article says session_id to connection_id is a one to one relationship. On the flip side, the dm_exec_sessions article details the relationship between these two tables as one to zero / one to many. Community comment demonstrates others are also confused. What gives?
If you read closely – this is not contradictory. The session article defines the cardinality when using the session_id to join the two views while the connection article was talking specifically about the relationship between a connection_id and a session_id. Joining the two tables on session_id can (and does) reveal one to zero or one to many relationships. Meanwhile a single connection_id can have one and only one related session_id.
The question however still remains. Can a single session have multiple connections?? Yes … uh, and no. If you think of a connection as an individual client connecting to a server then obviously one session cannot have more than one connection. The trick here is MARS. Multiple Active Result Sets. Under MARS one connection can spawn additional connections against the same session. In essence this is still the same connection (i.e. client) – but in practice (the DMV) is now tracking this as a separate connection linked to the original. So yes – one session can have multiple connections.
When querying the dm_exec_connection view there are two key fields that reveal this. One is the net_transport column. Per MSDN – whenever you see the word SESSION in this column it means this is a MARS connection. You can follow this MARS connection back to it’s parent with the parent_connection_id column.
Example I located on one of our busier servers to demonstrate. Well OK, Henry Lee located it, but like any good boss I will go ahead and take credit for it.
So, – can one session be linked to two connections? In the words of OBI WON – it’s all about your point of view. Life is full of ambiguities kid – get used to it. A better question is… can the dm_exec_session view have multiple related connections in the dm_exec_connections view when joining on the session_id field? That one is a solid YES – just like the MSDN documentation says.