Sql-server – Confusion about connection_id in SQL Server DMV

dmvsql serverssms

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

Best Answer

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).
Related Question