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, this custom 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). The lead blocker below will show in the BlkBy column as -1. [cc lang=”sql”] USE [master] GO CREATE PROCEDURE [dbo].[sp_who3] AS BEGIN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, CASE WHEN ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset / 2 ) < 0 THEN 0 ELSE CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset / 2 END ) ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read […]