How Fast and Robust Is Your Storage? - Professional SQL Server 2005 Performance Tuning - page 454

sequential I/O. This maximizes the performance of the storage. It doesn't necessarily give you the best
set of results to see what real-life performance will be like.
To see the storage at its worst, see the results from a test where there is a small block size and little ability
for the storage to use its cache such as in Test A or Test B.
Don't forget that it's not always going to be the systemwith the highest throughput that is the best system
for any given solution. You have to ensure that the I/O characteristics of the system tested match the I/O
requirements of the target system. It's no good choosing a configuration that delivers excellent results for
large block size sequential reads and writes if you are building a system that will be issuing very high
numbers of small block size random reads and writes.
SQLIO Best Practices
Make sure the size of the file is 2-4 times larger than your disk cache.
Run the test for long enough to ensure you're getting the results you want.
Share your results with the rest of the team, including the hardware vendor, to validate
the results and investigate unexpected outcomes.
Run a full set of tests every time you change anything in the storage configuration.
Compare I/O perf results with observations from Perfmon captured daily, weekly, and
monthly.
Look for changes in I/O performance over time to indicate a potential performance-to-
reliability issue.
Test failure scenarios. Pull a disk from your raid array and see what happens to
performance with the disk missing, and when the array is rebuilding when you
replace it!
Storage Reliability
To help build your confidence in the robustness and reliability of your storage subsystem you need to run
a stress test. There are a number of tools you can use for this. LoadSim and JetStress are I/O stress tools
optimized for Exchange I/O patterns. IOMeter is another highly configurable open source I/O stress
tool. This chapter focuses on the latest I/O stress test tool from Microsoft: SQLIOSim.
SQLIOStress is a tool written by the SQL Server support team. It was the preferred tool for stress testing
SQL Server I/O, but has since been replaced by SQLIOSim.
SQLIOSim
SQLIOSim is a new I/O stress tool that simulates SQL Server 2005 I/O patterns. It was written by the SQL
Server engine team and is based upon the same code that SQL Server uses for its I/O. Like SQLIOStress,
SQLIOSim doesn't require SQL Server to be installed to run and is a standalone stress test tool.