Skip to content

Instantly share code, notes, and snippets.

@AnderssonPeter
Last active January 4, 2021 20:21
Show Gist options
  • Save AnderssonPeter/8261e255630d6f672ba5bc80f51b017f to your computer and use it in GitHub Desktop.
Save AnderssonPeter/8261e255630d6f672ba5bc80f51b017f to your computer and use it in GitHub Desktop.
Add-Type -TypeDefinition @"
public enum AggregationType
{
Min,
Max,
Sum,
Avg
}
"@
function Get-CounterData
{
param($CounterInformation, $CollectionTime)
Write-Host 'Get-CounterData'
$Counters = $CounterInformation.Keys | % ToString
$StartDateTime = Get-Date
$CounterResult = Get-Counter $Counters -MaxSamples $CollectionTime -SampleInterval 1
$EndDateTime = Get-Date
Aggregate-Counters -CounterInformation $CounterInformation -CounterResult $CounterResult -StartDateTime $StartDateTime -EndDateTime $EndDateTime
}
function Aggregate-Counters
{
param($CounterInformation, $CounterResult, $StartDateTime, $EndDateTime)
Write-Host 'Aggregate-Counters'
$result = New-Object –TypeName PSObject
$result | Add-Member -NotePropertyName 'Start' -NotePropertyValue $StartDateTime
$result | Add-Member -NotePropertyName 'End' -NotePropertyValue $EndDateTime
$rawCounterData = $CounterResult.CounterSamples | Select-Object -Property Path, CookedValue
foreach ($counterName in $CounterInformation.Keys)
{
$relevantCounters = $rawCounterData | Where-Object {$_.Path -ilike '*' + $counterName }
foreach ($aggregate in $CounterInformation[$counterName].Keys)
{
$outputName = $CounterInformation[$counterName][$aggregate]
$value = $null
switch ($aggregate)
{
([AggregationType]::Min)
{
$value = $relevantCounters | Measure-Object -Property CookedValue -Minimum | Select-Object -ExpandProperty Minimum;
continue
}
([AggregationType]::Max)
{
$value = $relevantCounters | Measure-Object -Property CookedValue -Maximum | Select-Object -ExpandProperty Maximum;
continue
}
([AggregationType]::Sum)
{
$value = $relevantCounters | Measure-Object -Property CookedValue -Sum | Select-Object -ExpandProperty Sum;
continue
}
([AggregationType]::Avg)
{
$value = $relevantCounters | Measure-Object -Property CookedValue -Average | Select-Object -ExpandProperty Average;
continue
}
}
$result | Add-Member -NotePropertyName $outputName -NotePropertyValue $value
}
}
$result
}
$fileExtension = "csv"
function Write-CountersToDisk
{
param($CounterData, $Directory, $ExportType)
Write-Host 'Write-CountersToDisk'
$counter = 0
$suffix = ''
$done = $false
$date = Get-Date -format yyyy-MM-dd
while($Done -eq $false -and $Counter -le 9)
{
Try
{
$filename = $ExportType + "_" + $date + $suffix + "." + $fileExtension
Write-Host "Trying to write $filename"
$filePath = $Directory + $filename
$CounterData | Export-Csv -Path $filePath -Delimiter ";" -NoTypeInformation -Append
$done = $true
}
Catch [Exception]
{
Write-Host "Failed to create file " + $_.Exception.GetType().FullName, $_.Exception.Message
$counter++
$suffix = '(' + $counter + ')'
}
}
#Notify if we failed
if ($Done -eq $false)
{
Notify-WriteCountersToDiskFailed -Directory $Directory -ExportType $ExportType
}
}
function Notify-WriteCountersToDiskFailed
{
param($Directory, $ExportType)
Write-Host 'Write-CountersToDisk'
#Todo: Notify that we failed
}
function Clean-OldCounterFiles
{
param($Directory, $ExportType, $NumberOfDaysToKeepFiles)
Write-Host 'Clean-OldCounterFiles'
$filenameRegex = "^" + $ExportType + "_(?<Date>\d{4}-\d{2}-\d{2})(?:\(\d\))?\." + $fileExtension + "$"
#Cleanup
$files = Get-ChildItem $Directory -Filter ("*." + $fileExtension)
$files | Foreach-Object {
$filePath = $_.FullName
$filename = [System.IO.Path]::GetFileName($filePath)
$match = [regex]::Match($filename, $filenameRegex)
#Write-Host $FilePath
#Write-Host $Filename
if ($match.Success -eq $true)
{
#Write-Host $Match.Groups["Date"].Value
$fileDate = [datetime]::ParseExact($match.Groups["Date"].Value, "yyyy-MM-dd", $null)
$timeSince = New-TimeSpan -Start $fileDate -End (Get-Date).Date
if ($timeSince.TotalDays -ge $NumberOfDaysToKeepFiles)
{
Write-Host "Deleting $filePath"
Remove-Item $filePath
}
}
}
}
. "$PSScriptRoot\ExportPerfomanceCountersHelpers.ps1"
$SqlServerCounterPrefix = '\MSSQL$MSSQL_2008'
$Counters = @{
($SqlServerCounterPrefix + ":Buffer Manager\Buffer cache hit ratio") = @{[AggregationType]::Avg = "Buffer_cache_hit_ratio_avg"};
($SqlServerCounterPrefix + ":Buffer Manager\Page life expectancy") = @{[AggregationType]::Min = "Page_life_expectancy_min"};
($SqlServerCounterPrefix + ":Buffer Manager\Checkpoint pages/sec") = @{[AggregationType]::Sum = "Checkpoint_pages_sum"};
($SqlServerCounterPrefix + ":General Statistics\Processes blocked") = @{[AggregationType]::Sum = "Processes_blocked_sum"};
($SqlServerCounterPrefix + ":General Statistics\User Connections") = @{[AggregationType]::Max = "User_connections_max"};
($SqlServerCounterPrefix + ":SQL Statistics\Batch Requests/sec") = @{[AggregationType]::Sum = "Batch_requests_sum"};
($SqlServerCounterPrefix + ":SQL Statistics\SQL Compilations/sec") = @{[AggregationType]::Sum = "Compilations_sum"};
($SqlServerCounterPrefix + ":SQL Statistics\SQL Re-Compilations/sec") = @{[AggregationType]::Sum = "ReCompilations_sum"};
($SqlServerCounterPrefix + ":Access Methods\Page splits/sec") = @{[AggregationType]::Sum = "Page_splits_sum"};
($SqlServerCounterPrefix + ":Locks(_Total)\Lock Waits/sec") = @{[AggregationType]::Sum = "Lock_waits_sum"};
($SqlServerCounterPrefix + ":Locks(_Total)\Lock Wait Time (ms)") = @{[AggregationType]::Sum = "Lock_wait_time_sum"};
($SqlServerCounterPrefix + ":Locks(_Total)\Number of Deadlocks/sec") = @{[AggregationType]::Sum = "Number_of_deadlocks_sum"};
}
$DataDirectory = 'Z:\Temp\PowershellTest\'
$Type = 'SQLServerStatistics'
$CounterData = Get-CounterData -CounterInformation $Counters -CollectionTime 300
Write-CountersToDisk -CounterData $CounterData -Directory $DataDirectory -ExportType $Type
Clean-OldCounterFiles -Directory $DataDirectory -ExportType $Type -NumberOfDaysToKeepFiles 7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment