About Me

My photo
Colorado
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.

Wednesday, August 31, 2011

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


Tuesday, August 2, 2011

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