SQLIO Batch

SQLIO is a tool that must be installed on the computer where you want to test the storage. The installation is rather simple. I did a few changes away from the default installation.

Default settings. I changed to "Everyone" so that my collegueages can use the tool. The folder was changed to "C:\SQLAdmin\SQLIO".

The tool is 32-bit no matter what Windows build you install it on.
If you want to keep the registry clean, you can install SQLIO on a workstation using the MSI file and copy "sqlio.exe" to the server.

Syntax

Sqlio.exe –k{R|W} –t<threads> -s<seconds> -d<drive(-s)> -R<drive(-s)> -f<stripe> -p[I]<affinity> -a[R[I]]<mask> -o -b<size> -i<ios> -m[{C|S}]<blocks> -L[{S|P}][i]<latency> -B[{N|Y|H|S}] –S<blocks> -v1.1.1 –F<parmfile>

Default

sqlio -kR -t1 -s30 -f64 -b2 -i64 -BN testfile.dat

Example

sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat

Parameters

• -k{R|W}: Kind of IO (R=reads, W=writes). Do Write before Read, so that there is something to read.
• -t<threads>: Number of threads. Maximum is 256.
• -s<seconds>: Number of seconds to run.
• -d<drive(-s)>: Use same filename on each drive letter given. Multiple drives are possible. Write driveletters without space delimiter. Maximum is 256.
• -R<drive(-s)>: Raw drive letters/number for I/O. Multiple drives are possible. Maximum is 256.
• -f<stripe>: Stripe size in blocks, random, or sequential.
• -p[I]<affinity>: CPU number for affinity. 0 (zero) based (I=ideal).
• -a[R[I]]<mask>: CPU mask for (R=roundrobin (I=ideal)) affinity.
• -o<outstanding>: Depth to use for completion routines. Requests are not piling up.
• -b<size>: I/O block size in KiB.
• -i<IOs>: Number of I/Os per I/O run.
• -m[{C|S}]<blocks>: Do multi block IO (C=copy, S=scatter/gather).
• -L[{S|P}][i]<latency>: Latencies from (S=system, P=processor) timer.
• -B[{N|Y|H|S}]: Set buffering (N=none, Y=all, H=hardware, S=software).
• -S<blocks>: Start I/Os number of blocks into file.
• -v1.1.1: I/Os runs use same blocks, as in version 1.1.1. sqlio.exe is version 1.5.SG. Check version with the parameter “-?”.
• -F<parmfile>: Read parameters from <parmfile>. The length of the filename has a maximum of 256.

Scenario

All tests use up to 8 processing threads, run for 10 minutes each, are enabled for multi-IO, and allow up to between 16 and 64 outstanding IOs each.
Files used include 2 data files and 1 log file.

• Aligned or not.
• 4 or 64 KB NTFS cluster size
• VRAID5 or -10

The tests include each of the following:

1. 8 KB random writes to the data files (pattern for checkpoints, tempdb, etc.)
2. 8 KB random reads to the data files (pattern for random data reads, singleton seeks, etc.)
3. 64 KB sequential writes to the log file (bulk log writes)
4. 8 KB random reads to the log file (rollbacks, log reader, etc.)
5. 1 KB sequential writes to the log file (small log writes)
6. 64 KB sequential writes to the data files (checkpoints, reindex, bulk inserts)
7. 64 KB sequential reads to the data files (read-ahead, reindex, checkdb)
8. 128 KB sequential reads to the data files (read-ahead, reindex, checkdb)
9. 128 KB sequential writes to the data files (bulk inserts, reindex)
10. 256 KB sequential reads to the data files (read-ahead, reindex)
11. 1 MB sequential reads to the data files (backups)

Batch

This PowerShell script executes SQLIO with various parameter values
#requires -version 2.0
$Drive = @('G', 'H', 'I', 'J')

$IO_Kind = @('W', 'R')  # Write before read so that there is something to read.
$Threads = @(2, 4, 8)
#$Threads = @(2, 4, 8, 16, 32, 64)
$Seconds = 10*60  # Five minutes
$Factor = @('random', 'sequential')
$Outstanding = @(1, 2, 4, 8, 16, 32, 64, 128)
$BlockSize = @(1, 8, 64, 128, 256, 1024)

Clear-Host

$ResultFileName = "SQLIO_Result.{0:yyyy-MM-dd}.txt" -f $([System.DateTime]::Now)
"{0:s}  Results are written to the file `"$ResultFileName`"." -f $([System.DateTime]::Now)
$InvokeNumber = 0
$InvokeCount = $($IO_Kind.Count * $Threads.Count * $Drive.Count * $Factor.Count * $Outstanding.Count * $BlockSize.Count)

"{0:s}  Batch executed by the user `"$([Security.Principal.WindowsIdentity]::GetCurrent().Name)`" on the computer `"$($env:COMPUTERNAME)(.$($env:USERDNSDOMAIN))`"." -f $([System.DateTime]::Now) > $ResultFileName

foreach ($_blocksize in $BlockSize) {
foreach ($_outstanding in $Outstanding) {
foreach ($_factor in $Factor) {
foreach ($_drive in $Drive) {
foreach ($_threads in $Threads) {
$IO_Kind | ForEach-Object {
$InvokeNumber++
"`n{0:s}  Step $InvokeNumber of $InvokeCount." -f $([System.DateTime]::Now) >> $ResultFileName
$SqlioCmd = "-k$_ -t$_threads -s$Seconds -d$_drive -f$_factor -o$_outstanding -b$_blocksize -LS -BH sqlio.test.dat"
Write-Progress -Activity 'Running SQLIO (sqlio.exe).' -Status "Step $InvokeNumber of $($InvokeCount): $SqlioCmd" -Id 1 -PercentComplete ($InvokeNumber/$InvokeCount * 100)
"{0:s}  SQLIO parameters: $SqlioCmd" -f $([System.DateTime]::Now)  >> $ResultFileName
Invoke-Expression -Command ".\sqlio.exe $SqlioCmd" >> $ResultFileName
if ($InvokeNumber -ne $InvokeCount) {
"{0:s}  Step finshed. Batch paused 60 seconds to allow I/O idle state." -f $([System.DateTime]::Now) >> $ResultFileName
Start-Sleep -Seconds 60
}
}
}
}
}
}
}
"`n{0:s}  SQLIO batch finshed." -f $([System.DateTime]::Now)  >> $ResultFileName
The usage of the script is just calling the script:
./SQLIO_Batch.ps1
Typically a batch run will take about three days and will generate a 1.1 MiB result file.

Parse

This PowerShell script parse a result file from the script above "SQLIO_Batch.ps1". The parse script is based on Perl script from Linchi Shea "Parse the sqlio.exe Output".
#requires -version 2.0
param ([string]$ResultFileName = $(throw 'The parameter "ResultFileName" requires a file name.'))

$Sqlio = @()

ForEach($line in Get-Content $ResultFileName) {

$match = $line -match "-k(?<kind>\w) -t(?<threads>\d+) -s(?<seconds>\d+) -d(?<drive>\w*) -f(?<stripe>\w*) -o(?<outstanding>\d+) -b(?<size>\d+)"

if ($match -eq $true) {

$Kind = $Matches['kind']

$Threads = [int]$Matches['threads']

$Seconds = [int]$Matches['seconds']

$Drive = $Matches['drive']

$Stripe = $Matches['stripe']

$Outstanding = [int]$Matches['outstanding']

$Size = [int]$Matches['size']

}

$match = $line -match "^IOs/sec: *(?<ios_per_sec>\d+.\d+)"

if ($match -eq $true) { $IOs = [float]$Matches['ios_per_sec'] }

$match = $line -match "^MBs/sec: *(?<mbs_per_sec>\d+.\d+)"

if ($match -eq $true) { $MBs = [float]$Matches['mbs_per_sec'] }

$match = $line -match "^Min_Latency\(ms\): *(?<min_latency>\d+)"

if ($match -eq $true) { $Latency_min = [int]$Matches['min_latency'] }

$match = $line -match "^Avg_Latency\(ms\): *(?<avg_latency>\d+)"

if ($match -eq $true) { $Latency_avg = [int]$Matches['avg_latency'] }

$match = $line -match "^Max_Latency\(ms\): *(?<max_latency>\d+)"

if ($match -eq $true) { 

$Sqlio += New-Object PSObject -Property @{

Kind = $Kind

Threads = $Threads

Seconds = $Seconds

Drive = $Drive

Stripe = $Stripe

Outstanding = $Outstanding

Size = $Size

IOs = $IOs

MBs = $MBs

Latency_min = $Latency_min

Latency_avg = $Latency_avg

Latency_max = [int]$Matches['max_latency']

}

}

}

#$Sqlio | Format-Table -Property IOs,MBs,Latency_min,Latency_avg,Latency_max -AutoSize

$Sqlio | Format-Table -Property Kind,Threads,Seconds,Drive,Stripe,Outstanding,Size,IOs,MBs,Latency_min,Latency_avg,Latency_max -AutoSize

#$Sqlio | Export-Csv SQLIO_Parse.csv

The script can in the end be altered to give a filtered output in a table or output in a CSV file. A CSV file can be imported in a spreadsheet for further analysis.
Usage of the script requires the name of a result file in the parameter "ResultFileName":
./SQLIO-Parse.ps1 -ResultFileName <file name>
This is an example on a script execution:
./SQLIO-Parse.ps1 -ResultFileName 'SQLIO_Result.2010-12-24.txt'

Discussion

The amount of data is so large, so a deeper analysis could be done using SQL Server Analysis Services.
The results has in my case been rather usefull to get a nice talk with the storage guys about storage and its usage and configuration.

History

2011-04-24 : Entry postet.
2013-09-10 : Parse script fixed on threads as described in comment by Manuel (2013-02-27).

2015-02-03 : Solution discussed by David Klee in the blog post "Storage Benchmarking with SQLIO Batch".

2017-08-22 : Solution mentioned in session on PASS Summit 2014 by David Klee about "Right-Sizing Your SQL Server Virtual Machine" (46:50).