Working with SQLIO and analyzing it’s output


While working on a customer request, I faced the question what is the SQLIO? how to operate it and how to analyze its outputs?

The customer has read about the tool and was also able to run it but wanted more information on how to do it and how to easily analyze the SQLIO output.

What is SQLIO

SQLIOis a Disk Subsystem Benchmark Tool, provided by Microsoft, used to determine the I/O capacity of a given configuration. The SQLIO is a tool that coms “as is” no support is given.

SQLIO is been use to determent the best IO configuration for a certain load.

Working with SQLIO

Before you start working with the tool there are some preparations that need to be done.

– Parameter file
The Parameter file contains the info of the server and the path of the test file:

## File Path = the test file location. In this file we will have all the test requests
## 2 = Number of test file has number of CPU – when testing multiple
Path reduce the file number or split the tests.
## 0x0 = Affinity mask to bind operations against this file to particular CPU’s.

Use always the 0x0 musk setting.

## 100 = File size in MB recommend to be 2-4 time bigger than the SAN cache.
File stretcher



– Test file
The test file will have the test details that we would like to issue on the server;
the reads are sequential or random, read or write, size of the read or write, output file etc.

Test large random read, outstanding I/O requests 8

callsqlio –kRs60 –frandom –o8 –b64 -LS -Fparam.txt

Test large sequential write, outstanding I/O requests 32

callsqlio –kWs60 –fsequential –o32 –b8 –LS -Fparam.txt







Number of outstanding I/O requests per thread. When attempting to determine
the capacity of a given volume or set of volumes, start with a reasonable number
for this and increase until disk saturation is reached.

-o8 –o32,
-o64 ….


Instructs SQLIO to capture disk latency information.



Specify either R or W (read or write). Both reads and writes should be tested.

-kR, -kW


Duration of test (in seconds). For initial tests, running for 5-10 Min per IO.

-s60, -s120


Random I/O, pay close attention to the number of I/Os per second and latency.
For sequential I/O, test a range of sizes (4, 8, 16, 32, 64, 128,256) and pay
attention to throughput and latency.

-b8, -b32,


Type of I/O to issue. Either ‘random’ or ‘sequential’



Name of the file that will contain a list of the test file(s) used by SQLIO.




The output

One of the downside of the SQLIO is reading the output of the test.
Now after running the SQLIO script we are getting the output and we need to analyze it.
Lots of details and it repeat itself; The output is in text format and look exactly like this:
(Attached is a result demo file)


C:\Windows\system32>”c:\Program Files (x86)\SQLIO\sqlio.exe” -kW -t2 -s120 -o4 -frandom -b64 -BH -LS -FJ:\SQLIO\param_g.txt
sqlio v1.5.SG
using system counter for latency timings, 2081142 counts per second
parameter file used: J:\SQLIO\param_g.txt
file g:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file g:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 4 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 20000 MB for file: g:\testfile.dat
initialization done
CUMULATIVE DATA:throughput metrics:
IOs/sec: 4419.34
MBs/sec: 276.20
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 60
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+
%: 185 12 1 0 0 0 0 0 0 0  0   0   0   0   0   0   0   0   0   0  0   0    0   0   0

We need to read it and analyze it. PowerShell can help us.

When I needed to analyze this file, I looked up and I have found the PowerShell script in one of JonathanKehayias post, I took the basic Power tweak it a bit and added the option of generation the output to an excel power pivot table.


Excel Row Data



Excel Power Pivot table And Table with Graph


SQLIOPivotViewAnd Graph

The PowerShell Script:

Download a SQLIO result demo file.

$Results=$filedata.Split([string[]]"sqlio v1.5.SG",[StringSplitOptions]::RemoveEmptyEntries ) | `
select @{Name="Threads"; Expression={[int]([regex]::Match($_,"(\d+)?\sthreads\s(reading|writing)").Groups[1].Value)}},`
                           @{Name="Operation"; Expression={switch ([regex]::Match($_,"(\d+)?\sthreads\s(reading|writing)").Groups[2].Value){"reading" {"Read"}"writing" {"Write"}}}},`
                           @{Name="Duration"; Expression={[int]([regex]::Match($_,"for\s(\d+)?\ssecs").Groups[1].Value)}},`
                           @{Name="IOSize"; Expression={[int]([regex]::Match($_,"\tusing\s(\d+)?KB\s(sequential|random)").Groups[1].Value)}},`
                           @{Name="IOType"; Expression={switch ([regex]::Match($_,"\tusing\s(\d+)?KB\s(sequential|random)").Groups[2].Value){"random" {"Random"}"sequential" {"Sequential"}}}},`
                           @{Name="PendingIO"; Expression={[int]([regex]::Match($_,"with\s(\d+)?\soutstanding").Groups[1].Value)}},`
                           @{Name="FileSize"; Expression={[int]([regex]::Match($_,"\s(\d+)?\sMB\sfor\sfile").Groups[1].Value)}},`
@{Name="IOPS"; Expression={[decimal]([regex]::Match($_,"IOs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
@{Name="MBs_Sec"; Expression={[decimal]([regex]::Match($_,"MBs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
                           @{Name="MinLat_ms"; Expression={[int]([regex]::Match($_,"Min.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                           @{Name="AvgLat_ms"; Expression={[int]([regex]::Match($_,"Avg.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                           @{Name="MaxLat_ms"; Expression={[int]([regex]::Match($_,"Max.{0,}?\:\s(\d+)?").Groups[1].Value)}}`

####--------------  Setting the culture and UI culture incase not using en-US. -- See for more details

####--------------  Creatingth EXCEL Data Workbook and Sheet
$WorkBook.WorkSheets.Item(1).Name ="RawData"
$WorkBook.WorkSheets.Item(2).Name ="Pivot"

####--------------  Now working on the Sheet rowdata and populating it
$WorkSheet.Cells.Item(1,1) ="Threads"
$WorkSheet.Cells.Item(1,2) ="Operation"
$WorkSheet.Cells.Item(1,3) ="Duration"
$WorkSheet.Cells.Item(1,4) ="IOSize"
$WorkSheet.Cells.Item(1,5) ="IOType"
$WorkSheet.Cells.Item(1,6) ="PendingIO"
$WorkSheet.Cells.Item(1,7) ="FileSize"
$WorkSheet.Cells.Item(1,8) ="IOPS"
$WorkSheet.Cells.Item(1,9) ="MBs/Sec"
$WorkSheet.Cells.Item(1,10) ="Min_Lat(ms)"
$WorkSheet.Cells.Item(1,11) ="Avg_Lat(ms)"
$WorkSheet.Cells.Item(1,12) ="Max_Lat(ms)"
$WorkSheet.Cells.Item(1,13) ="Caption"

$Results|% {
$WorkSheet.Cells.Item($x,1) =$_.Threads
$WorkSheet.Cells.Item($x,2) =$_.Operation
$WorkSheet.Cells.Item($x,3) =$_.Duration
$WorkSheet.Cells.Item($x,4) =$_.IOSize
$WorkSheet.Cells.Item($x,5) =$_.IOType
$WorkSheet.Cells.Item($x,6) =$_.PendingIO
$WorkSheet.Cells.Item($x,7) =$_.FileSize
$WorkSheet.Cells.Item($x,8) =$_.IOPS
$WorkSheet.Cells.Item($x,9) =$_.MBs_Sec
$WorkSheet.Cells.Item($x,10) =$_.MinLat_ms
$WorkSheet.Cells.Item($x,11) =$_.AvgLat_ms
$WorkSheet.Cells.Item($x,12) =$_.MaxLat_ms
$WorkSheet.Cells.Item($x,13) =[string]$_.IOSize+"KB "+[string]$_.IOType+" "+ `
[string]$_.Operation +" "+[string]$_.Threads + `
" Threads "+[string]$_.PendingIO+" pending"

####--------------  Pivoting

$xlDataRange="RawData!R1C1:R{0}C13"-f ($x-1)

####--------------  Save and close Excel

Now go and start running the SQLIO (Do not do that in Prod environment)

Comments (6)

  1. Surfer says:


    thanks for the informations. The Script is cutted, so the script don't works.


  2. Faisal says:


    Firstly, this a very useful blog post in providing a way for parsing out sqlio output into an excel spreadsheet. The code you've provided generates a lot of run time exceptions. Did you test the code out on your system. I would appreciate if you could provide us with a working code, and if that's not possible how we can manually create charts and pivot table using power pivot in excel.


  3. Keith says:

    That's be 'its' output, not 'it's' output

  4. Tony says:

    Script worked for me with minor modifications. I’m in US and Set-Culture doesn’t seem to be available on my system, so I commented that part out. Also, change the first two lines to point to where your files are. Other than that, you just need to insert
    spaces after all of the powershell commendlets, Get-Content, Sort-Object, and New-Object.

  5. RATG says:

    like Tony’s comment it needs a few tweaks and modification.
    below are the lines/section that needs to be modified.

    $filedata=Get-Content $sourceFile|Out-String

    |Sort-Object IOSize,IOType,Operation,Threads


    $Excel=New-Object -ComObject Excel.Application
    $WorkBook.WorkSheets.Item(1).Name ="RawData"
    $WorkBook.WorkSheets.Item(2).Name ="Pivot"

  6. tictactoc says:

    Great post Oren, I ran the script on your demo file and a results ouput I generated on one of my machines. I am very new to this tool, so when looking on the charts what conclusion we can reach? when looking in the 2nd chart, line n 9, for the pending
    IO = 8, should we understand that our subsystem can support 1274 IOPs per second (division of 152933 by 120 seconds). In fact, I am not able to understand how the outcome or the charts can help to draw a conclusion
    Thanks again and keep on writing useful posts for the community