Technical Article

sp_what

,

This is a replacement for SP_who and modification of sp_who2.

It will always list only active, nonsystem processes, and will list the number of seconds a transaction has been running for. Some transactions do not report a last batch time, so I forced a large value to display so you can see any issue with that transaction. Its sorted by spid, so you can se more easily whats blocking, and the columns are arranged to fit all info better on the screen (1280x1024).

CREATE PROCEDURE sp_what  --- 2006/3/22
--    @loginame     sysname = NULL
as

set nocount on

declare
    @retcode         int, @loginame  sysname
declare
    @sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int

declare
    @charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
    @charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
--------
select  @retcode         = 0      -- 0=good ,1=bad.
--------------------------------------------------------------

if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

--------------------  Capture consistent sysprocesses.  -------------------

SELECT
  spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort', 
substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch
      INTO    #tb1_sysprocesses
      from master.dbo.sysprocesses   (nolock)

--------Screen out any rows
   DELETE #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    IN (
                     'AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'
    ,'TASK MANAGER'
                                  )

         and     blocked       = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE()) 
where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch < '01/01/1950'
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN program_name varchar(20)
--------Prepare to dynamically optimize column widths.
SELECT
            @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5)),
            @charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)),
            @charMaxLenCPUTime =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)),
            @charMaxLenDiskIO =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)),
            @charMaxLenCommand = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)),
            @charMaxLenHostName  = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),
            @charMaxLenProgramName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)),
            @charMaxLenLastBatch = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
      from
             #tb1_sysprocesses
      where
             spid >= 0 and spid <= 32767



--------Output the report.


EXECUTE(
'SET nocount off
SELECT   SPID          = convert(char(5),spid)
        ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END
        ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
        ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
        ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
        ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END
 ,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
        ,LBDate = substring(last_batch_char,1,5)
,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
        ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
        ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
      -- (Seems always auto sorted.)   order by SPID
SET nocount on')

drop table #tb1_sysprocesses
--return @retcode 
GO

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating