Sanitizing Execution Plans using Powershell

A few weeks ago I tweeted a couple of images showing a really nasty execution plan in SQL Sentry’s free Plan Explorer tool being displayed on a 65 inch touch screen LCD monitor.

Large Plan Explorer Plan Large Plan Explorer Plan2

 

I’ve been using Plan Explorer to look at execution plans since its early beta’s, when it was known as Plan Viewer, after seeing a demo of Performance Advisor v6 by Greg Gonzalez (Blog|Twitter).  During the beta I provided a lot of feed back to Greg and I pushed the tool to its limits a couple of times with some of the nasty execution plans I had.  In the case of the plan shown above, Management Studio falls to its knees trying to manage the plan, and is everything except useful when the plan is open, but Plan Explorer handles it without a hitch.

After posting these images and getting some comments, I began to realize how useful this type of plan could be in doing demo’s of the tool, but one of the problems with an execution plan is that it contains a lot of very specific information about the database structures and code that could be considered intellectual property.  It is possible to retain the integrity of the plan structure, and what it shows, while removing all of the object and statement information from the plan by replacing them with meaningless values.  During the beta’s I did this a couple of times when I came across an issue, or thought of a useful feature, but it was always a painstaking manual process.  This time I set out to do it with Powershell by parsing the XML Showplan and performing replace operations on the XML nodes. 

Loading the XML Showplan from a .sqlplan file is relatively straightforward, and I’ve done quite a bit of plan shredding in the past, so I thought this would be a very simple task to complete.  It turns out that I had a bit to learn about processing XML in .NET and by extension Powershell.  The first problem I encountered was how to use the ShowPlan XmlNamespace so that I could shred the document to find the elements of interest.  This is accomplished using the XmlNamespaceManager in .NET.

#Setup the XmlNamespaceManager and add the ShowPlan Namespace to it.

$nsMgr = new-object 'System.Xml.XmlNamespaceManager' $xml.NameTable;

$nsMgr.AddNamespace("sm", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");

The SelectNodes() method of the .NET XmlDocument is overloaded and accepts an XPath and XmlNamespaceManager to determine the nodes to select from the XmlDocument.  With the correct nodes selected replacing the information as easy as setting a new value for the the element of interest.

#Replace the StatementText for all statements

$xml.SelectNodes("//sm:StmtSimple", $nsMgr) | % {

$_.StatementText = "–Statement text stripped by ExecutionPlanSanitizer Powershell Script (https://www.sqlskills.com/blogs/jonathan)" } 

The attached Powershell script replaces the statements, database names, object names, index names, non-dbo schema names,  and column names of the input file and then writes the new plan to an output file that still conforms to the schema and can be shared without sharing intellectual property.

ExecutionPlanSanitizer.ps1 (3.26 kb)

3 thoughts on “Sanitizing Execution Plans using Powershell

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.