Skip to content
 

A Better sp_who2 using DMVs (sp_who3)

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
Share and Enjoy:
  • Print this article!
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks

Related Posts:

  • » SQL Server Kill
  • » Using sp_who2
  • » What Version of SQL Server Am I Running?
  • » Determine Database Owner
  • » Find Best Clustered Index
    • eatdirt
      Excellent example, this is a big help for me in our monitoring application.
    blog comments powered by Disqus