Difference between revisions of "Sp mon sql2--ASE spid activity monitor"

From SybaseWiki
Jump to: navigation, search
m
m (corrected spacing)
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
Note: This SP uses the MDA tables.<BR>
 
Note: This SP uses the MDA tables.<BR>
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 an 'x' or something similar as the sp can also be called with <spid> to investigate a spid which is not currently active.
+
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.
 +
 
 +
Example output (normal usage)
 +
<pre>
 +
    Spid  Login        DB              Hostname    blocked StartTime          Dmins  Dtime CpuTime    LogicalReads PhysicalReads Command
 +
  --- ------ ------------ ---------------- ------------ ------- ------------------- ------ ----- ----------- ------------ ------------- ------------------------------
 +
***    126 sa          PROD_DB1        prod-srv1          0 Mar 24 2010 12:13AM    570 09:30          0          42            0 SELECT
 +
***    143 app_user1    PROD_DB1        user-hosta        0 Mar 24 2010  9:38AM      6 00:06          10            0            0 SELECT
 +
***    32 app_user1    PROD_DB1        user-hostb      143 Mar 24 2010  9:38AM      5 00:05          0          20            0 SELECT
 +
***    44 cron        cron_tempdb      prod-srv1          0 Mar 24 2010  9:40AM      3 00:03          0            0            0 WAITFOR
 +
</pre>
 +
 
 +
Example output (extended info)
 +
Spid  Login        DB              Hostname    blocked StartTime          Dmins  Dtime Application  OS PID  Blocked(s)  Logged in ClientIP        Command
 +
------ ------------ --------------- ------------ ------- ------------------- ------ ----- ------------ ------- ----------- --------- --------------- ------------------------------
 +
    126 sa          PROD_DB1        prod-srv1          0 Mar 24 2010 12:13AM    570 09:30 isql        6181          NULL 15:25:59  12.7.3.55      SELECT
 +
    143 user1        PROD_DB1        user-hosta        0 Mar 24 2010  9:38AM      6 00:06 NULL        -              NULL 15:25:09  12.7.3.56      SELECT
 +
    32 user1        PROD_DB1        user-hostb      143 Mar 24 2010  9:38AM      5 00:05 NULL        -              NULL 15:26:04  12.7.3.57      SELECT
 +
    44 cron        cron_tempdb    prod-srv1          0 Mar 24 2010  9:40AM      3 00:03 isql        8639          NULL 15:26:04  12.7.3.55      WAITFOR
  
 
Usage summary:<BR>
 
Usage summary:<BR>
sp__mon_sql2<BR>
+
- sp__mon_sql2           ((no params), returns standard info for all spids)
sp__mon_sql2 <spid><BR>
+
- sp__mon_sql2 spid       (returns standard info for a specific 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.
+
- sp__mon_sql2 null, "x" (returns extended info for all spids)
 +
- sp__mon_sql2 spid, "x"  (returns extended info for a specific spid)
  
 
<pre>
 
<pre>
 
create procedure sp__mon_sql2
 
create procedure sp__mon_sql2
 
(
 
(
@spid int = 0
+
@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
 
as
Line 18: Line 38:
 
--    Author: Bob Holmes (Email: cambob@gmail.com)
 
--    Author: Bob Holmes (Email: cambob@gmail.com)
 
--    Usage: sp__mon_sql2 [spid]
 
--    Usage: sp__mon_sql2 [spid]
-- Last modification: bobh - if 1 is specified as the spid number then alternate info is returned (dirty hack)
+
-- 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:
 
-- INFO:
 
-- The three *'s are a grep key for a shell script to use to extract the lines needed for alerting.
 
-- 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
 
--part 1 - snapshot
 
set forceplan on
 
set forceplan on
Line 51: Line 87:
 
set forceplan off
 
set forceplan off
  
--part 2 --display spid summaries
+
--
 
+
--
if @spid=0
+
--case 1 - no params (0 and "u")
 +
if @spid=0 and @extinfo="u" -- display standard information for all spids
 
begin
 
begin
 
-- display header information for multiple spids
 
-- display header information for multiple spids
Line 77: Line 114:
 
   order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
 
   order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
 
end
 
end
else if @spid=1
+
--
 +
--
 +
--case 2
 +
else if @spid=0 and @extinfo="x" -- extended info for all spids
 
begin
 
begin
 
-- display header information for multiple spids
 
-- display header information for multiple spids
Line 103: Line 143:
 
   order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
 
   order by Dmins desc, CpuTime Desc, LogicalReads Desc, PhysicalReads Desc
 
end
 
end
else
+
--
 +
--
 +
-- case 3
 +
else if @spid <> 0 and @extinfo = "u" -- display standard info for specific spid
 
begin
 
begin
select distinct "***",
+
  select distinct "***",
 
   spid Spid,
 
   spid Spid,
 
   convert(char(12),suser_name(suid)) Login,
 
   convert(char(12),suser_name(suid)) Login,
 
   convert(char(16),db_name(dbid)) DB,
 
   convert(char(16),db_name(dbid)) DB,
 
   (case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
 
   (case hostname when "" then "-" else convert(char(12),hostname) end) Hostname,
 +
  blocked,
 
   convert(varchar(19),StartTime) StartTime,
 
   convert(varchar(19),StartTime) StartTime,
 
   Dmins,
 
   Dmins,
  blocked,
 
 
   right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
 
   right( replicate("0", 2) + convert(varchar(2),(Dmins/60)),2) + ":" + right( replicate("0", 2) + convert(varchar(2),(Dmins%60)),2) Dtime,
 
   CpuTime,
 
   CpuTime,
 
   LogicalReads,
 
   LogicalReads,
 
   PhysicalReads,
 
   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
 
   cmd Command
 
   from #processmon2
 
   from #processmon2

Latest revision as of 11:05, 24 March 2010

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.

Example output (normal usage)

     Spid   Login        DB               Hostname     blocked StartTime           Dmins  Dtime CpuTime     LogicalReads PhysicalReads Command
 --- ------ ------------ ---------------- ------------ ------- ------------------- ------ ----- ----------- ------------ ------------- ------------------------------
 ***    126 sa           PROD_DB1         prod-srv1          0 Mar 24 2010 12:13AM    570 09:30           0           42             0 SELECT
 ***    143 app_user1    PROD_DB1         user-hosta         0 Mar 24 2010  9:38AM      6 00:06          10            0             0 SELECT
 ***     32 app_user1    PROD_DB1         user-hostb       143 Mar 24 2010  9:38AM      5 00:05           0           20             0 SELECT
 ***     44 cron         cron_tempdb      prod-srv1          0 Mar 24 2010  9:40AM      3 00:03           0            0             0 WAITFOR

Example output (extended info)

Spid   Login        DB              Hostname     blocked StartTime           Dmins  Dtime Application  OS PID  Blocked(s)  Logged in ClientIP        Command
------ ------------ --------------- ------------ ------- ------------------- ------ ----- ------------ ------- ----------- --------- --------------- ------------------------------
   126 sa           PROD_DB1        prod-srv1          0 Mar 24 2010 12:13AM    570 09:30 isql         6181           NULL 15:25:59  12.7.3.55       SELECT
   143 user1        PROD_DB1        user-hosta         0 Mar 24 2010  9:38AM      6 00:06 NULL         -              NULL 15:25:09  12.7.3.56       SELECT
    32 user1        PROD_DB1        user-hostb       143 Mar 24 2010  9:38AM      5 00:05 NULL         -              NULL 15:26:04  12.7.3.57       SELECT
    44 cron         cron_tempdb     prod-srv1          0 Mar 24 2010  9:40AM      3 00:03 isql         8639           NULL 15:26:04  12.7.3.55       WAITFOR

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