Gauge the throughput of disk subsystem
Having a tool to benchmark or guage the system throughput is a definite helping hand from every Database Administrator out there. The basic function that should include in such tool is 1) Establish a base-line so that you know ahead of time the maximum throughput and response time to expect 2) Identify any performance issues quickly and resolve them.
This process is quicker because the issues are only due to the I/O subsystem since the DBMS has not been deployed yet. I would recommend to take a running tests and some of the key points to keep in mind are the following:
- Use test files which are significantly larger than SAN cache.
- Make sure to test a combination of different IO types and sizes. We generally try to include the following in the tests:
- Large amount of random 8K writes (checkpoint).
- Sequential IO’s of 2K – 64K in size (log).
- Sequential reads/writes of 8 – 256K (read-ahead/bulk load).
- Test all data paths independently or, if multipathing is used, make sure to monitor this and ensure it is working as expected during tests.
- Understand the theoretical limits of your configuration prior to running tests. Remember each component in the path from host to disks has an associated throughput (i.e., HBA, fiber channel switch ports, fiber channel array ports, service processors, disks…).
- Adjust test duration so that enough time elapses to exhaust cache on the array. On a dedicated array tests will generally run very fast at the beginning and slow as the array cache is filled. Similarly, wait for some time between tests to allow the array cache to flush.
Tools such as SQLIO.exe or IOMeter can be used for this base lining.
You can download SQLIO from here and IOMeter from here.
Recently Microsoft has released SQLIOSIM to provide better results that the previous tools, you may find bit of problem to know about this tool by the way of documentation. The only reference I could find is this KBA.
For a more in depth look at the internals of SQL Server IO see the paper and for SQL Server 2005 Physical Database storage design refer to this paper.
In addition, please review the paper on disk subsystem performance.