Aaron Bertrand

Obfuscate sensitive data in your execution plans

March 1, 2013 by in SQL Plan, T-SQL Queries | 2 Comments
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts →

Recently we released a new version of SQL Sentry Plan Explorer, adding the ability to upload plans to our new Q & A site, answers.SQLPerformance.com, where execution plan experts can assist with analysis and provide feedback on improving performance (or at least help with understanding).

I've blogged about this feature, and mentioned that there may be cases where you want to protect sensitive or proprietary information – this can be to protect your company's IP, information about your employer or industry, or even your identity.

Jonathan Kehayias (blog | @SQLPoolBoy) of SQLskills released a free PowerShell script that you can use to mask all of your database, object, table, index and column names. The script is available for download here, along with some more background:

http://www.sqlskills.com/blogs/jonathan/execution-plan-sanitizer-v2/

Running the script is simple – invoking the script by name will prompt you for a filename (say C:\wherever\MyPlan.sqlplan), and will produce a sanitized version in the same folder called MyPlan_Cleaned.sqlplan. Once you've done that, it will turn a plan like this:

dirty_plan

Into a much less sensitive plan, like this:

cleanplan

It currently only works with .sqlplan files, so if you have a plan in Plan Explorer, you will need to save it as a .sqlplan as opposed to .queryanalysis or .pesession before sanitizing it. This means you will lose some of the additional information that Plan Explorer exposes, but as long as you keep the original copy, you can use it to map back to runtime metrics, correlate sanitized entity names, etc. during the ongoing discussion.

Now, this advice is not just for this site, and not just about Plan Explorer – you can use this script to obfuscate any .sqlplan that you plan (ahem) to distribute anywhere, publicly or otherwise, without any of the information you'll want to keep to yourself.

That said, Plan Explorer will soon have the ability to obfuscate your plans, whether you save them as .sqlplan, .queryanalysis or .pesession, or upload them directly to answers.SQLPerformance.com. So keep your version checker active. :-)