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

AdvancedSQLIO
In the intermediate SQLIO section, you saw how to use multiple batch files to test the performance of a
storage subsystem as you changed a single I/O characteristic, the number of outstanding I/Os. This only
gives you part of the picture of the overall storage subsystem's performance, and you can see that there
are a number of other parameters you need to vary to get a full picture of the performance.

The other parameters you have yet to vary are:

❑ B - Number of threads

❑ C - Number of Files

❑ D - Duration

❑ E-BlockSize

Then there are a number of other factors you might want to test for as well, including:

❑ The file size

❑ Where the file is on the disk

❑ Whether the file was just created

The file size affects the ability of any cache to contain the whole file. A file that's smaller than the available
cache can be completely contained in cache. The other factor with file size is how much data you are
reading and writing in the test. With a small file size and a high-speed disk system, you stand a good
chance of completely reading and writing the whole file, which will guarantee the entire file is in cache
by the end of the test. With a much larger file and a slower disk, you stand a good chance that most of
the file will not be cached by the end of the test. Depending on what you want to test, you can alter your
filesizeaccordingly.

Accessing files located on the outside sectors of a disk is faster than accessing files on the inside of a
disk. This is due to the geometry of the disk and the fact that the head, which is fixed, has a higher speed
over the surface of the disk on the outside of the disk compared to when it's over the inside. The impact
of this is that files created on the outside of the disk will have higher performance than files created on
the inside of the disk. One way to observe this is to start with an empty disk and then create 8 test files
that completely fill the disk. Test the performance of I/O to the first and last files and compare the results
to see this effect.

If the file was created immediately before you run the test, then it will remain cached at the start of the
test. A file that's in the cache will show much faster access times than a file that's not in the cache. It's
important to test both cached and uncached performance as there will be a large difference between the
two sets of results. To test a cached file (warm cache), create a small file ( < 1.0 thesizeofthecache)

and run the test immediately after creating the file. To test an uncached file (cold cache), create multiple
large files ( > 2-4

×

size of the cache) and run the test on the first file created. Make sure the test duration
and I/O rates are such that the whole file will not be read or written during the test.

×

Just because these variables are there, that doesn't mean that it's a valuable test to perform. Some of
these variables produce useful data; others don't deliver real performance data but can be used to confirm
the validity of other results.

Examples of parameters that don't provide good test results are Number of threads, Number of files,
and duration of the test (once the test duration is above 3-5 seconds). If you run a series of tests while