vwWhoActiveSummary

Shows a summary of the active spids.  A larger number of rows per spid, shown here in the Qty column, indicates a large query is being serviced with multiple threads.

Use Admin

go

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

      DROP VIEW dbo.vwWhoActiveSummary

go

CREATE VIEW dbo.vwWhoActiveSummary

AS

/*    DATE        AUTHOR            REMARKS

      8/15/11           PPaiva            Initial creation.

      DESCRIPTION

            A summary of the active processes.  Useful for identifying how many

                  threads are dedicated to a given spid.  A large quantity of

                  rows indicates a large query is being serviced.

                 

            SELECT *

            FROM vwWhoActiveSummary

            SELECT *

            FROM vwWho3

*/

SELECT  DB,

            spid,

            blocked,

            (     SELECT LogiName

                  FROM Admin.dbo.vwWho3

                  WHERE spid = v.spid

                    AND LogiName <> '') LogiName,

            HostName,

            Count(*) Qty,

            Max(WaitTimeMin) WaitTimeMin,

            Max(WaitTimeHr) WaitTimeHr,

            Cmd,

            Program,

            (     SELECT SqlText

                  FROM Admin.dbo.vwWho3

                  WHERE spid = v.spid

                    AND LogiName <> '') SqlText

FROM Admin.dbo.vwWho3 v

WHERE spid <> 1

GROUP BY DB, spid, blocked, HostName, Program, Cmd

vwWho3

Shows just the active processes from vwWho3.

Use Admin

go

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

      DROP VIEW vwWho3

go

CREATE VIEW [dbo].[vwWho3]

AS

/*    DATE            AUTHOR          REMARKS

      8/1/11                  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

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

  

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

SQL 2008 or 2008 R2 Installation Guide

Here is a guide to installing SQL Server 2008 or SQL 2008 R2.  It merely contains 30 screen shots that you would encounter during the installation.  This is geared toward a full installation, and helpful for non-SQL-savvy professionals who are doing this installation for the first time.

SQL 2008 R2 Installation Guide
(this is a pdf document you can save)

Some of the options can be intimidating, but know that all of the paths and user account options can be changed after installation, so you don't need to fret about having a perfect configuration from the start.

Hope this is helpful to you!

- Paul