SQL Server disk IO benchmark using SQLIO utility

SQLIO is not a part of SQL Server package despite its name, it is a tool provided by Microsoft which can be used to determine the I/O capacity or benchmark a given disk subsystem. Benefit of SQLIO is that, it allows us to identify hardware or I/O configuration issues before we deploy or install SQL Server by measuring I/O against one or more test files to measure I/Os per second (IOPs), throughput (MB/s), and latency. SQLIO does this by reading and writing files of different sizes. We can increase the file size to simulate the projected size of your database. We determine the saturation point of an I/O subsystem by gradually increasing the load. The saturation point is the limit of your I/O subsystem’s capacity.

  1. Download SQLIO
  2. Install the SQLIO.exe
    Default SQLIO Installation path would be
    “C:\Program Files (x86)\SQLIO\” or “C:\Program Files\SQLIO\”
    EULA.rtf – End-user license agreement that covers sqlio usage.
    param.txt – Configuration options that identify the target drives, test file names, and specifics about running the tests.
    readme.txt – Comprehensive details about the utility and its supported options.
    sqlio.exe – The utility’s executable file.
    Using SQLIO.rtf – General information about using the utility.
  3. My Computer → Properties → Advanced System Settings → Advanced → Performance → Settings → Data Execution Prevention → Add sqlio.exesqlio-advance-settings
  4. We are going to benchmark or do IO test on drive G: (a jump drive or removable drive)
    Open param.txt present in the SQLIO installation directory. Replace “c:\testfile.dat 2 0x0 100” with “g:\testfile.dat 2 0x0 100” . This is where the drive needs to be defined and other line with # is a commented line. That means we can define more than one drive at a time.

    Parameter Description Values
    file name This is the target drive; don’t worry about the file name. TestFile.dat will be created with during SQLIO execution and read write will be measured as per that. Make sure you delete this file manually after SQLIO execution) g:\TestFile.dat
    number of threads Size of the thread pool. This will be overridden by the command line later. 2
    mask Affinity mask to bind operations against this file to particular CPU’s. I don’t use this and just use 0x0 for all CPU’s 0x0
    file size The size in MB. Default file size is 100MB which is very small in real time. This should be roughly the size of your database and always larger than your cache, unless you want to test just the cache. 100
  5.  Create a batch file in the SQLIO directory named TESTG.bat or with any name you want. Paste the below the below line of commands.@echo off
    sqlio -kW -t1 –s60 -o4 -frandom -b8 -BH -LS -dG TestFile.dat
    sqlio -kR -t2 –s60 -o4 -frandom -b8 -BH -LS -dG TestFile.dat
    sqlio -kW -t1 –s60 -o4 -fsequential -b64 -BH -LS -dG TestFile.dat
    sqlio -kR -t2 –s60 -o4 -fsequential -b64 -BH -LS -dG TestFile.dat
    exit

    Parameter Description
    -k<R|W> Kind of IO (R=reads, W=writes)
    -t<threads> Number of threads
    -s<secs> Number of seconds to run
    -d<drive1>..<driveN> Use same filename on each drive letter given
    -R<drive1>,,<driveN> Raw drive letters/number on which to run
    -f<stripe factor> Stripe size in blocks, random, or sequential
    -p[I]<cpu affinity> CPU number for affinity (0 based)(I=ideal)
    -a[R[I]]<cpu mask> CPU mask for (R=roundrobin (I=ideal)) affinity
    -o<#outstanding> Depth to use for completion routines
    -b<io size(KB)> IO block size in KB
    -i<#IOs/run> number of IOs per IO run
    -m<[C|S]><#sub-blks> Do multi blk IO (C=copy, S=scatter/gather)
    -L<[S|P][i|]> Latencies from (S=system, P=processor) timer
    -U[p] Report system (p=per processor) utilization
    -B<[N|Y|H|S]> Set buffering (N=none, Y=all, H=hdwr, S=sfwr)
    -S<#blocks> Start I/Os #blocks into file
    -v1.1.1 I/Os runs use same blocks, as in version 1.1.1
    -64 Use 64 bit memory operations
    -F<paramfile> Read parameters from <paramfile>
  6. Now we are ready to go.. Open cmd from the SQLIO directory as an administrator.
    Type: TESTG.bat > output.log
    Wait until cmd does not exit. Then open output.txt file once the cmd gets closed.
    sqlio-disk-io-benchmarkoutput.txt
    sqlio v1.5.SG
    using system counter for latency timings, 2533388 counts per second
    1 thread writing for 60 secs to file G:TestFile.dat
    using 8KB random IOs
    enabling multiple I/Os per thread with 4 outstanding
    buffering set to use hardware disk cache (but not file cache)
    using current size: 0 MB for file: G:TestFile.dat
    initialization done
    CUMULATIVE DATA:
    throughput metrics:
    IOs/sec: 0.04
    MBs/sec: 0.00
    latency metrics:
    Min_Latency(ms): 126
    Avg_Latency(ms): 28760
    Max_Latency(ms): 85729
    histogram:
    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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100
    sqlio v1.5.SG
    using system counter for latency timings, 2533388 counts per second
    2 threads reading for 60 secs from file G:TestFile.dat
    using 8KB random IOs
    enabling multiple I/Os per thread with 4 outstanding
    buffering set to use hardware disk cache (but not file cache)
    using current size: 1105 MB for file: G:TestFile.dat
    initialization done
    CUMULATIVE DATA:
    throughput metrics:
    IOs/sec: 1358.54
    MBs/sec: 10.61
    latency metrics:
    Min_Latency(ms): 0
    Avg_Latency(ms): 5
    Max_Latency(ms): 66
    histogram:
    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 0 0 0 7 75 14 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    sqlio v1.5.SG
    using system counter for latency timings, 2533388 counts per second
    1 thread writing for 60 secs to file G:TestFile.dat
    using 64KB sequential IOs
    enabling multiple I/Os per thread with 4 outstanding
    buffering set to use hardware disk cache (but not file cache)
    using current size: 1105 MB for file: G:TestFile.dat
    initialization done
    CUMULATIVE DATA:
    throughput metrics:
    IOs/sec: 205.23
    MBs/sec: 12.82
    latency metrics:
    Min_Latency(ms): 4
    Avg_Latency(ms): 18
    Max_Latency(ms): 70
    histogram:
    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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 51 37 0 0 0 3 5
    sqlio v1.5.SG
    using system counter for latency timings, 2533388 counts per second
    2 threads reading for 60 secs from file G:TestFile.dat
    using 64KB sequential IOs
    enabling multiple I/Os per thread with 4 outstanding
    buffering set to use hardware disk cache (but not file cache)
    using current size: 1105 MB for file: G:TestFile.dat
    initialization done
    CUMULATIVE DATA:
    throughput metrics:
    IOs/sec: 603.64
    MBs/sec: 37.72
    latency metrics:
    Min_Latency(ms): 2
    Avg_Latency(ms): 12
    Max_Latency(ms): 97
    histogram:
    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 0 0 0 0 0 0 0 0 0 0 29 55 5 3 2 1 1 1 0 0 0 0 0 3
Command Data Chunk (-b) No of Threads (-t1) IOs/sec MBs/sec Min Latency Avg Latency Max Latency
sqlio -kW -t1 -s60 -o4 -frandom -b8 -BH -LS -dG TestFile.dat 8KB 1 0.04 0 126 28760 85729
sqlio -kR -t2 -s60 -o4 -frandom -b8 -BH -LS -dG TestFile.dat 8KB 2 1358.54 10.61 0 5 66
sqlio -kW -t1 -s60 -o4 -fsequential -b64 -BH -LS -dG TestFile.dat 64KB 1 205.23 12.82 4 18 70
sqlio -kR -t2 -s60 -o4 -fsequential -b64 -BH -LS -dG TestFile.dat 64KB 2 603.64 37.72 2 12 97

histogram:
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 0 0 0 0 0 0 0 0 0 0 29 55 5 3 2 1 1 1 0 0 0 0 0 3

The first row (ms) of the histogram gives buckets between 0 and 23 milliseconds and for 24 or more milliseconds. The second row (%) gives the percentage of the I/Os that completed at that column’s millisecond latency value. The sum of the percentages may not add up to 100 due to rounding. And, as in this example, 0% may not indicate 0 I/Os that completed with that latency value (thus there was one (or more) 2 ms I/O since this is the minimum latency, but there were less than 0.5% I/Os that completed within 1 and 2 ms.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: