Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 19, 2000

Tuning Max Async IO To Get The Most of Your Disk Subsystem

By Marc Israel

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 io can 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.


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM