To set the max async IO optionUse the max async IO option to configure the maximum number of outstanding asynchronous disk input/output (I/O) requests that the entire server can issue against a file (not database).
The default setting for max async IO is 32: at any time, 32 reads and 32 writes may be outstanding per file. Servers with nonspecialized disk subsystems do not benefit from increasing the value set in max async IO; the default setting is adequate. High performance servers with intelligent disk subsystems or servers that are using disk striping may gain some performance benefit by increasing the max async IO setting because they have the ability to rapidly accept multiple asynchronous I/O requests from a Microsoft® Windows NT® application, such as Microsoft SQL Server™.
Increasing the value set in max async IO usually results in performance improvements during disk intensive operations. However, you can reach the limit when further use actually degrades system performance. The actual value you set in this option and the resulting performance increases may vary depending on the system’s controller, driver, associated disk subsystem, and the database’s I/O profile.
Before you change the setting of max async IO, determine the optimum setting for a given situation using either the Microsoft TPC-B Benchmark Kit or a system-specific benchmark. Test your system using the default setting and then increase the setting slowly, while performing subsequent test runs. When you reach the point at which there is no further increase in performance, you have found the optimum value. In the absence of any empirical testing, it should be left at the default.
Note Do not change the max async IO option unless your databases span multiple physical drives, or you are using disk striping and your database has separate disk controllers or a smart disk controller, such as a Compaq SMART SCSI-2 Array Controller.
You can increase performance in the following disk subsystems:
First, consider how a data transfer occurs with a single nonintelligent controller and four drives. In the outbound transfer sequence, the device driver transfers a buffer of data to the controller’s onboard buffer. This takes place rapidly by direct memory access (DMA), shared memory, or programmed I/O, in a few hundred microseconds at typical bus rates. Next, the controller (under varying amounts of device driver assistance) must command the necessary seek operations from the drive, an operation that can take up to 50 milliseconds. That amount of time is hundreds of times longer than the bus-to-controller transfer. Following this, the actual data is transferred from the controller buffer to the disk drive at the transfer rate determined by the drive type. There may also be rotational latency involved prior to starting the transfer. During this interval in many systems, the device driver and the task that called it must simply wait for the hard drive. Operations cannot be performed on the second and subsequent drives until the first drive finishes because the controller does not have the necessary logic to keep track of multiple pending operations.
In a system with four controllers, where each controller is attached to its own drive and Windows NT striping is used, a transfer sequence can begin immediately on the second or subsequent controller/drive. In this case, each of the four drives can be in different phases of the transfer because each drive has its own controller to keep track. Using Windows NT asynchronous I/O in this hardware configuration can be beneficial because a pool of outstanding I/O requests can be built, which the drive subsystem processes in parallel four at a time. Because the rate at which the drive subsystem processes the requests can vary, it may be useful to build up a pool of outstanding requests from SQL Server to ensure that the subsystem is used to capacity. Depending on many system-specific factors, it may be useful to increase max async IO from 32 to a higher number to take advantage of this.
The expansion capacity of most systems precludes using a controller per drive. However, technology now makes it possible to include the capability of multiple nonintelligent controllers in a single intelligent controller. In this case, the single controller can connect to 2 through 16 drives. It can accept multiple I/O requests rapidly from the device driver, maintaining simultaneous transfer operations to the attached drives, which are usually striped in a RAID array. In this situation, depending on the capability and configuration of the controller, increasing max async IO may increase performance. The actual value used varies depending on the server and controller, and within a given server or controller by disk subsystem configuration, and within a certain disk subsystem configuration by I/O characteristics of the application. For this reason, using the Microsoft TPC-B Benchmark Kit is recommended, because it can provide an objective number for improvements to be measured. This benchmark can also be customized to represent the intended application more closely.
max async IO is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change max async IO only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
To set the max async IO option
| RECONFIGURE | sp_configure |
| Setting Configuration Options |