SQL SERVER: Combine Plans into One

CombinePlans.ps1

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

	[Parameter(Mandatory=$true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
	[string] $planHandle
 )

 <#
    http://blog.sqlgrease.com/plan-cached-dm_exec_query_plan-returns-null/ 
    Tim C: I wrote this powershell to combine the outut of the plans from his query. Makes it easier to look at in plan explorer when they are combined
 #>

Clear-Host

$query = "SELECT SUBSTRING(c.text, 
	(a.statement_start_offset / 2) + 1, (
		(CASE a.statement_end_offset
			WHEN -1 THEN
				DATALENGTH(c.text)
                ELSE
                a.statement_end_offset
			END - a.statement_start_offset
		) / 2) + 1) AS statement_text,
	[query_plan] = CONVERT(XML, b.query_plan)
FROM sys.dm_exec_query_stats a
    CROSS APPLY sys.dm_exec_text_query_plan(a.plan_handle, a.statement_start_offset, a.statement_end_offset) b
    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) c
WHERE a.plan_handle = `$(plan_handle)
ORDER BY a.statement_start_offset"


$SqlcmdVariables= "plan_handle = $planHandle"
#really dont like passing the variables this way, would prefer to use standard sql variables. however i cannot use invoke-sqlcmd2 as not all users of this script are on the latest PS
$results =  Invoke-SqlCmd -ServerInstance $ServerInstance -Database "master" -As DataTables -Query $query -Variable $SqlcmdVariables -MaxCharLength ([int]::MaxValue)

[xml]$showPlanXml = "<ShowPlanXML xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/showplan"">
  <BatchSequence>
    <Batch>
      <Statements>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>"

$planXmlStatements = $showPlanXml.ShowPlanXML.BatchSequence.Batch.Statements


foreach($result in $results) {
    [xml]$qp = $result.query_plan

    $statements = $qp.ShowPlanXML.BatchSequence.Batch.Statements
    
    foreach($statement in $statements.ChildNodes){
        $node = $showPlanXml.ImportNode($statement, $true)
        $planXmlStatements.AppendChild($node) | Out-Null
    }
}

$dir = "$($env:TEMP)\Plans\"
$fn = "$($dir)Plan.sqlplan"

if (!(Test-Path -Path $dir -PathType Container)) {
    New-Item -Path $dir -ItemType Directory 
}

$showPlanXml.OuterXml | Out-File -LiteralPath $fn -Force

ii -path $dir