Difference between revisions of "Sp mon sql2--ASE spid activity monitor"
From SybaseWiki
m |
m (corrected spacing) |
||
(3 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 | + | 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 | + | - sp__mon_sql2 ((no params), returns standard info for all spids) |
− | sp__mon_sql2 | + | - sp__mon_sql2 spid (returns standard info for a specific spid) |
− | sp__mon_sql2 | + | - 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 = | + | @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] | ||
− | -- | + | -- 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 | ||
− | -- | + | -- |
− | + | -- | |
− | 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= | + | -- |
+ | -- | ||
+ | --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, | ||
− | |||
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 12: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