Return SQL Server Connections Information Using sys.dm_exec_connections

By:   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dynamic Management Views and Functions


I know SQL Server has a lot of views and functions which I can use to understand what’s going on with my SQL Server. I’m interested in seeing what SQL Server can tell me about the users and applications which are connected. How can I see this information?


The Dynamic Management View (DMV) sys.dm_exec_connections reports all connections into SQL Server. The into is important because SQL Server doesn’t include internal processes. This makes sense as they aren’t connected to SQL Server.

Return Basic Current SQL Server Connection Information

Let’s take a sample query and see what it tells us:

SELECT session_id, connect_time, net_transport, auth_scheme, client_net_address 
FROM sys.dm_exec_connections

We’re getting some useful information with only a few columns: the session ID (which we can tie to other) views, when the connection was initially made, how the connection was made (what protocol), how the connection authenticated, and what IP address the client is coming in from, all of which can help us track down a particular connection if we needed to troubleshoot one.

Initial query against sys.dm_exec_connections

With this query we find that we have a mix of transport protocols, authentication schemes, and what appears to be two different systems connecting. It’s not too different systems, however, as is localhost, or the local machine. The reason we’re seeing a difference is because with TCP as the transport protocol we need an IP address. With shared memory it has to be the local machine.

However, we see more information: two different types of authentication schemes: SQL and NTLM. SQL is self-explanatory. The connection was made using a login created within SQL Server. NTLM, however, means we have a Windows user connection. It’s important to note that it is an NTLM connection and not a Kerberos connection. If all you see if NTLM connections, you can never use the features available with Kerberos, such as Kerberos delegation. Kerberos delegation comes into play when you have to pass credentials across systems. For instance, you are connecting to SSRS with a web browser and your Windows user account is what determines your security against the SQL Server database. In this case, you have to have SSRS be able to pass your credentials on to SQL Server, we call this delegation. However, if we aren’t able to make a Kerberos connection to SQL Server, we can’t do this. So auth_scheme is important.

See What SQL Server Connections Have Been Utilizing SQL Server the Most

This DMV, sys.dm_exec_connections, can also show us how many reads and writes each connection has performed as well as when the last of each occurred. Needless to say, this can be useful information if you’re trying to find the connection and process (session ID) that’s beating up one of your SQL Servers:

SELECT session_id, num_reads, num_writes, last_read, last_write 
FROM sys.dm_exec_connections

The results show that one of our existing connections is a bit more active than the others. This is a demo system, so none of the numbers are significant. On a production system, especially a heavily used one, you’d expect to see a lot higher number of reads and/or writes.

Read and Writes from sys.dm_exec_connections

In any case, this allows us to see if particular connections, especially newer connections have a high number of reads/writes. If so, that may be the problem child(ren) we’re looking for if we’re troubleshooting  an issue in real-time.

Return Last SQL Server Command for Current Connections

One last thing we’ll look at with sys.dm_exec_connections. That is the most_recent_sql_handle column. It contains a varbinary value that we can use with a different dynamic management object, sys.dm_exec_sql_text. In this case we’ll need to use CROSS APPLY and pass the handle in. Here’s how we can do so:

SELECT DEC.session_id, T.text AS 'SQLQuery'
FROM sys.dm_exec_connections AS DEC
CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS T

Here are out results:

Finding last SQL query by using the sql_handle

If we compare with our previous results, we now can understand why session 51 had the most I/O. What is interesting is that session 51 issued a SELECT statement. However, it has a high number of writes. Unfortunately, sys.dm_exec_connections won’t tell us why. However, by using other DMVs, which we’ll cover in future tips, we may be able to tell. In this case, session 51 was Excel making a data connection to SQL Server. It’s entirely possible that data was written to TempDB.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 8, 2021 - 4:55:36 PM - Maurice Pelchat Back To Top (88359)
My first guess would be to suppose that de dmv impersonate another less privileged login for which the view_server_state was withdrawn.

Monday, May 21, 2018 - 9:40:28 AM - Mark Anderson Back To Top (75991)

why do i get this error even when logged in as sa?


Msg 300, Level 14, State 1, Line 2

VIEW SERVER STATE permission was denied on object 'server', database 'master'.

Msg 297, Level 16, State 1, Line 2

The user does not have permission to perform this action.

get free sql tips
agree to terms