Disk Tuning

The aim is to ensure that at all times, all the disks available to the database are seeing the same level of disk activity. This will never happen, as the nature of the applications tend to cause different disks to receive different loads depending on what the user is requesting. A more realistic aim is to ensure that there are no 'hot disks' ie no disk is doing more than any other under typical load conditions. Life is further complicated when the system needs to be OLTP based during 'normal' user hours but need to be DSS biased for overnight reporting purposes.

The key to getting the I/O balance correct is to exam the entire system and concentrate on few SQL statements. The OS requirements for disk access must also be considered and allowed for in any proposed disk layout.

OLTP systems by their very nature have a larger number of transactions and hence the disks see significant INSERT/UPDATE/DELETE statements. Therefore, more emphasis should be place on the logical and physical log placement. However, if an OLTP system does not use logging, then the importance of the logical logs is reduced. With unbuffered logging, the number of writes to the logical logs will increase dramatically. Also bear in mind a very active table may receive more I/O than the physical and logical logs.

Decision support systems tend to use ORDER/GROUP BY statements and hence can require significant areas of DBSPACETEMP which will see a high level of activity. So in these systems the physical and logical logs placement are not as important as the temporary areas.