INF: Max Async IO Configuration Parameter

  - Microsoft SQL Server version 4.21

SUMMARY

SQL Server for Windows NT has a configuration parameter called Max Async IO that can be viewed or changed with the sp_configure stored procedure. This parameter controls the number of outstanding asynchronous disk input/output (I/O) requests the SQL Server checkpoint and lazywriter threads use. The default setting is 8, which is adequate for most systems. However, high performance servers with intelligent disk subsystems may gain some performance benefit by increasing this number.

MORE INFORMATION

SQL Server for Windows NT uses the asynchronous I/O capability of the Windows NT operating system. Examples of these are the Win32 API calls ReadFile(), ReadFileEx(), WriteFile(), and WriteFileEx(). See the Microsoft Windows Software Development Kit (Win32 SDK) for more information. Asynchronous, or overlapped I/O, refers to the ability of a calling program to issue an I/O request and without waiting for completion to continue with another activity. When the I/O finishes, the operating system will notify the program via a callback or other Win32 synchronization mechanism.

This has two main advantages. The first is it makes implementation easier for an application designer, since the operating system can be used to perform async I/O rather than having to simulate this capability in the application. The second advantage is that the multiple outstanding I/O requests can drive certain high performance disk subsystems at greater performance levels than would be otherwise possible.

This is generally only possible with very high performance intelligent disk subsystems. Examples include, but are not limited to, the Compaq SMART SCSI- 2 Array Controller, Mylex DAC960 Disk Array Subsystem, and the Tricord and Sequent SCSI Intelligent Storage Subsystems. Contact your hardware vendor for more information on how the particular disk subsystem handles Windows NT async disk I/O.

The reason is that only these types of systems have the specific features necessary to rapidly accept multiple async I/O requests from a Win32 application such as SQL Server. On these systems increasing the Max Async IO parameter of SQL Server can result in performance improvements during very disk intensive operations. The actual setting used for this parameter and the resultant performance increase will vary depending on the exact hardware and database I/O profile. It should not be set arbitrarily high, since inordinate async I/O consumes system resources.

Please see article Q98893 "INF: Limit on the Number of Bytes Written Asynchronously" for more information.

It is recommended the optimum value be determined empirically for a given situation using either the Microsoft TPC-B Benchmark Kit or a customer- specific benchmark. The procedure would be to take a test run with the default of 8, then increase the parameter slowly while taking subsequent test runs. When no further performance increase is noted, the optimum value has been found. In the absence of any empirical testing, it should be left at the default.

NOTE: Non-specialized disk subsystems will not benefit from increasing this parameter, and the default setting will be adequate.

The reason for the performance increase can be seen by considering three different types of disk subsystems. The first is a non-intelligent controller attached to four disk drives. The second is four non-intelligent controllers, each attached to a single disk drive. The third is a single intelligent controller attached to four disk drives.

First, consider how a data transfer occurs with a single controller and four drives. In the outbound transfer sequence, the device driver transfers a buffer of data to the controller's on-board buffer. This takes place very rapidly via DMA, shared memory, or programmed I/O, typically in a few hundred microseconds at typical bus rates. Then the controller (under varying amounts of device driver assistance) must command the necessary seek operations from the drive that can take up to 50 milliseconds, which 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 which 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 the case of four controllers (each attached to its own drive) if Windows NT or Windows NT Server striping is used, a transfer sequence can immediately begin on the second or subsequent controller/drive. In this case the four drives can independently be in different phases of the transfer since each has its own controller to keep track of this. Using WinNT async I/O in this hardware configuration can be beneficial since a pool of outstanding I/Os can be built up, which the drive subsystem can process in parallel four at a time. Since 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 NT in order to ensure the subsystem is used to capacity. Depending on many system-specific factors, it may be useful to increase the SQL NT Max Async IO parameter from 8 to a higher number to take advantage of this.

The expansion capacity of most systems precludes using a controller per drive. However, technology advances now make it possible to effectively include the capability of multiple non-intelligent controllers in a single intelligent controller. In this case, the single controller may connect to 2 to 16 drives. It can rapidly accept multiple I/O requests from the device driver, maintaining effectively 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 the SQL NT Max Async IO parameter could increase performance. The actual value used will vary depending on the server and controller and within a given server/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, which can provide an objective number for improvements to be measured. This benchmark can also be customized to more closely represent the intended application.

The threads which comprise SQL NT use async disk I/O for both normal transfers as well as batch and lazywriter transfers. Lazywriter is a SQL NT thread that exists beginning on version 4.21. Its purpose is to flush dirty cache pages in the background and maintain a list of readily-available free cache pages. (See the 4.21 Release Notes for more information.)

The Max Async IO parameter does not limit the number of outstanding async I/Os from non-batch and non-lazywriter transfers, because these rarely accumulate an excessive number of I/Os. This contrasts with the checkpoint/lazywriter threads, which more typically need to process larger amounts of data when flushing dirty cache pages. The parameter prevents the I/O subsystem from being flooded with an excessive number of outstanding requests when the checkpoint/lazywriter threads become active. You can monitor the number of outstanding SQL Server read and write requests using performance monitor.

The term "batch I/O" refers to a group of async I/Os the checkpoint or lazywriter threads attempt to process at a time. For example, if the Max Async IO parameter is set to 8, then the maximum number of IOs in a single batch these threads attempt will be 8. A batch, in this case, does not consist of a single 8 page I/O, but rather 8 asynchronous single-page I/Os.

When considering such matters, do not overemphasize the importance of physical and system-layer performance tuning. In most cases the performance increases available from other areas such as index, query, and database design will be much greater, and less expensive to achieve. We encourage prioritizing examination of these areas above that of the system area.

The allowed upper limit for the Max Async IO parameter varies depending on the version of SQL Server for Windows NT. Version 4.20 and 4.21 allowed an essentially unlimited value, which was reduced to 50 for version 4.21A. This was done to prevent accidental mis-configuration to an unreasonable value. The value 50 is higher than most current high-performance disk subsytems can benefit from. However, if unforeseen technical advances develop that would necessitate increasing this limit beyond 50, it can be done using the following technique.

NOTE: This should not be done unless you have an expert level of system and SQL knowledge.

1. sp_configure "allow", 1

2. reconfigure with override

3. update spt_values set high=255 where name='max async IO'

4. sp_configure "max async io", 70 (where 70 is for example the new value)

5. reconfigure with override

6. restart SQL Server

Also, see the Windows NT Resource Kit, volume 3 for more general NT performance information. This volume is titled "Optimizing Windows NT", by Russ Blake, ISBN 1-55615-619-7, and contains nearly 600 pages of recommendations concerning tuning Windows NT and Win32 applications.