PowerShell to fetch a SQL Execution Plan

December 7, 2010

With PowerShell becoming the scripting language of choice for many people, I’ve occasionally wondered about using it to analyse execution plans. After all, an execution plan is just XML, and PowerShell is just one tool which will very easily handle xml.

The thing is – there’s no Get-SqlPlan cmdlet available, which has frustrated me in the past. Today I figured I’d make one.

I know that I can write T-SQL to get an execution plan using SET SHOWPLAN_XML ON, but the problem is that this must be the only statement in a batch. So I used go, and a couple of newlines, and whipped up the following one-liner:

(but please bear in mind that I have the SQL Snapins installed, which provides invoke-sqlcmd)

To use this, I just do something like:

And then find myself with an easy way to navigate through an execution plan!


At some point I should make the function more robust, but this should be a good starter for any SQL PowerShell enthusiasts (like Aaron Nelson) out there.

This Post Has 4 Comments

  1. SQLvariant

    This is Excellent Rob.  Thanks for throwing it together!!

  2. juerg

    thanks for the script, however e.g. trying to get the plan for AdventureWorks2012.dbo.uspGetBillOfMaterials I get
    Error: "Unexpected end of file has occurre
    d. The following elements are not closed: OutputList, RelOp, QueryPlan, StmtSimple, Statements, Stor
    edProc, StmtSimple, Statements, Batch, BatchSequence, ShowPlanXML. Line 1, position 4001."
    At D:\Projekte\Wincasa\QVReporting_home\CreateExecutionPlan.ps1:6 char:109
    + { return ([xml] (invoke-sqlcmd -Server $server -Database $db -Query "set showplan_xml on;ngon$qu
    ery").Item <<<< ( 0)) }
       + CategoryInfo          : NotSpecified: (:) [], RuntimeException
       + FullyQualifiedErrorId : RuntimeException
    any idea?

  3. Rob Farley

    Hi juerg,
    Make sure the file contains the right combinations of brackets, braces and parentheses, and has no extra characters in there.

  4. Rob Farley

    Oh Juerg,
    The error you got is just because the plan is larger than the 4000 characters that are being returned. If you use "-MaxCharLength 9999999" this should work better.

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs