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