Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to combine index usages stats across multiple AG nodes
$PrimaryInstance = 'Sql1'
$SecondaryInstance = 'Sql2'
$SqlInstance = $PrimaryInstance, $SecondaryInstance
$Database = 'DbName'
$results = Get-DbaHelpIndex -SqlInstance $SqlInstance -Database $Database
$export = @()
$export += $results | Where-Object {$_.SqlInstance -eq $PrimaryInstance} | ForEach-Object -PipelineVariable pIndex -Process { $_ } | ForEach-Object {
# get the matching secondary row
$sIndex = $results | Where-Object { $_.SqlInstance -eq $SecondaryInstance -and $_.ObjectName -eq $pIndex.ObjectName -and $_.Index -eq $pIndex.Index}
[PSCustomObject]@{
PrimaryInstance = $pIndex.SqlInstance
SecondaryInstance = $sIndex.SqlInstance
Database = $pIndex.Database
Object = $pIndex.Object
Index = $pIndex.Index
IndexType = $pIndex.IndexType
KeyColumns = $pIndex.KeyColumns
IncludeColumns = $pIndex.IncludeColumns
FilterDefinition = $pIndex.FilterDefinition
Rows = $pIndex.IndexRows
SizeKB = $pIndex.Size
PrimaryIndexReads = [int]$pIndex.IndexReads
SecondaryIndexReads = [int]$sIndex.IndexReads
TotalIndexReads = [int]$pIndex.IndexReads + $sIndex.IndexReads
PrimaryIndexUpdates = [int]$pIndex.IndexUpdates
SecondaryIndexUpdates = [int]$sIndex.IndexUpdates
TotalIndexUpdates = [int]$pIndex.IndexUpdates + $sIndex.IndexUpdates
}
}
$export | Export-Excel -Path IndexUsage.xlsx -WorksheetName IndexUsage -TableName IndexUsage -AutoSize
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment