What about SQL Server? Processor: Percent User Time measures the amount of processor time consumed by non-kernel level applications. SQL is such an application. If this is high and you have multiple processes running on a server, you may want to delve further by looking at specific process instances through the instances of the counter Process: Percent User Time. This can be very useful for occasions such as when our operating system engineers installed new anti-virus software on all our servers. It temporarily brought them to their knees until we were able to determine the culprit through analyzing Process:Percent User Time for the anti-virus software instance.
Disk Tuning and Performance Monitoring
Begin disk performance monitoring by looking at the following counters:
- PhysicalDisk: Percent Disk Time
- PhysicalDisk: Current Disk Queue Length
- PhysicalDisk: Avg. Disk Queue Length
Applications and systems that are I/O-bound may keep the disk constantly active. This is called disk thrashing.
You should always know how many channels, what types of arrays, how many disks are in each array, and which array/channel your data and transaction logs are located on before you start thinking about disk performance tuning.
The PhysicalDisk: Percent Disk Time counter monitors the percentage of time that the disk is working. Check the PhysicalDisk: Current Disk Queue Length counter to see the number of requests that are queued up waiting for disk access.
It is important at this point to be familiar with your disk subsystem. If the number of waiting I/O requests has a sustained value more than 1.5 to 2 times the number of spindles making up the physical disk, you have a disk bottleneck. For example, a RAID 5 configuration with seven spindles/disks would be a candidate for disk performance tuning should the Current Disk Queue Length continually rest above 12-14.
To improve performance in this situation, consider adding faster disk drives, moving some processes to an additional controller-disk subsystem, or adding additional disks to a RAID 5 array.
Most disks have one spindle, although RAID devices usually have more. A hardware RAID 5 device appears as one physical disk in Windows NT PerMon or Windows 2000 sysMon. RAID devices created through software appear as multiple instances.
WARNING: The Percent Disk Time counter can indicate a value greater than 100 per cent if you are using a hardware based RAID configuration. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine the average number of system requests waiting for disk access. Again, this is indicative of a performance problem if a sustained value of 1.5 to 2 times the number of spindles in the array is observed.
Published with the express written permission of the author. Copyright 2003.
]]>