If your I/O subsystem is working efficiently, then each time SQL Server wants to write or read data, it can without waiting. But if the load on the server is too great, then reads and writes will have to wait, each taking their turn. This can significantly reduce SQL Server’s performance.
One of the best ways to monitor your server’s I/O subsystem is to use the PhysicalDisk Object: Avg. Disk Queue Length to monitor each disk array in your server. If the Avg. Disk Queue Length exceeds 2 for continuous periods (over 10 minutes or so) for each individual disk drive in an array, then you probably have an I/O bottleneck for that array. You will need to calculate this figure because Performance Monitor does not know how many physical drives are in arrays.
For example, if you have an array of 6 physical disks, and the Avg. Disk Queue Length is 10 for a particular array, then the actual Avg. Disk Queue Length for each drive is 1.66 (10/6=1.66), which is within the recommended 2 per physical disk.
If your server has an I/O bottleneck, consider these potential solutions: adding drives to an array (if you can), getting faster drives, adding cache memory to the controller card (if you can), using a different version of RAID, getting a faster controller, or reducing the load on the server.
Before using this counter under NT 4.0, be sure to turn it on manually by going to the NT Command Prompt and entering the following: “diskperf -y”, and then rebooting your server. . If you are running Windows 2003 or above this counter is turned on by default.
*****
The Physical Disk Object: % Disk Time counter is a handy tool for several reasons. This counter measures how busy a physical array is (not a logical partition or individual disks in an array). It provides a good relative measure of how busy your arrays are, and over a period of time, can be used to determine if I/O needs are your server are increasing, indicating a potential need for more I/O capacity in the near future.
As a rule of thumb, the % Disk Time counter should run less than 55%. If this counter exceeds 55% for continuous periods (over 10 minutes or so), then your SQL Server may be experiencing an I/O bottleneck. If you suspect a physical disk bottleneck, you may also want to monitor the % Disk Read Time counter and the % Disk Write Time counter in order to help determine if the I/O bottleneck is being mostly caused by reads or writes.
Also, this counter is a good indicator of how busy each array on your server is. By monitoring each array, you can tell how well balanced your I/O is over each array. Ideally, you want to distribute the I/O load of SQL Server as evenly as possible over your arrays, and this counter will tell you how successful you have been doing this.
Before using this counter under NT 4.0, be sure to turn it on manually by going to the NT Command Prompt and entering the following: “diskperf -y”, and then rebooting your server.
*****
If you are not sure what to make the fillfactor for your indexes, your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters: Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. When you run both counters on an array, you should get a good feel for what percentage of your I/O’s are reads and writes. You will want to run this over a period of time representative of your typical server load to ensure that your data is good.
Once you know the ratio of disk write to reads, you now have the information you need to help you determine an optimum fillfactor for your indexes. If you find that you have a high rate of writes relative to reads, then the fillfactor needs to be large enough so that there is room for data to be inserted into your SQL Server tables without causing excessive page splits. If you find that you have a high rate of reads relative to writes, then the fillfactor can be very small, as the risk of page splits occurring because of a lack of room on a page is minimal.
Before using this counter under NT 4.0, be sure to turn it on manually by going to the NT Command Prompt and entering the following: “diskperf -y”, and then rebooting your server This counter is turned on by default.
]]>