Here’s a link to the Microsoft documentation on this DMV.
This is part 1 in a series I’m writing about my favorite Dynamic Management Views in SQL Server. I’m going to talk about how they’re useful, and then build on each post in the series by showing they’re connected.
Why it’s useful
Well, as the name implies, this DMV shows all the current sessions connected to your system. When I think of current sessions, I often think about queries that users are running on the server, but there’s also system activity. When I’m troubleshooting, sometimes I want to see both user and system activity, and this DMV allows me visibility to both.
Useful columns to query
login_name: Find the user who’s executing the session on your server
host_name: Find the computer that’s hosting the session
reads, writes, and logical reads: Useful for judging the total activity of one session
is_user_process: 0 for system processes, 1 for user processes
status: There’s a few different status but the most important ones are sleeping or running
program_name: The name of the program used to create this session. See the example below
Example query using this DMV
SELECT host_name, login_name, writes, reads, logical_reads, program_name
FROM sys.dm_exec_sessions as s
WHERE s.is_user_process = 1
and s.status = 'running'
ORDER BY s.writes DESC