vwWhoActiveSummary

Shows a summary of the active spids.  A larger number of rows per spid, shown here in the Qty column, indicates a large query is being serviced with multiple threads.

Use Admin

go

IF object_id('dbo.vwWhoActiveSummary') Is Not Null

      DROP VIEW dbo.vwWhoActiveSummary

go

CREATE VIEW dbo.vwWhoActiveSummary

AS

/*    DATE        AUTHOR            REMARKS

      8/15/11           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