 |
| Default settings. I changed to "Everyone" so that my collegueages can use the tool. The folder was changed to "C:\SQLAdmin\SQLIO". |
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
- 8 KB random writes to the data files (pattern for checkpoints, tempdb, etc.)
- 8 KB random reads to the data files (pattern for random data reads, singleton seeks, etc.)
- 64 KB sequential writes to the log file (bulk log writes)
- 8 KB random reads to the log file (rollbacks, log reader, etc.)
- 1 KB sequential writes to the log file (small log writes)
- 64 KB sequential writes to the data files (checkpoints, reindex, bulk inserts)
- 64 KB sequential reads to the data files (read-ahead, reindex, checkdb)
- 128 KB sequential reads to the data files (read-ahead, reindex, checkdb)
- 128 KB sequential writes to the data files (bulk inserts, reindex)
- 256 KB sequential reads to the data files (read-ahead, reindex)
- 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) >> $ResultFileNameThe usage of the script is just calling the script:
./SQLIO_Batch.ps1Typically 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.csvThe 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":
This is an example on a script execution:
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).
Posts
