SQL SERVER ExtractMissingIndexesFromPlan

ExtractMissingIndexesFromPlan.ps1

 [CmdletBinding()]
 Param (
	[Parameter(Mandatory=$true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
	[string] $path
 )

Clear-Host

[xml]$plan = Get-Content -Path ($path.TrimStart('"').TrimEnd('"'))

$plan.ShowPlanXML.SelectNodes(".//MissingIndex")

$namespace = @{e="http://schemas.microsoft.com/sqlserver/2004/07/showplan"}
$indexes = Select-Xml -Xml $plan "//e:MissingIndex" -Namespace $namespace

$output = @()

foreach($index in $indexes) {
    $impact = $index.Node.ParentNode.Impact
    $schema = $index.Node.Schema
    $table = $index.Node.Table

    $indexCols = $index.Node.ColumnGroup | Where-Object { $_.Usage -ne "INCLUDE" }
    $includeCols = $index.Node.ColumnGroup| Where-Object { $_.Usage -eq "INCLUDE" }

    $names = [string]::Join(", ", $indexCols.Column.Name)
    $includes = ""
    if($includeCols) {
        $includes = "INCLUDE ($([string]::Join(", ", $includeCols.Column.Name)))"
    }

    $output += [PSCustomObject]@{ 
        index = "CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON $schema.$table ($names) $includes" 
        impact = $impact 
    }
}

$output | Group-Object { $_.Index } | `
    Select-Object Count, @{ 
            Name = "Impact" 
            Expression = { ($_[0].Group.impact | Measure-Object -Maximum).Maximum }
        }, @{
            Name = "Index" 
            Expression = { $_.Name }
        } | `
    Sort-Object Impact -Descending