vwWhoActiveSummary - vwWho3 GROUPed BY spid

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