PowerShell to fetch a SQL Execution Plan

Posted by Rob Farley on SQL Blog See other posts from SQL Blog or by Rob Farley
Published on Tue, 07 Dec 2010 01:14:32 GMT Indexed on 2010/12/08 5:53 UTC
Read the original article Hit count: 430

Filed under:
|

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:

function Get-SqlPlan([string] $query, [string] $server, [string] $db)
{ return ([xml] (invoke-sqlcmd -Server $server -Database $db -Query "set showplan_xml on;`ngo`n$query").Item( 0)) }

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

To use this, I just do something like:

$plan = get-sqlplan "select name from Production.Product" "." "AdventureWorks"

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

image

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.

© SQL Blog or respective owner

Related posts about powershell

Related posts about sql