Max async IO is the less known option, and one of the most powerful when it comes to tuning your SQL Server system. Modifying this value can increase the power of your SQL box and the number of transactions per second your system can handle. This article will show you how to tune this configuration option to suit with your disk subsystem, if you are running a RAID system. This value should not been increased on a one disk machine
Disks and IO
As we’ve seen in a previous article (Choosing the right disk subsystem for your SQL Server), a disk subsystem can handle a certain amount of I/Os per second, depending on the quality of your controller, of your disks and the number of disks.
One more figure concerns the number of simultaneous I/Os your disk subsystem can handle. Windows NT/2000 works with asynchronous I/Os, meaning the OS ask the disk subsystem for an I/O and doesn’t wait for the completion of this I/O to continue its work. Windows 95/98 doesn’t have this functionality, so this article deals only with Windows NT/2000.
SQL Server 6.5 and 7 on Windows NT/2000 uses this asynchronous feature and can ask for a maximum number of simultaneous asynchronous IO, thanks to its parameter max async io.
Note:
max async io does not appear in EM (though it appeared in SQL 6.5 GUI), you need to run sp_configure to check and modify its value:
sp_configure ‘max async io’
to check the value
sp_configure ‘max async io’, 64
RECONFIGURE
to modify it
Modifying max async io
By default, max async io is set to 32 in SQL 7 and to 8 in SQL 6.x. 32 is a good value for modern disk subsystem, with or without RAID. 8 is definitely too low, and if you are running a SQL 6.5, I urge you to modify this value. Running a SQL Server with a wrong value of max async io is a little bit like filling a Porsche with diesel!
Finding the right max async io value can increase the performance of your system by a magnitude of 2 or more. For example, with a Compaq Smart Array 3200 and 5 disks in RAID 5, changing the value from 32 to 96 doubled the number of transactions per second.
Of course the question is: how can I find that damned right value? Keep cool, take a beer, it’s quite simple, though long a process.
The main purpose of the tuning is finding the best max async io value to obtain the best number of transactions/second.
You have two possibilities to tune your max async io:
- You run a benchmark (like the TPC-B, provided on the BackOffice Resource Kit, or like Benchmark Factory, available at http://www.benchmarkfactory.com/)
- You use your production box live!
Running a Benchmark
The benchmark strategy is definitely the easiest one. Starting with a max async io of, by instance, 24, you run your first benchmark and record the number of tps (Transaction per seconds). Then you increase the value of max async io to 28, rune the benchmark and record the number of tps. You do this while the number of tps keeps increasing. As soon as it starts to decrease, you stop the benchmarking. The best value for max async io is the one corresponding to the best number of tps.
Using a live production box
The problem with a live production box is you need to reboot your server each time you modify the max async io value. You can probably do that only once a day. So, as with the benchmark, you start with a value of 24 and check, with the Performance Monitor, the value Average Disk Queue Length of your physical or logical disks. If the average disk queue length is less than the configured value of max async io, you can increase it. So increase it gradually, until the value of Average disk queue length is greater than max async io. At that time, the disk subsystem cannot handle the load anymore. You then come back to the last but one value.
Another way to check your choice is with the duration of a checkpoint: the shorter, the better. With an optimized value of max async io, the checkpoint will run as fast as possible, having the least effect on the production system.
Wrap-up
Max async iocan have a tremendous effect on your production server. Remember these advices:
- Tune your max async io value to increase the number of transaction per second, to lower the checkpoint duration and to keep your disks working.
- Tune again you max async io if you change your disk subsystem configuration (add or remove disks, add or remove cache on your controller).
- Use the TPC-B benchmark or a third party tool to help you tune your platform.
- Never use too high a value for max async io, as the performance will decrease fast if you go too high.
- Keep monitoring your disk subsystem to detect disk bottleneck, especially average disk queue length and % disk time.
