In SQL Server, there are a few DMVs that tell us “What is running now”. This is built into SSMS as Activity Monitor, but I’ve never met anyone who actually finds that method useful, there’s also sp_who and sp_who2, but they are dated and limited in what they tell you. Instead, the more robust way is to use the DMVs (Dynamic Management Views) to see what is going on. DMVs give us a peek at what is happening under the hood of SQL Server and there are a few that help us when we want to see what’s running on our server. Let’s look at these DMVs and how they piece together what is running.
sys.dm_exec_connections
This DMV returns one line for every physical connection into SQL Server, i.e. something that connects into SQL Server on a network protocol such as TCP. If we do a straight SELECT * from it, we can see a number of connections:
SELECT * FROM sys.dm_exec_connections;
Each row shows various useful networking and other information for a given connection. If I open a new SSMS tab on this server, it gets a new session ID, AKA SPID (Server Process ID). We can verify the session ID using:
SELECT @@SPID;
If I run the SELECT * again, I get another row which is session 120, the one we just opened and we can see I’ve connected from 192.168.2.181, the server address is 192.168.2.23 and the connection is on port 1433:
sys.dm_exec_sessions
This is similar to connections, but while sys.dm_exec_connections is the physical connection, a session is more of an information exchange process within SQL Server. I think of it like a telephone conversation (remember those?) – the session is our phone call and the connection is the underlying telephone network connection that allows us to undertake our telephone call session and exchange information.
In SQL Server, generally, each connection will have a session (exceptions noted here), though a session may not have a connection. A couple of connections without sessions are shown in the SELECT * FROM sys.dm_exec_connections output above, using the database mirroring protocol, though I am not exactly sure why the database mirroring connection does not have an associated session. As for sessions, there are a number of sessions that will not have a connection, these are SQL Server’s internal sessions and background processes.
Let’s have a look at the view:
SELECT * FROM sys.dm_exec_sessions;
Again, there’s quite a lot of information here, and there is a lot more that won’t fit on my screen.
Firstly, any session with an ID of 50 or below is an internal SQL Server session, reserved for background processes (Checkpointer, Log Writer, Lazy Writer etc).
Let’s narrow it down to those that are user sessions:
SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50;
We can see a number of results have NULL hostnames, program names etc and each of those corresponds to a row where is_user_process = 0. But wait, I thought system processes were <50? Well, yes, but these are sessions that are used by SQL Server features rather than the core engine, they are described by the documentation as internal sessions. If we remove these, we get to see just the “true” user sessions:
SELECT * FROM sys.dm_exec_sessions WHERE session_id > 50 AND is_user_process = 1;
sys.dm_exec_requests
Every row in this DMV returns an executing request which the documentation describes as “a logical representation of a query or batch” I just think of it as something is running in a session (i.e. we have an SSMS window and our query is executing, rather than sitting there with a load of text in the window, waiting for us to hit execute) going back to our telephone analogy, I would describe this as when someone is actually talking, rather than the part of the conversation where there is awkward silence.
Let’s see what it looks like:
SELECT * FROM sys.dm_exec_requests;
Again, this view shows system sessions and we can filter them out by returning only those sessions with an ID over 50:
SELECT * FROM sys.dm_exec_requests WHERE session_id > 50;
Note that we can see 2 sessions with actual commands running (these are the sessions that are is_user_process = 1 in dm_exec_sessions)
Putting it all together
Now we know what each of these three views do, let’s pull it all together to see what users are currently running on our system:
SELECT *
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
WHERE s.is_user_process = 1 AND
s.session_id <> @@spid;
We’ve cut through all that noise and can see that there’s only one user session currently executing a query on this system (two actually, but one of them is the session I ran this query from, so we filtered that out).
What the query above has given us:
- Currently executing queries…
- Where the session is a user process…
- Which is a user initiated session
Rather than SELECT *, we can return a smaller, more succinct list:
SELECT s.session_id,
s.database_id,
s.host_name,
s.login_name,
c.client_net_address,
s.last_request_start_time,
s.reads AS TotalSessionReads,
s.cpu_time,
c.connect_time,
r.reads AS ThisRequestReads,
r.command,
r.sql_handle
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
WHERE s.is_user_process = 1 AND
s.session_id <> @@spid;
We can see here that the user session is number 113. It is connected to the master database (Database ID 1) we can see the host and IP where the user is logged in from. We can also see that the connection has been open since 23:28 but the current running request started at 23:53, indicating the session was either sleeping (open but doing nothing – waiting for a request) and / or running other requests in the time between. We see the command type (it’s a WAITFOR to make this demo easier!) and we also get a SQL handle at which point another DMV (actually a DMF – Dynamic Management Function) enters the chat…
sys.dm_exec_sql_text()
This function takes a SQL handle as a parameter and returns the query text for that handle. We can apply it as below and see the query being executed:
SELECT s.session_id,
s.database_id,
s.host_name,
s.login_name,
c.client_net_address,
s.last_request_start_time,
s.reads AS TotalSessionReads,
s.cpu_time,
c.connect_time,
r.reads AS ThisRequestReads,
r.command,
t.[text]
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1 AND
s.session_id <> @@spid;
Here’s what it looks like if we run a somewhat more realistic workload:
From here, there are other things we probably want to join in – wait stats and execution plans, but I’ll stop here and leave those as an exercise for the reader.
Conclusion
In this post, we’ve looked at some DMVs where SQL Server exposes activity information to us and we used them to build a query which shows us what’s happening on the server. In reality, I use community tools such as sp_whoisactive or sp_blitzwho, however, I still find it useful to understand how these DMVs work in case I’m working on a server that doesn’t have these installed, but also because it’s useful for better understanding what it is we’re looking at and how SQL Server works.
References / Further Reading
dba.stackexchange – Why Am I Getting TCP Connections Without Sessions
Microsoft – Thread and Task Architecture Guide
Microsoft – sys-dm-exec-sessions
Microsoft – sys-dm-exec-requests