2

I am confused about the connection_id column of sys.dm_exec_connections DMV:

SELECT connection_id FROM sys.dm_exec_connections;

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only? And multiple New Query windows would represent different sessions represented by different session_ids or @@SPIDs?

In particular, why does the result set of the above query return an different uniqueidentifier for each session (and sessions that do seem to be currently open) of this same server connection?

Is this connection the same as the physical connection to an instance of SQL Server?

Thanks

2

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only?

Well, I think it would help to first be clear about what you actually mean by "connect to an instance". I will assume that you are referring to starting up SSMS and it asking you to connect to an instance. When this happens, it is, at the very least, Object Explorer, that is making the connection. And when it first connects, it appears to use between 2 and 4 connections / sessions. After a while, it appears that all but one of those connections drops off. These connections are different threads of Object Explorer, gathering info to display. And the behavior of the connections sticking around for a while suggests that connection pooling is being used.

If you have the "Object Explorer Details" tab, then that can use another connection or two, especially if you are clicking on various objects to get per object details at the bottom.

Each connected query window will be yet another connection.

Open up a "properties" window (such as right-clicking on a database within Object Explorer and selecting "Properties" to open the "Database Properties" dialog) and that will be another 1 or 2 connections.

And if you have IntelliSense enabled (it is by default), then sometimes it will connect to gather info and then disconnect.

You can see these behaviors by doing the following test:

  1. Close SSMS completely.
  2. Open SSMS, but when prompted to "Connect to Server", hit the "Cancel" button.
  3. Click the "New Query" button and now connect to the server.
  4. Immediately paste in the following query and hit F5:

    SELECT conn.[connection_id], sess.[program_name], txt.[text] AS [most_recent_batch],
           '---' AS '---',
           sess.*,
           '---' AS '---',
           conn.*
    FROM   sys.dm_exec_sessions sess
    LEFT JOIN sys.dm_exec_connections conn
           ON conn.[session_id] = sess.[session_id]
    OUTER APPLY sys.dm_exec_sql_text(conn.[most_recent_sql_handle]) txt;
    

    This query shows all sessions and their respective connections (if they have one). It also shows the most recent query batch to help see how that connection is being used.

  5. Scroll to the bottom of the result list and you should see one row with the following value for "program_name":
    Microsoft SQL Server Management Studio
    (I believe it should actually be "Microsoft SQL Server Management Studio - Query", but this is how it is working in SSMS 17.0)

  6. Now hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
    Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
    Don't wait too long between executions or else you might miss it.
  7. Click on the "New Query" button, go back to the first query tab, hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
    Microsoft SQL Server Management Studio - Query
  8. Go back to the recently opened query tab, paste in the query shown above (in step # 4), close the original query tab, hit F5, scroll to the bottom of the result list, and you should see one row with the following value for "program_name":
    Microsoft SQL Server Management Studio - Query
  9. Go to the Object Explorer panel and connect to the instance.
  10. Go back to the query tab, hit F5, scroll to the bottom of the result list, and you should see at least two rows with the following value for "program_name":
    Microsoft SQL Server Management Studio
    If you have the "Object Explorer Details" tab, then there should be another connection for that (i.e. at least three non-"- Query" connections).
|improve this answer|||||
  • oh sure... take the time to actually write a really good answer! :-) – Max Vernon Jun 20 '17 at 19:01
  • @srutzky Thanks for your answer. I worked through all the steps you provided except the IntelliSense one. (Could't achieve that.) My understanding after this, is that various "components" of SSMS will connect to the server to retrieve info using their own connections which are handled by worker threads on the server side. (Which is roughly the same as Max said, I supposed?) So they are all PHYSICAL connections? Then how do those connections relate to sessions? – John Smith Jun 21 '17 at 3:40
  • @JohnSmithSr. There are multiple threads on both sides: client (SSMS) and server (SQL Server). That is why / how there are multiple sessions going on at one time, and why you can have all of the query windows executing at still poke around Object Explorer while still pulling up a properties window and having that do something. Not sure what the distinction is between "physical" connection and some other kind, but connections are tied to a session, though clearly some sessions can exist without a connection. – Solomon Rutzky Jun 21 '17 at 5:31
1

Connecting a modern version of SSMS will cause multiple connections to be opened to the server since SSMS uses background connections to enumerate various items from the instance and its databases. There will also be at least one connection per query window you open.

Each row in dm_exec_connections represents a single physical connection to the server. Each session has a single connection. See https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-connections-transact-sql for details.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.