Most are familiar with using the procedures sp_who or sp_who2 to get a quick glimpse of the processes running on the SQL Server instance. However these procs are missing some valuable and easily extractable information. Also you cannot intrinsically JOIN or filter on these results.
Examining the code from sp_who2 reveals that the essence of the proc is
SELECT *
FROM sys.sysprocesses
The below code for vwWho2 is exactly that, with some slight enhancements. The advantage of having this code in a view rather than a proc is so that you can filter the results easily with a WHERE clause, something that would be helpful on a busy server.
In sys.sysprocesses, rows for spid < 51 can be ignored because they are being used by the system. However, vwWho2 does include one of those rows, just to have a handy way of getting the date/time when this instance of SQL Server was last started.
In the next post I will show the code for vwWho3 which will helps to filter on the most pertinent spids.
USE Admin
go
IF Object_ID('dbo.vwWho2') Is Not Null
DROP VIEW dbo.vwWho2
go
CREATE VIEW [dbo].[vwWho2]
AS
/* DATE AUTHOR REMARKS
7/5/11 PPaiva Initial creation.
DESCRIPTION
Pertinent info from sysprocesses. This is an alternative
to using sp_who2 because results from sp_who2 cannot be
intrinsically joined nor queried.
Note that the first row in the output is given just to get the
start time of the SQL Server service (login_time).
NOTE: Executing this Trace Flag will force SQL Server to record the
sql_handle for even trivial queries.
DBCC TRACEON (2861)
However, beware as this has been known to sometimes cause
performance issues.
USAGE
SELECT *
FROM vwWho2
WHERE WaitTimeMin > 0
-- By User
SELECT *
FROM vwWho2
ORDER BY LogiName
-- By User, summary
SELECT LogiName, Count(*) Qty
FROM vwWho2
GROUP BY LogiName
ORDER BY LogiName
NOTE: For versions of SQL Server that do not support the
view sys.dm_exec_sql_text, you can use this system
function to get the SQL text from the SQL Handle.
SELECT *
FROM :: fn_get_sql(0x010006007756E52D60372D320000000000000000)
DEBUG
SELECT *
FROM sys.sysprocesses
*/
SELECT TOP 1
spid,
RTrim(LogiName) LogiName,
RTrim(db_name(dbid)) DB,
Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,
Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,
LastWaitType,
blocked,
Status,
RTrim(HostName) HostName,
dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),
2, ' ') MinsFromLastBatch,
last_batch,
login_time,
cpu,
physical_io PhysicalIO,
MemUsage,
RTrim(Program_Name) Program,
Null SqlText,
WaitTime WaitTimeMs,
open_tran,
cmd,
RTrim(nt_UserName) NTUser,
net_address,
net_library,
sql_handle
FROM master.dbo.sysprocesses P
WHERE spid <= 50
UNION ALL
SELECT TOP 100 PERCENT
spid,
RTrim(LogiName) LogiName,
RTrim(db_name(p.dbid)) DB,
Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,
Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,
LastWaitType,
blocked,
Status,
RTrim(HostName) HostName,
dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),
2, ' ') MinsFromLastBatch,
last_batch,
login_time,
cpu,
physical_io PhysicalIO,
MemUsage,
RTrim(Program_Name) Program,
sh.Text,
WaitTime WaitTimeMs,
open_tran,
cmd,
RTrim(nt_UserName) NTUser,
net_address,
net_library,
sql_handle
FROM master.dbo.sysprocesses p
OUTER APPLY sys.dm_exec_sql_text(sql_handle) sh
WHERE spid > 50
OR Blocked <> 0
