Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding and Using sys.dm_exec_sessions in SQL Server


By:   |   Read Comments   |   Related Tips: More > Functions - System

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

I know SQL Server has a lot of system 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 sessions connected users and applications. How can I see this information?

Solution

The Dynamic Management View (DMV) sys.dm_exec_sessions reports all sessions within SQL Server. These include internal processes. For instance, the following query will tell us of processes that are created by SQL Server:

SELECT session_id, login_time, security_id, status
FROM sys.dm_exec_sessions
WHERE host_name IS NULL;

The key is the host_name being NULL. There are quite a few. SQL Server has multiple internal processes that run and perform work. In this query’s results, we see 40 rows:

Sessions created internall by SQL Server

However, the vast majority of the time we’re not concerned with internal processes. We’re more concerned with connections into SQL Server and what they’re doing. So let’s start with a simple join with sys.dm_exec_connections to see these.

SELECT DEC.session_id, DEC.protocol_type, DEC.auth_scheme,
  DES.login_name, DES.login_time
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

And we will see a few connections. This is how we can quickly get related connections to the logins, something we’re not able to do with just sys.dm_exec_connections.

sys.dm_exec_sessions joined with sys.dm_exec_connections

Note also that we don’t see 40 rows. We only see a handful. That’s because sys._dm_exec_connectionsonly returns connections into SQL Server. Therefore, when we join (with an INNER JOIN) sys.dm_exec_sessions to sys_dm_exec_connections, we will automatically filter out the internal sessions.

Seeing What Is Connected to SQL Server and Via What Client Interface

We’ll keep the join in the remainder of the examples because likely you will use a combination of sys.dm_exec_connections and sys.dm_exec_sessions info in your troubleshooting. However, for the purposes of these examples I don’t need any further information from sys.dm_exec_connections because I’m demonstrating what we can find in sys.dm_exec_sessions. Thinking along those lines, it’s often important to note who is connecting, what they’re using to connect, and what client interface they are using

SELECT DES.session_id, DES.login_name, DES.program_name, DES.client_interface_name
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

For instance, if users aren’t supposed to connect via Excel to a database but I believe someone who has the permission to do so has broken their rule, I can typically see something that would give me cause to investigate further using this query. As we can see here, someone has connected using Microsoft Office 2010 (the FromExcel login):

What application and how it connects

This doesn’t tell us for sure that it’s Microsoft Excel, but it’s a starting point. If we shouldn’t see anything but connections via a particular application and perhaps some administrative logins via SQL Server Management Studio (SSMS), then we know that there is an issue. There is a catch with the program_name value. This is something the application defines. Therefore, it is possible for a savvy user to spoof a different name. This can be done, especially through the use of File DSN created through Data Sources (ODBC) under the Administrative Tools for the OS.

Note also that we do see through what library/interface the session has been made. In this case we see that the two connections via SSMS were made with the .Net SQLClient Data Provider. We see the interface from Office was OLEDB. If you’re trying to troubleshoot a particular issue that you can tie to a client interface, this is helpful information.

Seeing SQL Server Client Session Settings

Another example of something we might want to troubleshoot is if a user is having trouble inside a particular database, especially if they are saying queries we know should be working aren’t. A common example is when NULLs start behaving differently than we expect.

SELECT DES.session_id,DES.login_name, DB_NAME(DES.database_id) AS DB,
  DES.date_format, DES.quoted_identifier, DES.ansi_nulls
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

If we look at the results of this query for the example I’ve generated, we’ll see something interesting:

session id

Note that one of the DemoLogin entries is in the AdventureWorks2014 database and that its ansi_nulls setting is 0 (OFF). This is different than the default, which is 1 (ON). The session specific settings like what date format the session is using (here all are using mdy, the US default format), how nulls are handled, if they are using quoted identifiers for queries, etc. can all be found using sys.dm_exec_sessions. As a result, if you have users with strange results on their queries yet the queries run perfectly fine for everyone else and it’s not a permissions issue, checking session settings may uncover the root cause.

Next Steps


Last Update:


next webcast button


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.



    



Learn more about SQL Server tools