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.

Monday, October 15, 2012

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



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