Analyze-PhysicalIndexes2013.ps1

# Reports the number of indexes per folder for a given mailbox.
# Mailbox can be specified by display name or mailbox number.

[CmdletBinding()]
param (
    [Parameter(Position = 0, Mandatory = $true)]
    [string]
    $Database,

    [Parameter()]
    [int]
    $MailboxNumber,

    [Parameter()]
    [string]
    $DisplayName
)

. $exscripts\ManagedStoreDiagnosticFunctions.ps1

if ($null -eq (Get-Command Get-StoreQuery -ErrorAction SilentlyContinue)) {
    Write-Error "ManagedStoreDiagnosticFunctions not loaded."
    return
}

if ($null -ne $MailboxNumber) {
    $mb = Get-StoreQuery -Database $Database -Query "Select DisplayName from Mailbox where MailboxNumber = $MailboxNumber"
    if ($null -eq $mb) {
        Write-Error "MailboxNumber" $MailboxNumber "not found."
        return
    }

    $DisplayName = $mb.DisplayName

} elseif ($null -ne $DisplayName) {
    $mb = Get-StoreQuery -Database $Database -Query "Select MailboxNumber,DisplayName from Mailbox" | Where-Object { $_.DisplayName -eq $DisplayName }
    if ($null -eq $mb) {
        Write-Error "DisplayName" $DisplayName "not found."
        return
    } elseif ($null -ne $mb.Length) {
        Write-Error "Found more than one mailbox with DisplayName" $DisplayName
        return
    }

    $MailboxNumber = $mb.MailboxNumber

} else {
    return
}

Write-Host "Checking mailbox number" $MailboxNumber "with display name" $DisplayName

$pis = Get-StoreQuery -Database $Database -Query "Select * from PseudoIndexControl where MailboxPartitionNumber = $MailboxNumber" -Unlimited

Write-Host "Found" $pis.Count "indexes in mailbox" $MailboxNumber

$folders = Get-StoreQuery -Database $Database -Query "Select * from Folder where MailboxPartitionNumber = $MailboxNumber" -Unlimited

Write-Host "Found" $folders.Count "folders in this mailbox."

$searchFolders = $folders | Where-Object { $_.LogicalIndexNumber -ne "NULL" }

Write-Host $searchFolders.Count "of those are search folders."

$searchFolderIndexNumbers = New-Object 'System.Collections.Generic.HashSet[int]'
$searchFolders | ForEach-Object { $searchFolderIndexNumbers.Add($_.LogicalIndexNumber) | Out-Null }
$pisLinkedToSearchFolders = $pis | Where-Object { $searchFolderIndexNumbers.Contains($_.LogicalIndexNumber) }

Write-Host $pisLinkedToSearchFolders.Count "of the indexes are linked to search folders."

$pisByFolder = $pis | Group-Object FolderId | Sort-Object Count -Descending

Write-Host "Folders with the most indexes:"

$pisByFolder | Select-Object -First 20 | ForEach-Object {
    $id = $_.Name # The Name field of the group is the FolderId
    $displayName = ($folders | Where-Object { $_.FolderId -eq $id }).DisplayName
    [PSCustomObject]@{
        Name     = $displayName
        FolderId = $_.Name
        Count    = $_.Count
    } }