sys.dm_exec_sessions – Day 30 – One DMV a Day

Hello Geeks and welcome to the Day 30 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have started the execution related DMVs. I have covered sys.dm_exec_connections. Today I will be writing about another execution DMV, sys.dm_exec_sessions. As mentioned in the last post, sys.dm_exec_sessions joins with sys.dm_exec_connections on session_id.

Sys.dm_exec_sessions covers most of the columns about a session. This DMV replaces almost all columns of sys.sysprocesses. The DMV is straight forward in output. This gives you the information at the session level and very less detail in terms of execution context.

In the below query I have included the most used columns from sys.dm_exec_sessions. I will be filtering out sessions whose ID < 50. There is myth around this boundary on session ID. Any system session will not be above 50. This is not correct. It is actually the other way around. No user session will have a session ID assigned less than 50.

SELECT session_id,
	 login_time,
	 host_name,
	 program_name,
	 host_process_id,
	 client_interface_name,
	 login_name,
	 original_login_name,
	 status,
	 cpu_time,
	 memory_usage,
	 total_scheduled_time,
	 total_elapsed_time,
	 last_request_start_time,
	 last_request_end_time,
	 reads,
	 writes,
	 logical_reads,
	 is_user_process,
	 transaction_isolation_level,
	 open_transaction_count,
	 database_id,
	 authenticating_database_id
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

sys.dm_exec_sessions

In the above output you will observe total of three session on my SQL Instance. Each program_name is unique in this case. The session with “Microsoft SQL Server Management Studio” is my connection to the instance using the SSMS. Another session which has program_name as “Microsoft SQL Server Management Studio – Query” is my query window from where I am running the query. If you observe this one has the status as running. This is because I am running the query from this query window on SSMS. Both my connection and query are happening from same SSMS. SSMS is a single process. So you will also notice host_process_id (7208) is same for both sessions 51 and 55.

There is another session, 52 which is coming from a SQLCMD. The host_process_id is 7852. I have made a connection to this instance from SQLCMD and I can see both these process IDs in my task manager. Make a note that this process ID is associated with the process on the host_name. So you need to check the task manager on the host from where the session is connected.

sys.dm_exec_sessions

Sys.dm_exec_sessions provides all this information and more. The other important columns would be to know the session properties like Isolation Level, any properties are SET like quoted_identifier, arithabort, ansi settings, etc.

Now you are aware of the usage of sys.dm_exec_sessions. Tomorrow I will be covering another execution related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “sys.dm_exec_sessions – Day 30 – One DMV a Day”

  1. To determine if a session is a User session, you can use:
    .
    .
    .
    FROM sys.dm_exec_sessions
    WHERE is_user_process=1

Leave a Reply

Your email address will not be published. Required fields are marked *