Credits to Linchi Shea, SQLServerPedia/Brent Ozar, and Jonathan Kehayias for their discussions on sqlio.
Update: The test below, against one drive, can run for more than 1 hour. That is a long time. I’ve tested running 2 sqlio batch scripts against 2 locally attached drives, similar to the ones listed below, and found the results to be consistent with running those 2 concurrently.
1. Download and install sqlio;
2. Modify param.txt in the same directory where sqlio is. Create a testing file that is 24 gig, or sufficiently large to beat hardware cache along the way. For example, to benchmark drive m, the entry should look like this:
[sourcecode language=”text”]
m:\testfile.dat 2 0x0 20480
[/sourcecode]
If you use mount point, it should look like this:
[sourcecode language=”text”]
m:\mountPointDirectoryName\testfile.dat 2 0x0 20480
[/sourcecode]
3. Make sure file in step 2 is saved. In DOS command line, under the sqlio installation directory, run the command below. It will create a file for use later:
[sourcecode language=”text”]
sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10
[/sourcecode]
4. Create a batch file with the following line as content, let’s call it mDrive.bat. SQLServerPedia article lets each test run 120 seconds, 2 minutes. In my testing against local disks, I made it 30 seconds. Actually I ran both 120 and 30 and didn’t see much of a difference. So I will use 30 seconds.
Note: to test against Windows mount point, the lines below should be like this:
[sourcecode language=”text”]
sqlio -kW -t2 -s30 -dM -o1 -frandom -b64 -BH -LS \mountPointDirectoryName\Testfile.dat
[/sourcecode]
[sourcecode language=”text”]
sqlio -kW -t2 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s30 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat
[/sourcecode]
5. In DOS command line, under the sqlio installation directory, run the command below. It takes more than 1 hour, but less than 2 hours to finish:
[sourcecode language=”text”]
mDrive.bat > mDriveSqlioResults.txt
[/sourcecode]
6. Run this Python script to parse mDriveSqlioResults.txt into a CSV file;
7. Open the CSV file with Excel, create a new sheet, insert a column chart. Drag the chart to make it decent size. Right click on “Select Data…” or something like that, then go back to the first sheet to pick 2 columns of data for this chart. I found charts with MBPS, IOPS, avgLatencyInMS, and maxLatencyInMS are helpful.