SELECT
    t.text                                               AS SQLStatement, 
    s.Session_ID                                         AS SPID, 
    COALESCE(r.status, s.status)                         AS Status, 
    s.login_name                                         AS Login, 
    s.host_name                                          AS HostName, 
    lw.BlkBy                                             AS BlockedBy,  
    DB_NAME(r.Database_ID)                               AS DBName, 
    r.command                                            AS Command, 
    COALESCE(r.cpu_time, s.cpu_time)                     AS CPUTime, 
    COALESCE((r.reads + r.writes), (s.reads + s.writes)) AS DiskIO,  
    s.last_request_start_time                            AS LastBatch, 
    s.program_name                                       AS ProgramName
FROM 
    sys.dm_exec_sessions AS s
  LEFT JOIN
    sys.dm_exec_requests AS r
  ON
    s.session_id = r.session_id
  LEFT JOIN
    sys.dm_exec_connections AS c
  ON
    s.Session_ID = c.Session_ID
  LEFT JOIN
    ( 
        SELECT 
            l.request_session_id   AS SPID, 
            w.blocking_session_id  AS BlkBy
        FROM 
            sys.dm_tran_locks as l
          INNER JOIN
            sys.dm_os_waiting_tasks as w
          ON
            l.lock_owner_address = w.resource_address
    ) AS lw
  ON
    s.Session_ID = lw.SPID
  OUTER APPLY
    sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t

USE [master]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_who2DMV]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_who2DMV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_who2DMV]
@ORDERBY VARCHAR(4) = 'SPID'
/* 
The @ORDERBY parameter supports: 
                   "CPU"  CPUTime
                   "IO"   DiskIO
                   "USR"  Login (user)
                   "HOST" HostName
                   "APP"  AppName
Execution examples:
EXEC sp_who2DMV       --No order by (orders by SPID by default)
EXEC sp_who2DMV 'CPU' --orders by highest CPU Time
EXEC sp_who2DMV 'IO'  --orders by highest Disk IO
*/
AS
IF ((SELECT 
	CASE 
		WHEN @ORDERBY in ('SPID', 'CPU', 'IO', 'USR', 'HOST', 'APP') 
		THEN 1 
		ELSE 0 
	END) = 0)
	BEGIN 
		-- abort if invalid @ORDERBY parameter entered
		RAISERROR('@ORDERBY parameter not SPID, CPU, IO, USR, HOST or APP',11,1)
		RETURN
	END
SELECT
    t.text                                               AS SQLStatement, 
    s.Session_ID                                         AS SPID, 
    COALESCE(r.status, s.status)                         AS Status, 
    s.login_name                                         AS Login, 
    s.host_name                                          AS HostName, 
    lw.BlkBy                                             AS BlockedBy,  
    DB_NAME(r.Database_ID)                               AS DBName, 
    r.command                                            AS Command, 
    COALESCE(r.cpu_time, s.cpu_time)                     AS CPUTime, 
    COALESCE((r.reads + r.writes), (s.reads + s.writes)) AS DiskIO,  
    s.last_request_start_time                            AS LastBatch, 
    s.program_name                                       AS ProgramName
FROM 
    sys.dm_exec_sessions AS s
  LEFT JOIN
    sys.dm_exec_requests AS r
  ON
    s.session_id = r.session_id
  LEFT JOIN
    sys.dm_exec_connections AS c
  ON
    s.Session_ID = c.Session_ID
  LEFT JOIN
    ( 
        SELECT 
            l.request_session_id   AS SPID, 
            w.blocking_session_id  AS BlkBy
        FROM 
            sys.dm_tran_locks as l
          INNER JOIN
            sys.dm_os_waiting_tasks as w
          ON
            l.lock_owner_address = w.resource_address
    ) AS lw
  ON
    s.Session_ID = lw.SPID
  OUTER APPLY
    sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t
WHERE s.Session_ID > 50
ORDER BY 
	CASE 
		WHEN @ORDERBY = 'CPU'  THEN cast(ISNULL(r.cpu_time, s.cpu_time) as varchar)
		WHEN @ORDERBY = 'IO'   THEN cast(ISNULL((r.reads + r.writes),(s.reads + s.writes)) as varchar)
		WHEN @ORDERBY = 'USR'  THEN s.login_name
		WHEN @ORDERBY = 'HOST' THEN s.host_name
		WHEN @ORDERBY = 'APP'  THEN s.program_name
	END DESC