SQLServerWiki

“The Only Thing That Is Constant Is Change”

Disk Benchmark testing using SQLIO.

Posted by database-wiki on April 28, 2016

SQL Server I/O characteristics.

OPERATION RANDOM/SEQUENTIAL READ/WRITE SIZE RANGE
Create Database Sequential Write 512KB
Backup Database Sequential Read/Write Multiples of 64KB
Restore Database Sequential Read/Write Multiples of 64KB
DBCC – CHECKDB Sequential Read 8KB-64KB
Alter Index – on -rebuild (Read Phase) Sequential Read 64KB-512KB
Alter Index – on -rebuild (Write Phase) Random or Sequential Write 8KB upto 128KB
Sys.dm_db_index_physical_stats Sequential Read 8KB-64KB
Insert / Update / Delete Random or Sequential Write 64KB-512KB
Select Random or Sequential Read 64KB-512KB
TempDB Random or Sequential Read/Write 8KB-64KB
Transaction Log Sequential Write 60KB

Summary of SQL Server I/O Patterns that Determine SQLIO parameters.

  1. SQL Server only uses one worker thread per logical CPU at a time and this is managed by SOS Scheduler inside SQL Server. So SQLIO Param.txt file should be configured such that we use the number of logic CPUs  as number of threads.
  2. SQL Server performs data writes periodically, when Checkpoint runs (or under memory pressure when Lazy Writer kicks in). Data modifications are done in memory and those changes flushed to disk by a single system thread that executes Checkpoint (or Lazy Writer). Therefore it makes no sense to configure more than 1 thread in Params.txt when testing writes. In addition, SQL Server writes up to 32 pages – 256 KB – at a time. Therefore, it makes sense to use 256 KB as write size. One exception is Eager Writes, which are performed by minimally logged operations. Those would be 8 KB (or perhaps 64 KB in size).
  3. SQL Server performs data reads continuously. Most of the reads are done in 8 KB pages (singe page reads). Reads-ahead reads are done in 64-page (512 KB) chunks. Reading full extents – 64 KB in size- are also somewhat common. Therefore these three sizes are the only read sizes.
  4. Transaction Log writes vary in size depending on workload (transaction performed) but typically do not exceed 60 KB. Therefore, choosing a size to test for log writes is difficult, but as a test, I better we choose 8 KB (which will cover both eager writes and log writes).
  5. SQL Server is designed to maximize sequential I/O as much as possible (both reads or writes). Random and Sequential I/O play a smaller role in today’s SAN systems because of large storage caches, optimized read/write mechanisms, multiple spindles, etc.

Based on these patterns, below are the list of commands that I have used.

Random Read Tests

Similar to single-page reads (8 KB) in SQL. Use as many threads as logical CPUs in the Param.txt. 

In our case 48 CPU’s and we are using 90 GB files for testing: 

O:\SQLIOMP\Disk1\testfile.dat 48 0x0 92160

sqlio -kR -s7200 -frandom -o8 -b8 -LS -FparamMT.txt > Reads8KRandom8Oustanding.txt

Similar to extent reads I/O 64KB; use as many threads as CPUs in the Param.txt

sqlio -kR -s7200 -frandom -o8 -b64 -LS -FparamMT.txt > Reads64KRandom8Oustanding.txt

Similar to Read-Ahead in SQL; use as many threads as CPUs

sqlio -kR -s7200 -frandom -o8 -b512 -LS -FparamMT.txt > Reads512KRandom8Oustanding.txt

Random Write tests

8 KB Writes – similar to single-page writes in SQL, which are rare, or potentially similar to Log Writes though log write sizes vary . Also Eager Writes may be similar; use as many threads as CPUs in the Param.txt

sqlio -kW -s7200 -frandom -o8 -b8 -LS -FparamMT.txt > Writes8KRandom8Outstanding.txt

256 KB Writes similar to Checkpoint in SQL with a realistic outstanding I/O count 100. This will really push the I/O subsystem, but is realistic and can happen. Use 1 (max 2 threads) in Param.txt – similar to checkpoint.

In our case 48 CPUs and we are using 90 GB files for testing: :

O:\SQLIOMP\Disk1\testfile.dat 1 0x0 92160

sqlio -kW -s7200 -frandom -o100 -b256 -LS -FparamST.txt > Writes256KRandom100Outstanding.txt

256 KB Writes similar to Checkpoint in SQL with a possible realistic outstanding I/O count 200. This will really, really push the I/O subsystem, but could happen. Use 1 thread in Param.txt – similar to checkpoint

sqlio -kW -s7200 -frandom -o200 -b256 -LS -FparamST.txt > Writes256KRandom200Outstanding.txt

Sequential Read Tests

sqlio -kR -s7200 -fsequential -o8 -b8 -LS -FparamMT.txt > Reads8Ksequential8Oustanding.txt

sqlio -kR -s7200 -fsequential -o8 -b64 -LS -FparamMT.txt > Reads64Ksequential8Oustanding.txt

sqlio -kR -s7200 -fsequential -o8 -b512 -LS -FparamMT.txt > Reads512Ksequential8Oustanding.txt

Sequential Write tests

sqlio -kW -s7200 -fsequential -o8 -b8 -LS -FparamMT.txt > Writes8Ksequential8Outstanding.txt

sqlio -kW -s7200 -fsequential -o100 -b256 -LS -FparamST.txt > Writes256Ksequential100Outstanding.txt

sqlio -kW -s7200 -fsequential -o200 -b256 -LS -FparamST.txt > Writes256Ksequential200Outstanding.txt

SQLIO parameter reference:

  • -kW and -kR: means we’re testing writes or reads
  • -t8 and -o8: means 8 threads with up to 8 outstanding requests at once.  SQLIO isn’t CPU-bound at all, and you can use more threads than you have processors.  The more load we throw at storage, the faster it goes – to a point.
  • -s7200: means the test will last 120 minutes.(2 hrs)
  • -b8 , -b64, -b512: the size of our IO requests in kilobytes.  SQL Server does a lot of random stuff in 8KB chunks, and we’re also testing sequential stuff in 64KB chunks.
  • -frandom and -fsequential: random versus sequential access.  Many queries jump around randomly in the database, whereas things like backups, bulk loads, and table scans generally work sequentially.

A sample of SQLIO is shown below.

8KB random reads using 90 GB file with 8 outstanding I/O request:

sqlio v1.5.SG
using system counter for latency timings, 2539062 counts per second
parameter file used: paramMT.txt
file O:\SQLIOMP\Disk1\testfile.dat with 48 threads (0-47) using mask 0x0 (0)
48 threads reading for 7200 secs from file O:\SQLIOMP\Disk1\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file O:\SQLIOMP\Disk1\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding O:\SQLIOMP\Disk1\testfile.dat … done.
using specified size: 92160 MB for file: O:\SQLIOMP\Disk1\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 15504.09
MBs/sec: 121.12
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 24
Max_Latency(ms): 4887
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+
%: 13 21 7 4 3 3 2 2 2 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 32

sqlio v1.5.SG
using system counter for latency timings, 2539062 counts per second
parameter file used: paramMT.txt
file P:\SQLIOMP\SQLIO_01\testfile.dat with 48 threads (0-47) using mask 0x0 (0)
48 threads reading for 7200 secs from file P:\SQLIOMP\SQLIO_01\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file P:\SQLIOMP\SQLIO_01\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding P:\SQLIOMP\SQLIO_01\testfile.dat … done.
using specified size: 92160 MB for file: P:\SQLIOMP\SQLIO_01\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18325.52
MBs/sec: 143.16
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 20
Max_Latency(ms): 58819
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+
%: 3 3 3 3 5 6 7 19 13 6 4 2 3 2 1 1 1 1 1 1 1 1 1 1 13

sqlio v1.5.SG
using system counter for latency timings, 1948244 counts per second
parameter file used: paramMT.txt
file H:\SQLIOMP\SQLIO_01\testfile.dat with 40 threads (0-39) using mask 0x0 (0)
40 threads reading for 7200 secs from file H:\SQLIOMP\SQLIO_01\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file H:\SQLIOMP\SQLIO_01\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding H:\SQLIOMP\SQLIO_01\testfile.dat … done.
using specified size: 92160 MB for file: H:\SQLIOMP\SQLIO_01\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 191786.53
MBs/sec: 1498.33
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 113060
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+
%: 60 12 4 6 12 4 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Converting the output of SQLIO to a more readable output using SQLIOResult2Excel.ps1

Code for SQLIOResult2Excel.ps1 (From the internet)

<–Begin

## @The MTC Blog

# SQLIO Result Parser by Chai Swaddipong
# Feel free to pass along/improve

# 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”

End–>

Command to run:

PS C:\Users\bmani\Desktop\SQLIO\SQLIO-HUS150-64GB-Disk1\SQLIO> .\SQLIOResult2Excel.ps1 C:\Users\bmani\Desktop\SQLIO\SQLO-HUS150-64GB-Disk4\SQLIO\Reads8KRandom8Oustanding.txt

8KB random reads using 90 GB file with 8 outstanding I/O request:

Threadcount Outstanding IO Testfile Path Datafile size Duration IO Pattern Bandwidth(MB/s) IOPs Average latency(ms)
48 8 O:\SQLIOMP\Disk4\testfile.dat 92160 MB 7200 8KBrandomread-HUS150 48.64 6226.01 61
48 8 P:\SQLIOMP\SQLIO_04\testfile.dat 92160 MB 7200 8KBrandomread-Nimble 455.38 58288.99 6
40 8 H:\SQLIOMP\SQLIO_04\testfile.dat 92160 MB 7200 8KBrandomread-Pure 1473.68 188631.44 1

PS1

Hope this help!

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: