Earlier this month, I blogged about a PowerShell script from fellow MVP Jonathan Kehayias (blog | @SQLPoolBoy). Jonathan's script enables you to alter an execution plan to hide potentially sensitive data, such as table names and column names.
With the latest build of SQL Sentry Plan Explorer, you can now perform a similar style of obfuscation directly within the application – whether it is your intention to upload the plan to support or just to share it with a colleague.
There is a new menu item: Edit > Anonymize, which will strip the statement text and change database, table and column names to very generic things like Database1.Table2.Column3:
Here is an example before and after applying the option:
A plan that may contain sensitive information
Same plan, after anonymizing
Notice that it puts the obfuscated plan in a new tab, so you can continue working with the original, non-obfuscated version.
You may have noticed another difference in the screen shots above. A new feature that has been introduced in Plan Explorer is custom layouts – giving you a massive amount of flexibility in the way you view graphical plans. I won't steal any of his thunder, so you'll want to check out this post from Greg Gonzalez (blog | @SQLsensei) for more details:
https://blogs.sentryone.com/author/GregGonzalez/plan-explorer-pro-2-5-query-plans-your-way/
What about using more memorable table names like fruit names or geographic locations? Numbers are hard to remember when reading the plan.
Actually I think there is some logic to the numbers, especially if you keep a copy of the un-sanitized plan for an easy cross-reference. While fruits might work for you, I'm not convinced that everyone will find it easier to remember that
tangerine = Salesandpineapple = Products. :-) We could use Smurfs, or Star Wars characters, or Greek gods – I still don't find those things inherently better than a sequential number. The primary goal is to protect your table names etc. from the world at large.Tried Anonymize yesterday and it looks really good. Very useful addition.
Chris
It will not work, due to temp table use… why will Plan Explorer and the Anonymize not work of you are using temp tables?
Hi David, can you tell me what "will not work" means? I am able to get the anonymize feature to work just fine for plans involving #temp tables. Could you provide a repro, indicate the symptom or error message, and also what version of Plan Explorer you're using and against what version of SQL Server?
Hey guys, is there a way to Anonymise and save the plan rather than just uploading it?