Disk Benchmarking with SQLIO

Bench-marking disk IO in both MB/s and IOPS has always been very important and has only become more critical with the onset of Cloud infrastructure providers. Not only do you want to know that the storage subsystem will perform as expected, but you also want to make sure you are getting what you paid for.

First and foremost, the results of any IO benchmarks are next to useless if you don’t know what block size your numbers were achieved at. Are you bench-marking at over 600MB/s!? WOW, THAT’S GREAT! Then why is your SQL server only achieving 8MB/s?

You may already know that that SQL Server does the majority of its read and write operations in 8K chunks and that you'll generally get your best performance if all of your databases are sitting on logical disks which have been formatted with 64k allocation units (or block sizes) so that SQL Server can blast a single stream of 8 chunks (8 x 8k = 64k) to your disk at a time.

So, to truly know what you've got under the hood, you'll need to run your storage subsystems through a battery of tests using various block sizes but also very importantly, you’ll want to ensure that you are getting adequate IO rates when processing 8k and 64k chunks.

This is where Microsoft's SQLIO comes into play. The utility is great at swamping an IO subsystem but (1) it reports its findings to the command console and (2) it must be run many times with different parameters to get a full picture of your storage performance capabilities…. and that's why you are here.

I've put together an insane little batch file using various bad practices, all in an effort to automate and centralize the metrics delivered from SQLIO.

This batch file will run the tool for a given drive at 4k, 8k, 16k, 32k and 64k with a configured file size and will parse and dump the results into a SQL Server of your choosing. It will even create the database and tables for you.

The drawbacks are that you will need to install the SQLIO tool locally on each server that you want to test and that SQL Server 2005 (or above) must also be installed on the server being bench-marked because the batch file uses the shared sqlcmd.exe utility.

This tool will test various IO patterns and inserts the results into a SQL table.

Microsoft seems to recommend test file sizes of 20GB and test durations of 2+ minutes so that SAN caches will be saturated during the test.

Prerequisites:

SQL Server must be installed on the server being tested because "sqlcmd.exe" is required. The SQLIO.msi tool must be installed because it is used to generate workload.

Steps:

  • Copy the whole directory of test scripts to the local server.
  • Configure the test parameters inside the "RunTest.bat" file.
  • Configure the file size parameters inside the "FileParameters.txt" file.
  • Run the "RunTest.bat" file.
  • Check out the results in the SQL server you specified in the "RunTest.bat" file.
  • Delete the test files created by the tool!
  • Pivot in excel and Enjoy!

The SQLIO tool from Microsoft is provided here because the parser is VERY dependent on the exact output of this version.

Downloads