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.

Friday, January 22, 2016

vwWho2 - a better sp_who2 (dependencies included)

I've been asked to produce the code for vwWho2 with no dependencies.

This is created in tempdb but you can optionally put it into your Admin (or similarly named) database.

Use this as a first check when you are having a performance problem.


/*     DATE         AUTHOR       REMARKS
       2/22/16      PPaiva       Initial creation.

       DESCRIPTION
             Use this code to see currently running spids, as an
                    alternative to sp_who2, which is what this is based on
                    plus a few more features. 
             After executing all the code on this page in one query window,
                    you can then run any of the below queries.  The first
                    two queries have already been run for you at the end of
                    the script.


       -- By User, LastWaitType, summary, ACTIVE only
       SELECT Instance, spid, LogiName, LastWaitType, Count(*) Qty
       FROM vwWho3
       GROUP BY Instance, spid, LastWaitType, LogiName
       ORDER BY Instance, spid, LastWaitType, LogiName

       -- Active processes only (not sleeping)
       SELECT *
       FROM vwWho3 

       -- All spids > 50
       SELECT *
       FROM vwWho2
       WHERE WaitTimeMin > 0

       -- By User
       SELECT *
       FROM vwWho2
       ORDER BY LogiName

       -- By User, summary
       SELECT Instance, LogiName, LastWaitType, Count(*) Qty
       FROM vwWho2
       GROUP BY Instance, LogiName, LastWaitType
       ORDER BY Instance, LogiName, LastWaitType

*/

Use tempdb
--Use Admin
go
IF object_id('dbo.udfPadLeft') Is Not Null
       DROP FUNCTION dbo.udfPadLeft
go

CREATE FUNCTION [dbo].[udfPadLeft](
      @In varchar(100),
      @Width int,
      @PadChar varchar(1)
      )
RETURNS varchar(100)
AS
/*  DATE        AUTHOR            REMARKS
    8/19/15     PPaiva            Initial creation.
    
       DESCRIPTION
             Pads @In with @PadChar so that the length of the returned
             value is @Width.  If the length of @In is greater than @Width
             then @In is returned.

       USAGE
             SELECT dbo.udfPadLeft('7', '3', '0')

*/
BEGIN
      DECLARE @Out varchar(100),
                  @LenIn int


      SET @LenIn = Len(@In)
    
      IF @Width - @LenIn < 0
            SET @Out = @In
      ELSE
            SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In

      RETURN @Out

END
go



IF object_id('dbo.vwWho2') Is Not Null
       DROP VIEW dbo.vwWho2
go
CREATE VIEW dbo.vwWho2
AS
/*  DATE            AUTHOR          REMARKS
       1/22/16                    PPaiva          Initial creation.

       DESCRIPTION
             Pertinent info from sysprocesses.  This is an alternative
                    to using sp_who2 because results from sp_who2 cannot be
                    intrinsically joined nor queried.
             Note that the first row in the output is given just to get the
                    start time of the SQL Server service (login_time).

             NOTE:   Executing this Trace Flag will force SQL Server to record the
                           sql_handle for even trivial queries.
                                 DBCC TRACEON (2861)
                           However, beware as this has been known to sometimes cause
                                 performance  issues.

       USAGE
             SELECT *
             FROM vwWho2
             WHERE WaitTimeMin > 0

             -- By User
             SELECT *
             FROM vwWho2
             ORDER BY LogiName

             -- By User, summary
             SELECT Instance, LogiName, Count(*) Qty
             FROM vwWho2
             GROUP BY Instance, LogiName
             ORDER BY Instance, LogiName


             NOTE:  For versions of SQL Server that do not support the
                           view sys.dm_exec_sql_text, you can use this system
                           function to get the SQL text from the SQL Handle.
                                 SELECT *
                                 FROM :: fn_get_sql(0x010006007756E52D60372D320000000000000000)

       DEBUG
             SELECT *
             FROM sys.sysprocesses

*/
SELECT  TOP 1
                    ServerProperty('ServerName') Instance,
                    spid,
                    RTrim(LogiName) LogiName,
                    RTrim(db_name(dbid)) DB,
                    RTrim(Program_Name) Program,
                    Null SqlText,
                    Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,
                    Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,
                    LastWaitType,
                    blocked,
                    Status,
                    RTrim(HostName) HostName,
                    dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),
                                                     2, '        ') MinsFromLastBatch,
                    last_batch,
                    login_time,
                    cpu,
                    physical_io PhysicalIO,
                    MemUsage,
                    WaitTime WaitTimeMs,
                    open_tran,
                    cmd,
                    RTrim(nt_UserName) NTUser,
                    net_address,
                    net_library,
                    sql_handle
FROM master.dbo.sysprocesses P
WHERE spid <= 50

UNION ALL

SELECT  TOP 100 PERCENT
                    ServerProperty('ServerName') Instance,
                    spid,
                    RTrim(LogiName) LogiName,
                    RTrim(db_name(p.dbid)) DB,
                    RTrim(Program_Name) Program,
                    sh.Text,
                    Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,
                    Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,
                    LastWaitType,
                    blocked,
                    Status,
                    RTrim(HostName) HostName,
                    dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),
                                                     2, '        ') MinsFromLastBatch,
                    last_batch,
                    login_time,
                    cpu,
                    physical_io PhysicalIO,
                    MemUsage,
                    WaitTime WaitTimeMs,
                    open_tran,
                    cmd,
                    RTrim(nt_UserName) NTUser,
                    net_address,
                    net_library,
                    sql_handle
FROM master.dbo.sysprocesses p
OUTER APPLY sys.dm_exec_sql_text(sql_handle) sh
WHERE spid > 50
   OR Blocked <> 0
  
GO
IF object_id('dbo.vwWho3') Is Not Null
       DROP VIEW dbo.vwWho3
go
CREATE VIEW [dbo].[vwWho3]
AS
/*     DATE        AUTHOR          REMARKS
       1/22/16             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
go


-- By User, LastWaitType, summary, ACTIVE only
SELECT Instance, spid, LogiName, LastWaitType, Count(*) Qty
FROM vwWho3
GROUP BY Instance, spid, LastWaitType, LogiName
ORDER BY Instance, spid, LastWaitType, LogiName

-- Active processes only (not sleeping)
SELECT *
FROM vwWho3