Favorite DMVs: sys.dm_exec_sessions

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


By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close