Disk usage
SQL Server is heavily disk bound, so solving disk bottlenecks can make a big difference. If you found memory shortages in the previous section, fix those first, because a memory shortage can lead to excessive disk usage in itself. Otherwise check these counters to see if there is a disk bottleneck for some other reason.
Categories: PhysicalDisk and LogicalDisk
% Disk Time - Percentage of elapsed time that the selected disk was busy reading or writing.
Avg. Disk Queue Length - Average number of read and write requests queued during the sample interval.
Current Disk Queue Length - Current number of requests queued.
If % Disk Time is consistently over 85%, the disk system is stressed. Avg. Disk Queue Length and Current Disk Queue Length refer to the number of tasks that are queued at the disk controller or are being processed. You want to see a counter value of 2 or less. If you use a RAID array where the controller is attached to several disks, you want to see counter values of 2 times the number of individual disks or less.
Part 8 will show how to fix disk issues.
It is very important to look at each PhysicalDisk individually, otherwise you get an average.
In my opinion the numbers above indicate a severely stress disk subsystem.
At half the above levels the disks are probably affecting performance.
The Disk Usage section is not relevant to SAN disks
Actually it is. First see above "look at each disk individually."
If all you can get from your SAN is over 100% complain to the vendor to fix their driver.
More important is the fact that a SAN is just another way of connecting disks to the computer. It does not make any one disk spin faster nor the heads settle down to a track any faster nor the performance advantages and disadvantages of the various RAID configurations. The only performance attribute the SAN brings to the table is higher total bandwidth. The rest of a SANs advantages are administrative. Configuring a three disk RAID 5 for the primary data is equally bad on a SAN, a high end RAID controller or JBOD.
Having the transaction log file on dedicated spindles (eg dedicated heads) is still as important.