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

vwWho3

Shows just the active processes from vwWho3.

Use Admin

go

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

      DROP VIEW vwWho3

go

CREATE VIEW [dbo].[vwWho3]

AS

/*    DATE            AUTHOR          REMARKS

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