SQLIO Predeployment and Optimization Best Practices

• SQLIO is generally not a CPU-intensive process. However, you should monitor during the run to ensure that there are no CPU bottlenecks, which would skew the results.

• Larger I/O sizes normally result in slightly higher latencies.

• For random I/O, focus on number of I/Os per second and latency. For sequential I/Os, focus mainly on throughput (MB/s) and latency.

• Ideally, you should see some increase in your throughput as you increase the number of volumes being tested at the same time. This is dependent on the underlying storage design and specifically impacted by any spindle sharing between LUNs. You may also see increased total throughput as the size of the I/O is increased. This is, however, dependent on the specifics of your particular configuration.

• When you no longer see increased throughput when increasing the number of outstanding I/Os you probably have saturated the disk, the bandwidth of the channel, or do not have the HBA queue depth setting high enough.

• Save all of the test results. It can be helpful to share the results from your SQLIO tests with your storage vendor since the vendor has detailed knowledge about how the equipment should perform for different I/O types, RAID levels, and so on. This data will also be valuable if troubleshooting I/O problems in the future.

• If you have multiple HBAs or multipathing software in use on the host, ensure that both paths are functionally working and, ideally, that the cumulative bandwidth of the HBAs can be exhausted. One test approach to exhaust bandwidth resources between the host and storage is to use very small test files (100 MB) for SQLIO so that they reside in the cache of the storage array (that is, 100 MB). Sequential I/O issued against these small test file(s) should saturate the bandwidth of the paths and allow one to determine if there are any issues related to bandwidth. When running a test such as this, look at the cumulative MB/s attainable and related this number to the number and bandwidth of HBAs. Ideally, you should be able to almost completely saturate the cumulative bandwidth of the number of HBAs in the host.

• If test results vary wildly, check to determine if you are sharing spindles with others on the array.

• Monitoring at host and on the array during tests is ideal for complete analysis. This provides more insight into actual limiting hardware resource (disk, fiber channel (FC) ports, service processors, and so on). Monitoring strategies using System Monitor are discussed in the next section.