{"id":1710,"date":"2013-02-28T00:02:46","date_gmt":"2013-02-28T05:02:46","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1710"},"modified":"2017-04-13T13:05:42","modified_gmt":"2017-04-13T17:05:42","slug":"execution-plan-sanitizer-v2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/","title":{"rendered":"Execution Plan Sanitizer v2"},"content":{"rendered":"<p>Almost two years ago I blogged about <a title=\"Sanitizing Execution Plans using Powershell\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sanitizing-execution-plans-using-powershell\/\">Sanitizing Execution Plans using PowerShell<\/a>. The original intent behind this idea was to be able to provide completely sanitized execution plans that could be shared outside of your business environment without the risk of sharing any design elements that may be considered Intellectual Property.\u00a0Since writing that post I&#8217;ve had many people contact me with suggestions for the sanitized output, and so I&#8217;ve made a bunch of minor changes to the code to fix various output issues and suggestions.<\/p>\n<p>Fast forward two years and I believe this idea is even more useful today, especially since <a href=\"https:\/\/sqlperformance.com\/2013\/02\/t-sql-queries\/plan-explorer-upload-feature\" target=\"_blank\">you can now upload execution plans<\/a> using <a href=\"https:\/\/www.sqlsentry.com\/plan-explorer\/sql-server-query-view.asp\">SQL Sentry Plan Explorer<\/a> to the SQLPerformance.com website to get assistance from the community in diagnosing performance problems or query plan regressions. When I found out about this feature being included in Plan Explorer, my first question was whether the ability to sanitize or obfuscate the plan would be included, and I found that it wasn&#8217;t part of the initial launch plans but may eventually be part of the product. To be perfectly honest, the number of total users that worry about schema information in an execution plan is a minority so from a business standpoint I totally understand it not being in the first release of this functionality. We&#8217;ve already seen a few plans posted to the SQLPerformance.com site show up on the #sqlhelp tag on Twitter, so I know that the new upload feature is being used.<\/p>\n<p>Earlier today I was asked if I realized that the script was broken and didn&#8217;t sanitize the output XML properly. Unfortunately I never took the time to upload the changes I&#8217;ve made over the last two years, so ran my local script through some plans and then asked my good friend Aaron Bertrand (<a href=\"http:\/\/sqlblog.com\/blogs\/aaron_bertrand\/\" target=\"_blank\">Blog<\/a>|<a href=\"https:\/\/mobile.twitter.com\/AaronBertrand\" target=\"_blank\">Twitter<\/a>) to test out a new version of it for me. He sent me back some feedback which I was able to quickly implement and now there is a v2 version of the script that should handle most of the plan sanitizing issues that existed in the initial version of the PowerShell script.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/02\/2013_2_27_executionplansanitizer.ps1\">2013_2_27_ExecutionPlanSanitizer<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Almost two years ago I blogged about Sanitizing Execution Plans using PowerShell. The original intent behind this idea was to be able to provide completely sanitized execution plans that could be shared outside of your business environment without the risk of sharing any design elements that may be considered Intellectual Property.\u00a0Since writing that post I&#8217;ve [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,1],"tags":[],"class_list":["post-1710","post","type-post","status-publish","format-standard","hentry","category-execution-plans","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Execution Plan Sanitizer v2 - Jonathan Kehayias<\/title>\n<meta name=\"description\" content=\"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Execution Plan Sanitizer v2 - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-02-28T05:02:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T17:05:42+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Execution Plan Sanitizer v2\",\"datePublished\":\"2013-02-28T05:02:46+00:00\",\"dateModified\":\"2017-04-13T17:05:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/\"},\"wordCount\":368,\"commentCount\":4,\"articleSection\":[\"Execution Plans\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/\",\"name\":\"Execution Plan Sanitizer v2 - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2013-02-28T05:02:46+00:00\",\"dateModified\":\"2017-04-13T17:05:42+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"description\":\"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/execution-plan-sanitizer-v2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Execution Plans\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/execution-plans\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Execution Plan Sanitizer v2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Execution Plan Sanitizer v2 - Jonathan Kehayias","description":"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/","og_locale":"en_US","og_type":"article","og_title":"Execution Plan Sanitizer v2 - Jonathan Kehayias","og_description":"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-02-28T05:02:46+00:00","article_modified_time":"2017-04-13T17:05:42+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Execution Plan Sanitizer v2","datePublished":"2013-02-28T05:02:46+00:00","dateModified":"2017-04-13T17:05:42+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/"},"wordCount":368,"commentCount":4,"articleSection":["Execution Plans"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/","name":"Execution Plan Sanitizer v2 - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2013-02-28T05:02:46+00:00","dateModified":"2017-04-13T17:05:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"description":"A PowerShell script for sanitizing the contents of Execution Plan XML information generated by SQL Server for demonstrations or uploading online.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/execution-plan-sanitizer-v2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Execution Plans","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/execution-plans\/"},{"@type":"ListItem","position":3,"name":"Execution Plan Sanitizer v2"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1710","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1710"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1710\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1710"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1710"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1710"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}