Tune 'max async IO' and get the best out of your disk subsystem
The "max async IO" configuration setting for SQL Server enables you
to tune disk IO for increased performance. Unfortunately, though, this setting is often overlooked.
One of the main bottlenecks for any database system is disk IO. Reading data from a hard drive disk is many, many times slower than reading data from RAM. This is why database systems like SQL Server depend so heavily on having lots of free RAM--they cache data in RAM that has already been read from disk, to save having to read it from disk again later.
Obviously this does not help you out the first time that you read a particular item of data, when your cache is empty (as it is when SQL Server first starts up) or when the cache is full and the data you need has been deleted from the cache.
In these instances you need to get the most out of your disk subsystem, as indeed you do when changing data in your tables. This is where the 'max async IO' setting comes in. Max async IO basically governs the number of simultaneous IO requests that SQL Server can send to the IO subsystem.
Older IDE disk systems cannot really handle simultaneous requests (nor
can systems running Windows 95/98) but NT-based SCSI systems can, and the latest generation of
intelligent SCSI RAID controllers can cope with many IO requests at the same time. Since the default settings for 'max
async IO' (which varies from version to version) are often pretty conservative, if you do have such a system, tweaking this variable can definitely improve your disk system's performance.
The problem is that there are an almost infinite variety of combinations of RAID controllers, RAID strategies, and underlying SCSI drives, making it difficult to easily tell what the best setting should be. You have to discover this by trial and error.
Needless to say, you do not want to be playing "trial and error" games with business-critical live systems, so the time to do this is when your server is shiny and new.
The trial and error system works by simply increasing the value of this setting, stopping and restarting the box so that the setting can take effect, then throwing a lot of disk IO at the server. If your system copes without complaining, increase the setting again until it does complain, at which point you have found the threshold for you disk subsystem. When you do find the limit of the disk subsystem you will start to get 'bufwait' errors in the event log.
Once you are there, back off the setting slightly so that you have some breathing space and..... job done!
The only problem is how to generate the massive amounts of IO requests needed to push your disk system to the limit. To do this you can take a look at these MS provided tools for
SQL version 6.5 and earlier and version
on Threshold Management and MSDN
on Performance Tuning