About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Saturday, November 3, 2012

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

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


CREATE VIEW dbo.vwWhoActiveSummary
/*    DATE        AUTHOR            REMARKS
      11/3/12           PPaiva            Initial creation.

            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 LogiName
                  FROM Admin.dbo.vwWho3
                  WHERE spid = v.spid
                    AND LogiName <> '') LogiName,
            Count(*) Qty,
            Max(WaitTimeMin) WaitTimeMin,
            Max(WaitTimeHr) WaitTimeHr,
            (     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