Skip to content

Instantly share code, notes, and snippets.

@sirsql
Created August 27, 2020 17:01
Show Gist options
  • Save sirsql/929a822dd7ee6475edb5633822c27e14 to your computer and use it in GitHub Desktop.
Save sirsql/929a822dd7ee6475edb5633822c27e14 to your computer and use it in GitHub Desktop.
When used with "Backup Test Harness.sql" will parse the results into an Excel worksheet for evaluation. Related to blog post https://sirsql.net/2012/12/13/20121212automated-backup-tuning/
<#
.SYNOPSIS
Reads a SQL job log containing the output from backup performance tests and displays the results in excel.
.DESCRIPTION
This script is designed to read the output from a logfile that contains multiple backups which use
different settings of MAXTRANFERSIZE & BUFFERCOUNT. This logfile will have been created by executing the job
created using "Backup Test Harness.sql".
It will read in the log and output the results to excel (you must have this installed). It will also create
two charts which provide a visual representation of the current, optimal and best possible backup performance
for the given database. This information will be broken down by file count, maxtransfersize and buffercount.
The script is based upon one created for parsing SQLIO output, written by Jonathan Kehayis which can be found
at http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx
Any questions or problems with this script please contact me - Nic Cain at nic@sirsql.net
.PARAMETER <paramName>
BackupLogFileName - The path & filename of the backup log
.EXAMPLE
&'Parse Backup Perf Tests.ps1' -BackupLogFileName "C:\Temp\Log.txt"
&'Parse Backup Perf Tests.ps1' "C:\Temp\Log.txt"
#>
param( [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $BackupLogFileName)
IF (Test-Path $BackupLogFileName)
{
#$BackupLogFileName = 'C:\Temp\Backup.log'
#Pull in the log file
TRY
{
$BackupLogFileData = [string]::Join([Environment]::NewLine,(Get-Content $BackupLogFileName));
#Create a split list of the items to query
$BackupTestResults = $BackupLogFileData.Split([String[]]"NEW BACKUP [SQLSTATE 01000]", [StringSplitOptions]::RemoveEmptyEntries ) |
#Grab the relevant values from each of the backups performed
select @{Name="BufferCount"; Expression={[int]([regex]::Match($_, "BufferCount:(\s*\d+)").Groups[1].Value)}},
@{Name="MaxTransferSize"; Expression={[int] ([regex]::Match($_, "MaxTransferSize:(\s*\d+)").Groups[1].Value)}},
@{Name="BackupTimeInSeconds"; Expression={[double] ([regex]::Match($_, "pages in (\s*\d+.\d+)").Groups[1].Value)}},
@{Name="BackupSpeedInMBsec"; Expression={[double] ([regex]::Match($_, "pages in (\s*\d+.\d+) seconds \((\d+.\d+) MB/sec").Groups[2].Value)}},
@{Name="BackupFileCount"; Expression={[int] ([regex]::Match($_, "FILECOUNT=(\d+)").Groups[1].Value)}}
#Create a new excel workbook
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$WorkBook = $Excel.WorkBooks.Add()
$WorkBook.WorkSheets.Item(1).Name = "Backup Data"
$WorkBook.WorkSheets.Item(3).Delete()
$WorkBook.WorkSheets.Item(2).Delete()
$WorkSheet = $WorkBook.Worksheets.Item("Backup Data")
$x = 2
#Add headers for the rows
$WorkSheet.Cells.Item(1,1) = "BufferCount"
$WorkSheet.Cells.Item(1,2) = "MaxTransferSize"
$WorkSheet.Cells.Item(1,3) = "Backup Time /sec"
$WorkSheet.Cells.Item(1,4) = "Backup Speed MB/sec"
$WorkSheet.Cells.Item(1,5) = "Backup File Count"
$WorkSheet.Cells.Item(1,6) = "Caption"
$WorkSheet.Cells.Item(1,8) = "BufferCount"
$WorkSheet.Cells.Item(1,9) = "MaxTransferSize"
$WorkSheet.Cells.Item(1,10) = "Backup Time /sec"
$WorkSheet.Cells.Item(1,11) = "Backup Speed MB/sec"
$WorkSheet.Cells.Item(1,12) = "Backup File Count"
$WorkSheet.Cells.Item(1,13) = "Caption"
#Put in the information for the default backup
#All performance improvements will be identified against this value
$BackupTestResults | Select-Object -First 1 -Skip 1 | % {
$WorkSheet.Cells.Item($x,1) = $_.BufferCount
$WorkSheet.Cells.Item($x,2) = $_.MaxTransferSize
$WorkSheet.Cells.Item($x,3) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($x,4) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($x,5) = $_.BackupFileCount
$WorkSheet.Cells.Item($x,6) = "DEFAULT BACKUP! - Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize + " Files:" + [string]$_.BackupFileCount
$x++}
#Put in the absolute fastest backup to nul
#We know we can't get any faster than this, good reference point
$BackupTestResults | Where-Object {$_.BackupFileCount -eq 0} | Sort-Object BackupSpeedInMBSec -Descending | Select-Object -First 1 | % {
$WorkSheet.Cells.Item($x,1) = $_.BufferCount
$WorkSheet.Cells.Item($x,2) = $_.MaxTransferSize
$WorkSheet.Cells.Item($x,3) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($x,4) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($x,5) = $_.BackupFileCount
$WorkSheet.Cells.Item($x,6) = "FASTEST NUL BACKUP! - Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize
$x++}
#Put in the information for all the backups, ordered by the backup speed in MB/sec
$BackupTestResults | Where-Object {$_.BackupFileCount -gt 0} | Sort-Object BackupSpeedInMBSec -Descending | % { if ($_.BufferCount -gt 0)
{
$WorkSheet.Cells.Item($x,1) = $_.BufferCount
$WorkSheet.Cells.Item($x,2) = $_.MaxTransferSize
$WorkSheet.Cells.Item($x,3) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($x,4) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($x,5) = $_.BackupFileCount
$WorkSheet.Cells.Item($x,6) = "Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize + " Files:" + [string]$_.BackupFileCount
$x++}
}
$y = 2
$BackupTestResults | Select-Object -First 1 -Skip 1 | % {
$WorkSheet.Cells.Item($y,10) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($y,11) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($y,12) = $_.BackupFileCount
$WorkSheet.Cells.Item($y,13) = "DEFAULT BACKUP! - Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize + " Files:" + [string]$_.BackupFileCount
$y++}
$BackupTestResults | Where-Object {$_.BackupFileCount -eq 0} | Sort-Object BackupTimeInSeconds -Descending | Select-Object -First 1 | % {
$WorkSheet.Cells.Item($y,10) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($y,11) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($y,12) = $_.BackupFileCount
$WorkSheet.Cells.Item($y,13) = "FASTEST NUL BACKUP! - Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize
$y++}
#Put in the information for all the backups, ordered by the backup time in seconds
$BackupTestResults | Where-Object {$_.BackupFileCount -gt 0} | Sort-Object BackupTimeInSeconds | % { if ($_.BufferCount -gt 0)
{
$WorkSheet.Cells.Item($y,10) = $_.BackupTimeInSeconds
$WorkSheet.Cells.Item($y,11) = $_.BackupSpeedInMBSec
$WorkSheet.Cells.Item($y,12) = $_.BackupFileCount
$WorkSheet.Cells.Item($y,13) = "Buffers:" + [string]$_.BufferCount + " MaxTransfer:" + [string]$_.MaxTransferSize + " Files:" + [string]$_.BackupFileCount
$y++}
}
#Add a couple of charts for a nice visual layout of backup performance
$WorkBook.Charts.Add() | Out-Null
$Chart = $WorkBook.ActiveChart
$Chart.ApplyLayout(3)
$Chart.SetSourceData($WorkSheet.Range("D1:D$x"))
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("F2:F$x")
$Chart.SetSourceData($WorkSheet.Range("D1:D$x"))
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("F2:F$x")
$Chart.Name = "Backup Speed"
$WorkBook.Charts.Add() | Out-Null
$Chart = $WorkBook.ActiveChart
$Chart.ApplyLayout(3)
$Chart.SetSourceData($WorkSheet.Range("J1:J$y"))
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("M2:M$y")
$Chart.Name = "Backup Time"
}
CATCH
{
}
}
ELSE
{
Write-Error "The file $BackupLogFileName does not exist."
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment