This document describes how to use Microsoft’s SQLIO to test disk/SAN performance. It is biased towards SQL Server – which uses primarily 64KB and 8KB data pages so I am running the tests using those cluster sizes, however, other sizes can be specified.
Download SQLIO from https://www.microsoft.com/en-gb/download/details.aspx?id=20163
SQLIO is a command line tool with no GUI so you need to open a command prompt at C:\Program Files (x86)\SQLIO after you have installed it.
First of all edit param.txt so that you create the test file we will be using. The file needs to be bigger than the combined RAID and on-board disk caches. In this case we are using a 50GB file.
The “2” refers to the number of threads to use when testing, you don’t need to change this now. The “0x0” value indicates that all CPUs should be used, which you probably don’t want to change either, “#” is a comment. The only part you may want to change is 51200 (50GB) and the drive letter.
After saving param.txt, run the command below at the command line in the same directory where SQLIO is installed in order to create the test file, this will save time later when you run the tests proper. Depending on the speed of the hardware and size of the test file this could take 5 – 20 minutes to complete:
sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt
The parameters for the command you will be running are:
-kW means writes, use this or:
-kR means reads
-t2 means two threads (increase as required. You can have more threads than CPUs. Max 256)
-s120 means test for 120 seconds
-dE means drive letter E
-o1 means one outstanding request (increase as required)
-frandom means random access, use this or:
-fsequential means sequential access
-b64 means 64kb data clusters to process (useful for testing for SQL Server), use this or:
-b8 means 8kb data clusters to process (useful for testing for SQL Server)
-BH means use the disk cache (hardware buffering). We would normally use this.
-BN (default) means use file system cache (probably don’t want to use this value to get a realistic result)
-LS means show disk latency from the system (use this). Especially on SMP machines
-LP means show from the process (probably don’t want to use this)
/T means timeout in seconds before executing the next command, if one follows.
To give you an idea of how long all the tests will take run, copy the following into a batch file called TestSAN.bat. It should run for 8 minutes.
Execute it via a command prompt at C:\Program Files (x86)\SQLIO
TestSAN.bat > SANTestResults.txt
Reading and analysing the results
Contents of SANTestResults.txt
C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt
using system counter for latency timings, 3579545 counts per second
parameter file used: param.txt
file e:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file e:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 51200 MB for file: e:\testfile.dat
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 19 62 14 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
MBs/sec: Needs to be as high as possible
IOs/sec: As high as possible
Min_Latency(ms):As low as possible
Avg_Latency(ms): As low as possible
Max_Latency(ms): As low as possible
Reads should have higher throughput (MBs/sec) and IOPS (IOs/sec) than writes. Writes should have high latency rates than reads. Larger cluster sizes should have higher latency but maybe higher throughput.
The first row of the histogram (ms) provides groupings for 0ms through 23ms. The 24+ group includes operations that took 24ms and more. The second row in the histogram (%) provides a percentage of I/Os that completed in that time. For example, in the 4ms group 2% of the total I/Os completed running.
Look for where a drive plateaus for random reads, how many outstanding requests it takes to reach maximum capacity for random writes, or where sequential I/Os plateau compared to random ones.
The full test batch file
The batch file below will create a lot of logs. It may be more convenient to import them into a database and perform some aggregate queries on the results for easier analysis.