---Total Network I/O Checks 434994.8 367.1 38714539
Avg Net I/Os per Check n/a n/a 0.00003 n/a
Disk I/O Checks
Total Disk I/O Checks 435855.6 367.8 38791149 n/a
Checks Returning I/O 125358.1 105.8 11156875 28.8 %
Avg Disk I/Os Returned n/a n/a 0.00313 n/a
Engine Busy Utilization
Reports the percentage of time the Adaptive Server kernel is busy executing tasks on each Adaptive Server engine (rather than time spent idle). The summary row gives the total and the average active time for all engines combined.
The values reported on the Engine Busy Utilization report may differ from the CPU usage values reported by operating system tools. When Adaptive Server has no tasks to process, it enters a loop that regularly checks for network I/O, completed disk I/Os, and tasks in the run queue.
One measurement that cannot be made from inside Adaptive Server is the percentage of time that Adaptive Server had control of the CPU versus the time the CPU was in use by the operating system. The operating system provides CPU time to Adaptive Server. sp_sysmon reports how Adaptive Server uses the time the operating system provides. You should always use sp_sysmon in conjunction with the operating system’s monitoring tools to get readings for both systems.
Check your operating system documentation for the correct commands.
To reduce the time that Adaptive Server spends checking for I/O while idle, lower the sp_configure parameter runnable process search count. This parameter specifies the number of times an Adaptive Server engine loops looking for a runnable task before yielding the CPU. However, decreasing the value for runnable process search count can increase latency, decreasing performance.
“Engine Busy Utilization” measures how busy Adaptive Server engines were during the CPU time they were given. If the engine is available to Adaptive Server for 80% of a 10-minute sample interval, and “Engine Busy Utilization”
was 90%, it means that Adaptive Server was busy for 7 minutes and 12 seconds and was idle for 48 seconds.
When sp_sysmon samples the counters (by default, every 100 milliseconds), each engine indicates what it is currently doing. For example, if it is executing a task, it reports that it is "CPU Busy,"; if it is idling, it reports that it is
"Idle"; if it is idling and has at least one pending asynchronous disk IO, it reports "IO Busy."
“Engine Busy Utilization” can help you decide whether there are too many or too few Adaptive Server engines. Adaptive Server high scalability is due to tunable mechanisms that avoid resource contention.
By checking sp_sysmon output for problems and tuning to alleviate contention, response time can remain high even at “Engine Busy” values in the 80 to 90%
range. If values are consistently very high (more than 90%), it is likely that response time and throughput could benefit from an additional engine.
The “Engine Busy Utilization” values are averages over the sample interval, so very high averages indicate that engines may be 80% busy during part of the interval. Spinlock contention increases CPU usage, and should be checked if the server experiences high CPU utilization. In-memory table scans also increase CPU usage, which you can decrease by tuning queries appropriately.
When engine utilization is extremely high, the housekeeper wash task writes few or no pages out to disk (since it runs only during idle CPU cycles.) This means that a checkpoint finds many pages that need to be written to disk, and the checkpoint process, a large batch job, or a database dump is likely to send CPU usage to 100% for a period of time, causing a perceptible dip in response time.
If the “Engine Busy Utilization” percentages are consistently high, and you want to improve response time and throughput by adding Adaptive Server engines, check for increased resource contention in other areas after adding each engine.
In an environment where Adaptive Server is serving a large number of users, performance is usually fairly evenly distributed across engines. However, when there are more engines than tasks, you may see some engines with a large percentage of utilization, and other engines may be idle. For example:
Engine Busy Utilization CPU Busy I/O Busy Idle --- --- --- ---
Kernel utilization
--- --- --- ---
Summary Total 436.3 % 21.1 % 242.6 %
Average 62.3 % 3.0 % 34.7 %
In an SMP environment, tasks have soft affinity to engines. Without other activity (such as lock contention) that could cause a task to be placed in the global run cue, the task continues to run on the same engine.
CPU Yields by Engine
Number of times each Adaptive Server engine yielded to the operating system.
“% of total” data is the percentage of times an engine yielded as a percentage of the combined yields for all engines.
“Total CPU Yields” reports the combined data over all engines. However, an engine with one or more pending asynchronous disk I/Os does not yield, even when runnable process search count is exhausted.
If the “Engine Busy Utilization” data indicates low engine utilization, use
“CPU Yields by Engine” to determine whether the “Engine Busy Utilization”
data reflects a truly inactive engine or one that is frequently starved out of the CPU by the operating system.
When an engine is not busy, it yields to the CPU after a period of time related to the runnable process search count parameter. A high value for “CPU Yields by Engine” indicates that the engine yielded voluntarily.
• Engine Busy Low/CPU Yields Low – a higher value for the I/O Busy%
column than for the CPU Busy% column indicates a large amount of pending I/O. Look for changes you can make at the operating system level to solve bottlenecks in I/O processing. If the Adaptive Server system CPU time is too high (based on operating system output), decrease the value for runnable process search count.
• Engine Busy Low/CPU Yields High – engine is inactive.
• Engine Busy High/CPU Yields Low – Adaptive Server is very busy and has jobs to run. Adding engines will probably help performance.
• Engine Busy High/CPU Yields High – this should be the case when a normal number of user connections running simple queries. It is likely that lowering runnable process search count will have little or no effect.
Adding engines is also unlikely to help, unless ‘Engine Busy Utilization”
is very high.
See Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide, Volume 1.
CPU Yields by Engine per sec per xact count % of total --- --- --- ---
---Engine 0 73.5 1.8 44087 26.2 %
Engine 1 40.4 1.0 24224 14.4 %
Engine 2 23.7 0.6 14208 8.4 %
Engine 3 36.2 0.9 21746 12.9 %
Engine 4 38.7 1.0 23207 13.8 %
Engine 5 41.3 1.0 24760 14.7 %
Engine 6 26.8 0.7 16108 9.6 %
--- --- ---
---Total CPU Yields 280.6 6.9 168340