> a year later, ha... I ran across this thread in a search and realized I never came back and posted the version I've been using since shortly after my previous sp_who2DMV post.... This version compiles the work from KenSimmons and Grasshopper into one proc. It also accepts a parm... For example you can enter sp_who2DMV 'CPU' to sort desc by highest CPU. See comments for more.

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