About Me

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 for 11 years, from January 2009 to 2020.

Tuesday, October 2, 2012

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

No comments:

Post a Comment