Sometimes we need to further refine vwWho2 and vwWho3, to
get a quick glimpse of server activity.
Here we will create vwWhoActiveSummary. It shows a summary of the active spids, GROUPed
BY spid. A new column is introduced, Qty,
which is how many rows (threads) SQL Server is dedicating to that spid.
I use this to see if there are any spids that are using
multiple threads. That can happen for very
large or complex queries.
USE Admin
GO
IF object_id('dbo.vwWhoActiveSummary')
Is Not Null
DROP VIEW dbo.vwWhoActiveSummary
go
CREATE VIEW dbo.vwWhoActiveSummary
AS
/* DATE AUTHOR REMARKS
11/3/12 PPaiva Initial
creation.
DESCRIPTION
A summary of the active
processes. Useful for identifying how
many
threads are
dedicated to a given spid. A large
quantity of
rows indicates a
large query is being serviced.
SELECT *
FROM vwWhoActiveSummary
SELECT *
FROM vwWho3
*/
SELECT DB,
spid,
blocked,
( SELECT
LogiName
FROM
Admin.dbo.vwWho3
WHERE
spid = v.spid
AND LogiName <> '') LogiName,
HostName,
Count(*) Qty,
Max(WaitTimeMin)
WaitTimeMin,
Max(WaitTimeHr)
WaitTimeHr,
Cmd,
Program,
( SELECT
SqlText
FROM
Admin.dbo.vwWho3
WHERE
spid = v.spid
AND LogiName <> '') SqlText
FROM Admin.dbo.vwWho3 v
WHERE spid <> 1
GROUP BY DB, spid, blocked, HostName, Program, Cmd
