THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

PowerShell to fetch a SQL Execution Plan

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 -MaxCharLength 9999999 -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.

Published Tuesday, December 07, 2010 12:14 PM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SQLvariant said:

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

December 6, 2010 8:31 PM
 

Rob Farley said:

SQL Server is quite remarkable in a bunch of ways. In this post, I’m using the way that the Query Optimizer

January 3, 2011 12:55 AM
 

juerg said:

Hi

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;`ngo`n$qu

ery").Item <<<< ( 0)) }

   + CategoryInfo          : NotSpecified: (:) [], RuntimeException

   + FullyQualifiedErrorId : RuntimeException

any idea?

Thanks

November 4, 2012 11:11 AM
 

Rob Farley said:

Hi juerg,

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

Rob

November 13, 2012 5:46 AM
 

Rob Farley said:

A couple of years I ago I was going to write a song about automation, in reggae style, which could maybe

January 13, 2014 7:08 PM
 

Rob Farley said:

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.

Rob

February 17, 2014 12:43 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement