Sp mon sql2--ASE spid activity monitor

Note: This SP uses the MDA tables.
This sp is for getting a clear list of currently active spids with full details including login, db, blocking spid, processing time, query start time, cpu usage, logical and physical i/o, currently executing command etc. To maximise clarity, all info is displayed on one line for each spid. Alternate info is also available by running with '1' (without quotes) which includes, login time, host process, originating host and other information which may be useful for tracking down the source of a problem external to ASE. Note, unfortunately the '1' option was a quick hack - the sp needs modifying to change this to a 'x' or something similar as the sp can also be called with <spid> to investigate a spid which is not currently active.

Usage summary:
sp__mon_sql2
sp__mon_sql2 <spid>
sp__mon_sql2 1 (due to the quick hack noted above, if you want to investigate spid 1, you can't! sorry - I'll fix it in the next version. Luckily the need to investigate spid 1 is quite rare.

create procedure sp__mon_sql2
(
@spid int = 0
)
as
------------------------------
-- Procedure: sp__mon_sql2
--   Created: April 2008
--    Author: Bob Holmes
-- Last modification: bobh - if 1 is specified as the spid number then alternate info is returned (dirty hack)
------------------------------
-- INFO:
-- The three *'s are a grep key for a shell script to use to extract the lines needed for alerting.
------------------------------
--part 1 - snapshot
set forceplan on
select 	s.StartTime, 
	convert(smallint,(datediff(mi,StartTime,getdate()))) Dmins,  
	p.spid, 
	p.hostname, 
	p.suid, 
	p.dbid, 
	p.cmd, 
        p.blocked,
	s.CpuTime, 
	s.LogicalReads, 
	s.PhysicalReads,
	p.status, 
	p.loggedindatetime,
	p.program_name, 
	l.ClientOSPID, 
	p.time_blocked, 
	p.ipaddr
into #processmon2
from master..sysprocesses p, master..monProcessStatement s, master..monProcessLookup l
where p.spid != @@spid
and p.spid *= s.SPID
and l.SPID = p.spid
and suid <> 0
order by p.spid
set forceplan off

--part 2 --display spid summaries

if @spid=0
begin
-- display header information for multiple spids
  print "******************************************************************************"
  print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
  print "******************************************************************************"
  print ""
  select distinct "***",
  spid Spid,
  convert(char(12),suser_name(suid)) Login,
  convert(char(16),db_name(dbid)) DB,
  (case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
  blocked,
  convert(varchar(19),StartTime) StartTime,
  Dmins,
  right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
  CpuTime,
  LogicalReads,
  PhysicalReads,
  cmd Command
  from #processmon2
  where cmd <> "AWAITING COMMAND"
  order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
else if @spid=1
begin
-- display header information for multiple spids
  print "******************************************************************************"
  print "Process Summary (Order by Dmins, CpuTime, LogicalReads, PhysicalReads Desc )"
  print "******************************************************************************"
  print ""
select 
  spid Spid,
  convert(char(12),suser_name(suid)) Login,
  convert(char(15),db_name(dbid)) DB,
  (case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
  blocked,
  convert(varchar(19),StartTime) StartTime,
  Dmins,
  right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
  (case program_name when "" then "-" else convert(char(12),program_name) end) Application,
  (case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS PID",
  time_blocked "Blocked(s)",
  convert(char(8),loggedindatetime,108) "Logged in",
  convert(char(15),ipaddr) ClientIP,
  cmd Command
  from #processmon2
  where cmd <> "AWAITING COMMAND"
  order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
else
begin
select distinct "***",
  spid Spid,
  convert(char(12),suser_name(suid)) Login,
  convert(char(16),db_name(dbid)) DB,
  (case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
  convert(varchar(19),StartTime) StartTime,
  Dmins,
  blocked,
  right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
  CpuTime,
  LogicalReads,
  PhysicalReads,
  cmd Command
  from #processmon2
  where spid = @spid
  order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end


--select spid, convert(char(15),suser_name(suid)) user_name, 
--convert(char(15),db_name(dbid)) dbname, status, 
--convert(char(8),loggedindatetime,108) "logged in",
--(case hostname when "" then "-" else hostname end) hostname, 
--(case program_name when "" then "-" else convert(char(15),program_name) end) application, 
--(case ClientOSPID when NULL then "-" else convert(char(7),ClientOSPID) end) "OS Process", blocked, time_blocked, ipaddr ClientIP, cmd  from --master..sysprocesses p, master..monProcessLookup l
--where l.SPID = p.spid