Some useful stuff for disk & SAN benchmarking

After toying with SQLIO, I found it a bit aggravating in deciphering it's abundance of output data.
I found a useful Powershell script by Jonathan Kehayias that takes your output test file from SQLIO, and creates an Excel spreadsheet with charts to visualize the results. VERY cool utility.

After running your SQLIO tests, just run the powershell script and input the filename, and magically, Excel opens and starts pulling the raw data from the textfile into it and graphing it. THIS is how IT is supposed to be!

After installing SQLIO, the first thing I did was to setup the param.txt file for SQLIO (in the install directory).

I removed all contents and added this:

o:\testfile.dat 8 0x0 32768

This means all tests will create a testfile on the "O:\" disk named testfile.dat, use 8 threads designated by the "8" and it will be sized 32GB. The value at the end is in MB.

Then I created a script in the same directory called "santest.bat"

I added the following entries:

sqlio -kW -t8 -s300 -dO -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b16 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b32 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b64 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b128 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b256 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b512 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b1024 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b2048 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b4096 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -frandom -b8192 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b16 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b32 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b64 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b128 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b256 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b512 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b1024 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b2048 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b4096 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -frandom -b8192 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b8 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b16 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b32 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b128 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b256 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b512 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b1024 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b2048 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b4096 -BH -LS -Fparam.txt
sqlio -kW -t8 -s300 -dO -o8 -fsequential -b8192 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b8 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b16 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b32 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b64 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b128 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b256 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b512 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b1024 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b2048 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b4096 -BH -LS -Fparam.txt
sqlio -kR -t8 -s300 -dO -o8 -fsequential -b8192 -BH -LS -Fparam.txt
---------------------------------------------------------------------------------------------------
-kW denotes WRITE operation as kR denotes READ operation
-t8  denotes 8 threads (it will always take the parameter in your param.txt and ignore this setting)
-s300 denotes the length to test this specific test (in this case 300 seconds = 5 minutes)
-dO designates the O:\ disk as the target of the test
-o8  means 8 outstanding requests
-frandom means random access, -fsequential means sequential access
-bx designates the IO size to be tested
-BH means that if you have disk cache, to use it. (-BN will disable use of the cache and force read-write directly to disk instead of cache)

I used IO sizes from 8kb to 8192kb in size to test as many sizes as possible in the case above.

Then, open a command-line DOS prompt and navigate to the SQLIO installation directory.

Run the batch file with logfile name as in:

santest.bat > results.txt

This will take some time to finish, and in the example script above, this will be just a bit over 3.5 hours by adding the -s300 entries together.

Now that you have your disk benchmark results,  it is time to set your machine to be able to run unsigned powershell scripts. Using an elevated Powershell session, run:

Set-ExecutionPolicy Unrestricted

Then accept (Y) the warning


Finally, use the powershell script from this blog post.
I would save your result text file on your desktop with the powershell script.
When it asks for the input filename, enter it and away it goes magically opening Excel and importing the result data from your text file. Very cool stuff.