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 the "x" switch 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.

Usage summary:

- sp__mon_sql2          ((no params), returns standard info for all spids)
- sp__mon_sql2 spid     (returns standard info for a specific spid)
- sp__mon_sql2 null, "x"  (returns extended info for all spids)
- sp__mon_sql2 spid, "x"  (returns extended info for a specific spid)

create procedure sp__mon_sql2
(
@spid int = null, -- default to retrieve info for all spids
@extinfo char(1) = "u" -- extended info switch, default “u” (unset) is for off, “x” and “X” are for on)
)
as
------------------------------
-- Procedure: sp__mon_sql2
--   Created: April 2008
--    Author: Bob Holmes (Email: cambob@gmail.com)
--     Usage: sp__mon_sql2 [spid]
-- Version  : 1.1
------------------------------
--Modification history:
--bobh - if 1 is specified as the spid number then alternate info is returned (dirty hack)
--bobh: 14/09/2009: Dirty hack mentioned above removed.  Usage is now as follows:
--                  - sp__mon_sql2          ((no params), returns standard info for all spids)
--                  - sp__mon_sql2 spid     (returns standard info for a specific spid)
--                  - sp__mon_sql2 null, x  (returns extended info for all spids)
--                  - sp__mon_sql2 spid, x  (returns extended info for a specific spid)
------------------------------
-- INFO:
-- The three *'s are a grep key for a shell script to use to extract the lines needed for alerting.
------------------------------
set nocount on

--prep - setup input variables for run mode
if @spid=null
begin
  select @spid=0
end

--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

--
--
--case 1 - no params (0 and "u")
if @spid=0 and @extinfo="u" -- display standard information for all spids
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
--
--
--case 2 
else if @spid=0 and @extinfo="x" -- extended info for all spids
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
--
--
-- case 3 
else if @spid <> 0 and @extinfo = "u" -- display standard info for specific spid
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,
  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 spid = @spid
  order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end
--
--
-- case 4
else if @spid <> 0 and @extinfo = "x" -- display extended info for specific spid
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 spid = @spid
  order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
end