The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to execute a separate DBCC INPUTBUFFER, the statement being executed is shown in the results.
Unlike sp_who2, sp_who3 only shows sessions that have a current executing request.
What is also shown is the reads and writes for the current command, along with the number of reads and writes for the entire SPID. It also shows the protocol being used (TCP, NamedPipes, or Shared Memory).
CREATE PROCEDURE sp_who3 ( @SessionID int = NULL ) AS BEGIN SELECT SPID = er.session_id ,Status = ses.status ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 AND @SessionID IS NULL OR er.session_id = @SessionID ORDER BY er.blocking_session_id DESC ,er.session_id END
It is ok in sql 2008 but can not save in sql 2000, got an error:
Incorrect syntax near the keyword ‘OUTER’.
(OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st)
Hi adonetok,
Yes, unfortunately you cannot run this in SQL 2000. It will only work in SQL 2005+
Excellent example, this is a big help for me in our monitoring application.
Great, glad this helped. I have considered altering this to include all sessions and not just active ones. I may add an additional example for that one. This helps me sometimes but I still find myself using sp_who2 a lot.
[...] fix. It is likely you have a run away query. The first thing you want to do is run sp_who2 (or sp_who3) and look for connections that have a SPID > 50 with abnormally high CPU time. The CPU time shown [...]