mssqltips logo

Return SQL Server Connections Information Using sys.dm_exec_connections

By:   |   Updated: 2018-05-18   |   Comments (1)   |   Related: More > Dynamic Management Views and Functions

Problem

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?

Solution

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 127.0.0.1 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


Last Updated: 2018-05-18


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools