Monitoring ASE CPU/Disk/Network activity versus User Thread Activity

[sladebe]

In ASE there are a lot of tools to say what ASE itself is busy doing.  E.g., engines (threads) percent busy, I/O's per second, packets per second, and so on.   But there's much less information about what user threads are doing.   But there is some...

System Activity Monitoring with sp_sysmon

In the SAP Adaptive Server Enterprise (ASE) product, when you run sp_sysmon in threaded mode (kernel mode="threaded, aka use OS level threads), you get info about "Engine Utilization" attributes like:

• User busy


• System Busy


• I/O Busy


• Idle

User and System Busy make sense, basically the percentage of time an engine is busy doing user or system type CPU work.

But I/O busy is weird.  As currently implemented (ASE 16.0 SP03 to ?), if any engine is idle, and there is at least one I/O operation pending, that engine is counted as waiting for I/O.  Ie., if you have 8 engines with nothing to do, and one I/O pending, all 8 engines are counted as I/O busy.

(For more details,, see KBA 1863319 "sp_sysmon reports higher I/O Busy after upgrade to version 15.7 " which references not yet implemented CR 757246).

This causes I/O Busy to show lots of engines as "I/O busy" even though their all available to do work.  Note, this also causes @@idle to report numbers which are too low (making the server look busy)

User Thread Activity Monitoring with sp_sysmon

But let's say they fix "I/O Busy".  You're still stuck with the fact that sp_sysmon monitors how busy engines are, but doesn't measure what user processes are waiting for.

Well, actually, that's not entirely true.   sp_sysmon does show "Runnable Tasks" which are tasks waiting for a CPU, but it doesn't show tasks waiting for disk I/O.

Well, actually, it turns out that's not entirely true either.   You can run a sp_sysmon with fancy options like:

sp_sysmon "00:01:00",appmgmt,appl_and_login

and it will show you an "Application Management" section including stats keyed by application name and login name that does show, to a certain degree, what applications are waiting for:

------------------------------------------------------------------------------- 

  Application->Login:   myapplication->mylogin                                         

 

  Application Activity            per sec      per xact       count  % of total

  -------------------------  ------------  ------------  ----------

    CPU Busy                          0.3           0.0          17       0.3 %

    I/O Busy                        109.9           9.1        6591      99.7 %

    Idle                              0.0           0.0           0       0.0 %

 

    Number of Times Scheduled        57.9           4.8        3473       n/a   

 

  Application Priority Changes    per sec      per xact       count  % of total

  -------------------------  ------------  ------------  ----------  ---------- 

  Total Priority Changes              0.0           0.0           0       n/a   

 

  Application I/Os Completed     per sec      per xact       count  % of total

  -------------------------  ------------  ------------  ----------  ---------- 

    Disk I/Os Completed               0.0           0.0           1       0.0 %

    Network I/Os Completed            0.1           0.0           4       0.1 %

  -------------------------  ------------  ------------  ----------

  Total I/Os Completed               69.2           5.8        4152  <-- Not accurate, ignore (CR 818885)

 

  Resource Limits Violated      per sec      per xact       count  % of total

  -------------------------  ------------  ------------  ----------  ---------- 

  Total Limits Violated               0.0           0.0           0       n/a   

So this does tell you that a user process is spending most of it's time waiting for I/O.

The "I/O Busy" statistics seem to include network I/O busy and disk I/O busy.    It would've been nice if those were broken out, but you can maybe infer what's going on from the "I/Os Completed" section.   Here it looks pretty balanced.

I wonder if cached reads show up as CPU Busy activity.  I guess some mysteries have to be left unresolved to keep life exciting.

Note the "Total I/Os Completed" is inaccurate because of a bug (CR 818885), so ignore that until it's fixed.