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<br />FROM sys.dm_exec_sessions<br />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:
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,<br /> DES.login_name, DES.login_time<br />FROM sys.dm_exec_sessions AS DES<br /> JOIN sys.dm_exec_connections AS DEC<br /> 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.
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<br />FROM sys.dm_exec_sessions AS DES<br /> JOIN sys.dm_exec_connections AS DEC<br /> 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):
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,<br /> DES.date_format, DES.quoted_identifier, DES.ansi_nulls<br />FROM sys.dm_exec_sessions AS DES<br /> JOIN sys.dm_exec_connections AS DEC<br /> 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:
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
- Review the tip on sys.dm_exec_connections to see how you can use that DMV.
- Learn how to use sys.dm_exec_input_buffer to retrieve the last T-SQL command.
- Find out how to compare different session settings using sys.dm_exec_sessions.
