Partager via

Working with SQLIO and analyzing it’s output

  • Article

Introduction

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

c:\testfile.dat20x0100 d:\testfile.dat20x0100

 

- 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 -kR-s60 –frandom –o8 –b64 -LS -Fparam.txt

Test large sequential write, outstanding I/O requests 32

callsqlio -kW-s60 –fsequential –o32 –b8 –LS -Fparam.txt

 

 

Option

Description

Parameter

-o

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 ….

-LS

Instructs SQLIO to capture disk latency information.

-LS

-k

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

-kR, -kW

-s

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

-s60, -s120

-b

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, -b64

-f

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

-frandom, -fsequential

-F

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

-Fparam.txt

 

 

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
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+
%: 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

SQLIORowData

 

Excel Power Pivot table And Table with Graph

SQLIOPivotView

SQLIOPivotViewAnd Graph

The PowerShell Script:

Download a SQLIO result demo file .

 $sourceFile="C:\1\results_G.TXT"
$targetFile="C:\1\SLQIO_Pivot.xlsx"
$filedata=Get-Content$sourceFile|Out-String
$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)}}`
|Sort-ObjectIOSize,IOType,Operation,Threads


####--------------  Setting the culture and UI culture incase not using en-US. -- See https://msdn.microsoft.com/en-us/library/office/aa168494 for more details
$currentThread=[System.Threading.Thread]::CurrentThread
$culture=[System.Globalization.CultureInfo]::GetCultureInfo("en-US")
$currentThread.CurrentCulture=$culture
$currentThread.CurrentUICulture=$culture
Set-Culture$culture


####--------------  Creatingth EXCEL Data Workbook and Sheet
$Excel=New-Object-ComObjectExcel.Application
$Excel.Visible=$true
$WorkBook=$Excel.WorkBooks.Add()
$WorkBook.WorkSheets.Item(1).Name ="RawData"
$WorkBook.WorkSheets.Item(2).Name ="Pivot"
$WorkBook.WorkSheets.Item(3).Delete()


####--------------  Now working on the Sheet rowdata and populating it
$WorkSheet=$WorkBook.WorkSheets.Item("RawData")
$x=2
$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"
##$c=$worksheet.Activate(columns).count


$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"
$x++}


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

$xlDatabase=1
$xlPivotTableVersion=3
$xlDataRange="RawData!R1C1:R{0}C13"-f ($x-1)
$PivotTable=$Workbook.PivotCaches().Create($xlDatabase,$xlDataRange,$xlPivotTableVersion)
[void]$PivotTable.CreatePivotTable("Pivot!R1C1")


####--------------  Save and close Excel
$Workbook.SaveAs($targetFile)
$Excel.Quit()
while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){'.'}

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

Comments

  • Anonymous
    October 25, 2013
    Hi, thanks for the informations. The Script is cutted, so the script don't works. greetings

  • Anonymous
    November 19, 2013
    Hi, 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. Regards

  • Anonymous
    November 21, 2013
    That's be 'its' output, not 'it's' output

  • Anonymous
    September 23, 2014
    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.

  • Anonymous
    December 18, 2014
    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

    #$currentThread=[System.Threading.Thread]::CurrentThread
    #$culture=[System.Globalization.CultureInfo]::GetCultureInfo("en-US")
    #$currentThread.CurrentCulture=$culture
    #$currentThread.CurrentUICulture=$culture
    #Set-Culture$culture

    $Excel=New-Object -ComObject Excel.Application
    $Excel.Visible=$true
    $WorkBook=$Excel.WorkBooks.Add()
    $workbook.Worksheets.Add()
    $WorkBook.WorkSheets.Item(1).Name ="RawData"
    $WorkBook.WorkSheets.Item(2).Name ="Pivot"
    #$WorkBook.WorkSheets.Item(3).Delete()

  • Anonymous
    May 04, 2015
    The comment has been removed