vwWho3 - a filtered vwWho2

In the last post we created a view that is the contents of sp_who2, plus some other valuable information.

Here we will create vwWho3, which is the same as vwWho2, with a filter to see only active or pertinent spids.

USE Admin

GO

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

      DROP VIEW dbo.vwWho3

go

CREATE VIEW [dbo].[vwWho3]

AS

/*    DATE            AUTHOR          REMARKS

      10/15/12          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

vwWho2 - a better sp_who2

sp_who and sp_who2 are wonderful system procs that give you immediate information on all spids, (active or sleeping or any status), that are currently connected  to your SQL Server instance.

However, you cannot intrinsically JOIN these results to any other queries you may wish to write, perhaps for filtering purposes.   

SOLUTION:  Let’s make a view with the contents of sp_who2, then add some more useful information such as the SQL text for a given spid.

Find sp_who2 in the master database, and you will see it is mostly this query:

SELECT *

FROM sys.sysprocesses

In sys.sysprocesses, spids 1 thru 50 can be ignored because they are used by the system.  In our view, we will retain just one row from these 50, to make it easy to show the date/time that this instance of SQL Server was started.  Columns last_batch and login_time will contain that information.

USE Admin

GO

IF OBJECT_ID('dbo.vwWho2') Is Not Null

      DROP VIEW dbo.vwWho2

GO

CREATE  VIEW [dbo].[vwWho2]

AS

/*      DATE            AUTHOR          REMARKS

            10/1/12                 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 LogiName, Count(*) Qty

                  FROM vwWho2

                  GROUP BY LogiName

                  ORDER BY 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

                  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

                  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