Analyzing Disk Usage
Increased disk time on your database server usually means that your system is using more virtual memory due to a lack of RAM (analyze memory issues as described earlier). Monitor these counters:
- Physical Disk: Avg. Disk Queue Length. This counter
indicates how busy a drive is becoming due to excessive I/O requests. If one
drive is getting much more activity than another, try moving some SQL Server
files from the busy drive(s) to other drives that are not so busy. This
technique will help to spread the disk I/O activity and reduce bottlenecking on
If memory is good and the above strategies still don't decrease I/O, look at your current disk configuration for ways to increase disk I/O performance. A good configuration for performance is multiple SCSI drives using a RAID (redundant array of inexpensive disks) configuration (preferably 1 or 5). A 32-bit SCSI controller will transfer larger chunks of data faster, thus decreasing I/O time. A RAID configuration allows for concurrent asynchronous I/O requests, speeding up disk performance immensely.
- SQL Server: Databases: Log Flush Wait Time and SQL Server: Databases: Log Flush Waits/Sec. Disk I/O performance becomes especially important when SQL Server writes a transaction to the transaction log on the hard drive. The busier your databases are, the more disk I/O time will increase. These two counters tell you whether your disk I/O performance is sufficient for handling transaction log requests. The greater the wait time, the longer SQL Server is waiting to write the next transaction to the disk subsystem. As your server gets busier, these values increase because there are more sequential writes to the log. Make sure that I/O performance is sufficient by keeping these numbers reasonable under peak conditions.