I/O system overloads

Adaptive Server and the operating system place limits on the number of outstanding I/Os for the server as a whole and for each engine. The configuration parameters max async i/os per server and max async i/os per engine control these limits for Adaptive Server.

See your operating system documentation for more information about configuring I/Os for your hardware.

The configuration parameter disk i/o structures controls the number of disk control blocks that Adaptive Server reserves. Each physical I/O (each buffer read or written) requires one control block while it is in the I/O queue.

See Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide: Volume 1.

If Adaptive Server tries to issue asynchronous prefetch requests that would exceed max async i/os per server, max async i/os per engine, or disk i/o structures, it issues enough requests to reach the limit and discards the remaining requests. For example, if only 50 disk I/O structures are available, and the server attempts to prefetch 80 pages, 50 requests are issued, and the other 30 are discarded.

sp_sysmon reports the number of times these limits are exceeded by asynchronous prefetch requests. See Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.

Try to tune the system so there are no delayed I/Os. If there are I/Os delayed by: