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, July 20, 2011

vwWho2

Most are familiar with using the procedures sp_who or sp_who2 to get a quick glimpse of the processes running on the SQL Server instance.  However these procs are missing some valuable and easily extractable information.  Also you cannot intrinsically JOIN or filter on these results.

Examining the code from sp_who2 reveals that the essence of the proc is
SELECT *
FROM sys.sysprocesses

The below code for vwWho2 is exactly that, with some slight enhancements.  The advantage of having this code in a view rather than a proc is so that you can filter the results easily with a WHERE clause, something that would be helpful on a busy server.

In sys.sysprocesses, rows for spid < 51 can be ignored because they are being used by the system.  However, vwWho2 does include one of those rows, just to have a handy way of getting the date/time when this instance of SQL Server was last started.

Note this view is dependent upon scalar function udfPadLeft() whose code was given in the last post.  This is just for formatting some columns and is not essential.  

In the next post I will show the code for vwWho3 which will helps to filter on the most pertinent spids.


USE Admin
go
IF Object_ID('dbo.vwWho2') Is Not Null
      DROP VIEW dbo.vwWho2
go


CREATE  VIEW [dbo].[vwWho2]
AS
/*      DATE            AUTHOR          REMARKS
            7/5/11                  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,
                  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,
                  RTrim(Program_Name) Program,
                  Null SqlText,
                  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,
                  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,
                  RTrim(Program_Name) Program,
                  sh.Text,
                  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
  

Wednesday, July 6, 2011

udfPadLeft()

This is a simple user-defined scalar function which will be referenced by subsequent posts.  It is just a tool that comes in very handy for formatting.


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
      7/6/11            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