SYS.DM_EXEC_SESSIONS and System Processes

Being able to gather information about current sessions has for many years been an important part of a DBA’s responsibilities when troubleshooting a performance issue.  The type of information that we need includes the SPID, host, database name, login name and application being used.  While all this is important, at times system processes need to be identified or even excluded from the results.

Over the years, DBAs have developed their own technics for gathering information about current processes. These have included sp_who, sp_who2 and a query pulling data from the sys.processes table. SYS.DM_EXEC_SESSIONS is a DMV that came out more recently, SQL Server 2008.  I have read many articles and posts over the years that have said any SPID with an ID of 50 or less should be considered system related.  While this is true, there are some processes that will have an ID greater than 50 and also be a system process. SYS.DM_EXEC_SESSIONS allows us to easily identify system processes.

Sp_who and Sp_who2 are stored procedures I have used many times over the years. I usually use sp_who2 rather than sp_who.  Sp_who2 returns more information than sp_who. If you look at the image below, you will see two data sets.  The top one is sp_who and the bottom is sp_who2. These additional columns is why I prefer sp_who2.

Sys.sysprocesses is also a great resource   However is does not include a column that will allow us to determine if it is a system process or not.  Below are the columns in sys.sysprocesses.

SYS.DM_EXEC_SESSIONS is a view that was included starting with SQL Server 2008.  This view returns much, if not all the same information as the above. However, it also returns a great deal of additional information.  One of the columns returned is “is_user_process”.  This is a bit column that if set to 0 is a system process and if set to 1, is a user process.
SELECT     session_id
        , login_time 
        , is_user_process
        , login_name
        , host_name
If you run the above query, you will see a result set similar to what is below.  Is_user_process is the third column.  The rows in the red box are above session_id of 50 or more.  The items in the green box are user processes, as
identified in the is_user_process column.

This is a great view to use.  Some thing to keep in mind, it is a view and one of the tables it pulls from is sys.sysprocesses.  Here is a link to Microsoft’s documentation on this view.

Thanks for visiting my blog!!!