Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Participant
0 Kudos
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.


Labels in this area