This is created in tempdb but you can optionally put it into your Admin (or similarly named) database.
Use this as a first check when you are having a performance problem.
/* DATE AUTHOR REMARKS
2/22/16 PPaiva Initial
creation.
DESCRIPTION
Use
this code to see currently running spids, as an
alternative
to sp_who2, which is what this is based on
plus
a few more features.
After
executing all the code on this page in one query window,
you
can then run any of the below queries.
The first
two
queries have already been run for you at the end of
the
script.
--
By User, LastWaitType, summary, ACTIVE only
SELECT
Instance, spid, LogiName, LastWaitType, Count(*) Qty
FROM
vwWho3
GROUP
BY Instance, spid, LastWaitType, LogiName
ORDER
BY Instance, spid, LastWaitType, LogiName
--
Active processes only (not sleeping)
SELECT
*
FROM
vwWho3
--
All spids > 50
SELECT
*
FROM
vwWho2
WHERE
WaitTimeMin > 0
--
By User
SELECT
*
FROM
vwWho2
ORDER
BY LogiName
--
By User, summary
SELECT
Instance, LogiName, LastWaitType, Count(*) Qty
FROM
vwWho2
GROUP
BY Instance, LogiName, LastWaitType
ORDER
BY Instance, LogiName, LastWaitType
*/
Use tempdb
--Use Admin
go
IF object_id('dbo.udfPadLeft') Is Not Null
DROP FUNCTION dbo.udfPadLeft
go
CREATE FUNCTION [dbo].[udfPadLeft](
@In varchar(100),
@Width
int,
@PadChar varchar(1)
)
RETURNS varchar(100)
AS
/*
DATE AUTHOR REMARKS
8/19/15 PPaiva Initial creation.
DESCRIPTION
Pads
@In with @PadChar so that the length of the returned
value
is @Width. If the length of @In is
greater than @Width
then
@In is returned.
USAGE
SELECT
dbo.udfPadLeft('7', '3', '0')
*/
BEGIN
DECLARE @Out varchar(100),
@LenIn int
SET @LenIn = Len(@In)
IF @Width - @LenIn < 0
SET @Out = @In
ELSE
SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In
RETURN @Out
END
go
IF object_id('dbo.vwWho2') Is Not Null
DROP VIEW dbo.vwWho2
go
CREATE VIEW dbo.vwWho2
AS
/*
DATE AUTHOR REMARKS
1/22/16 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 Instance, LogiName, Count(*) Qty
FROM
vwWho2
GROUP
BY Instance, LogiName
ORDER
BY Instance, 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
ServerProperty('ServerName') Instance,
spid,
RTrim(LogiName) LogiName,
RTrim(db_name(dbid)) DB,
RTrim(Program_Name)
Program,
Null SqlText,
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,
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
ServerProperty('ServerName') Instance,
spid,
RTrim(LogiName) LogiName,
RTrim(db_name(p.dbid)) DB,
RTrim(Program_Name)
Program,
sh.Text,
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,
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
GO
IF object_id('dbo.vwWho3') Is Not Null
DROP VIEW dbo.vwWho3
go
CREATE VIEW [dbo].[vwWho3]
AS
/* DATE AUTHOR REMARKS
1/22/16 PPaiva Initial creation.
DESCRIPTION
Same
as vwWho2 except this only gets active processes, plus
one
row from spids between 1 and 50, so that it is easy
to
show the date/time this instance of SQL Server was
last
started.
NOTE
If
spid = -2, it is an orphaned distributed transaction.
This
can be terminated with
KILL
'Unit Of Work ID (this is a guid)'
USAGE
SELECT
*
FROM
vwWho3
*/
SELECT *
FROM vwWho2
WHERE Status Not In ('sleeping')
OR Blocked <> 0
OR Open_Tran <>
0
OR spid < 1
go
-- By User, LastWaitType, summary,
ACTIVE only
SELECT Instance, spid, LogiName,
LastWaitType, Count(*) Qty
FROM vwWho3
GROUP BY Instance, spid, LastWaitType, LogiName
ORDER BY Instance, spid, LastWaitType, LogiName
-- Active processes only (not
sleeping)
SELECT *
FROM vwWho3
