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.
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!!!