After sifting through SQLIO results for the third customer this year at the MTC, I've decided it might be a good idea to join the PowerShell club and create a tool to automate it. The idea is to redirect the results into a file, parse, tabulate, and create graphs in Excel.


Microsoft provides the free SQLIO binaries with the attempt to stress the drive subsystem. It is easy to use, just a couple of switches and you are ready to go.


For example:

sqlio -kR -t8 -s120 -dM -o16 -fsequential -b64 -BN -LS S:\Testfile.dat


sqlio v1.5.SG

using system counter for latency timings, 1953128 counts per second

parameter file used: c:\sqlio\paramSSD.txt

file S:\testfile.dat with 8 threads (0-7) using mask 0x0 (0)

8 threads reading for 120 secs from file S:\testfile.dat

using 64KB sequential IOs

enabling multiple I/Os per thread with 16 outstanding

buffering set to use hardware disk cache (but not file cache)

using specified size: 550000 MB for file: S:\testfile.dat

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec: 12050.67

MBs/sec:   753.16

latency metrics:

Min_Latency(ms): 2

Avg_Latency(ms): 10

Max_Latency(ms): 32

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  4 86  8  1  0  0  0  0  0  0  0  0  0  0  0  0

 

Typically, you will need run multiple IO patterns and iterations and end up with a thousand lines of results to tinker with. OK, I think I have made my point. Let's get to the real thing here. Here is the SQLIOResult2Excel.ps1 PowerShell script I came up with:

 

## @The MTC Blog

 

# SQLIOResult2Excel.ps1 <Result file from SQLIO iterations>

 

# SQLIO Result Parser by Chai Swaddipong
# Feel free to share with others who need it!

# Parsing was tested with the results from sqlio v1.5.SG; results displayed in Excel2013


# Start Excel application
$xl=New-Object -ComObject "Excel.Application"

#Constant
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

# Create a workbook
$wb=$xl.Workbooks.Add()
$ws = $wb.Worksheets.Item("Sheet1")
   
$cells=$ws.Cells

$cells.item(1,1)="SQLIO Test Results from " + $args[0]
$cells.item(1,1).font.bold="True"
$cells.item(1,1).font.size=18

#Location of the header text
$row=3
$col=1

#insert header text

"Threadcount","Outstanding IO","Testfile Path","Datafile size","Duration","IO Pattern","Bandwidth(MB/s)","IOPs","Average latency(ms)" | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$True
    $col++
}

# Loop through lines of the result file

ForEach ($line in Get-Content $args[0] )
{
 
  # Remove leading spaces
  $line -replace '^\s+', ''
 
  # How many threads? Read or write test?How long the test runs? What is the test file?
  if ( $line -match "^\d+ threads \w*" )
  {    
      $TestDetails = $line.Split(" ")
      $ThreadCount = $TestDetails[0]
    
      if (  $TestDetails[2] -match "reading")
      {
           $ReadOrWrite = $TestDetails[2].replace("reading","read")
      }
      else
      {
          $ReadOrWrite = $TestDetails[2].replace("writing","write")
      }
    
      $TestDuration = $TestDetails[4]
      $TestFilePath =  $TestDetails[8]          
   }
 
   # IO size for the test? Sequential or random IO pattern?
   if ( $line -match " \w* (sequential|random)")
   {    
      $IoToTest = $matches[0].split(" ")
      $ioSize = $IoToTest[1]
      $RandomOrSeq = $IoToTest[2]
   }
 
   # Size of the datafile?
   if ( $line -match "\w+: \d+ \w+ " )
   {
      $DataFileSize = ($matches[0].Split(":"))[1]
      $FieldCount++
   }
   
   # IOs per thread
   if ( $line -match " \d+ outstanding" )
   {    
      $QueueDepth = ($matches[0].Split(" "))[1]
   }
 
   # Bandwidth attained
  if ( $line -match "MBs/sec" )
  {    
     $Bandwidth = ($line.Split(":"))[1]
  }
 
  # Average latency attained
  if ( $line -match "Avg_Latency" )
  {    
      $AverageLatency = ($line.Split(":"))[1]
  }

  # Throughput attained
   if ( $line -match "IOs/sec" )
   { 
      $Throughput = ($line.Split(":"))[1]
   }

  # End of each run of SQLIO. Write out results
  if ($line.startswith("histogram") )
  {
    write-host "Writing to Excel-->" $ThreadCount $QueueDepth  $TestFilePath $DataFileSize $TestDuration $ioSize $RandomOrSeq $ReadOrWrite  $Bandwidth $Throughput $AverageLatency   
    $row++
    $col=1
    $cells.item($row,$col)=$ThreadCount
    $col++
    $cells.item($row,$col)=$QueueDepth
    $col++
    $cells.item($row,$col)=$TestFilePath
    $col++
    $cells.item($row,$col)=$DataFileSize
    $col++
    $cells.item($row,$col)=$TestDuration
    $col++
    $cells.item($row,$col)=$ioSize+$RandomOrSeq+$ReadOrWrite
    $col++
    $cells.item($row,$col)=$Bandwidth
    $col++
    $cells.item($row,$col)=$Throughput
    $col++
    $cells.item($row,$col)=$AverageLatency
  }
   
}

#Sort on Bandwidth
$objRange = $ws.range("A4:I$row")
$objRange2 = $ws.Range("G4")
[void]$objRange.Sort($objRange2)


$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

$start=$ws.range("G3")

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range("G$($Y.item(1).Row):G$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = "Bandwidth"
$ws.shapes.item("Chart 1").top=60
$ws.shapes.item("Chart 1").left=600
$ws.shapes.item("Chart 1").width=600
$ws.shapes.item("Chart 1").height=300

#Sort on IOP
$objRange = $ws.range("A4:I$row")
$objRange2 = $ws.Range("H4")
[void]$objRange.Sort($objRange2)

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlLine

$start=$ws.range("H3")

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range("H$($Y.item(1).Row):H$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = "IOPs"
$ws.shapes.item("Chart 2").top=360
$ws.shapes.item("Chart 2").left=600
$ws.shapes.item("Chart 2").width=600
$ws.shapes.item("Chart 2").height=300

 

#Sort on average latency
$objRange = $ws.range("A4:I$row")
$objRange2 = $ws.Range("I4")
[void]$objRange.Sort($objRange2,2)

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlLine

$start=$ws.range("I3")

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range("I$($Y.item(1).Row):I$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = "Average Latency"
$ws.shapes.item("Chart 3").top=660
$ws.shapes.item("Chart 3").left=600
$ws.shapes.item("Chart 3").width=600
$ws.shapes.item("Chart 3").height=300


#Show time!!
$xl.visible="True"

 

The SQLIOResult2Excel.ps1 needs one argument, the file that contains SQLIO results. In this case it is the c:\results.txt file in the example below.

 

sqlio -kR -t8 -s120 -dM -o16 -fsequential -b64 -BN -LS S:\Testfile.dat >>c:\SSDSQLIO_Result.txt


Then,

 

SQLIOResult2Excel.ps1  c:\SSDSQLIO_Result.txt


Here are the  IOPs, Bandwidth, and Average Latency graphs in Excel from running SQLIOResults2Excel.ps1.

 

IOP.png

Banwidth.pngLatency.png


Enjoy!