How do I provide an execution plan to someone for analysis?

Question

I have been asked in another dba.stackexchange.com question to provide an actual execution plan. What is the best way to acquire this in order provide all the information required for analysis?

Inside SSMS I've run the query with "Include Actual Execution Plan" selected. I'm then given the "Execution plan" tab.

Should I:

1. Attempt to screen-grab the graphic, which is at least 6 times wider than my screen and 3 times taller. If I zoom to fit, then everything becomes unreadable.
2. "Show Execution Plan XML" and paste that into the question, given that the xml file is 2,782 lines long. Or is recommended to post it on a service like Pastebin.com and link to it?
3. "Save Execution Plan As..." and then upload that somewhere and provide a link to it? Any recommended hosts?
4. Something different?

A quick browse of the site shows a lot of method 1 exposing portions of the plan. However, I'm after the method that will best assist potential answerers without over-exposing information.

Since the execution plan is being provided for analysis purposes to an open Internet, is there anything that can or should be done in order to make sure sensitive information isn't provided with the plan? Is there any information that can be deemed sensitive inside the execution plan aside from database or column names that include text like a client name? How useful would the execution plan be in attempting to hack/attack the database?

Answer 1

For me the best option would be #3 - save the file as .sqlplan and post it somewhere that does not require a login to download. You want to make obtaining the file easy, not hard, for people trying to help you. Saving as a .sqlplan makes it easy for us to open it in Management Studio, Plan Explorer, etc.

The problems with the other methods:

1. A picture in this case is not worth a thousand words. Sure, if we zoom in and pan around, we can figure out the most expensive operator, but we've lost all of the details that are found in the tooltips, the properties panel, etc. Never mind that sometimes the most expensive operator is not the biggest problem.

2. XML is just massive and unreadable and only causes more work. In order to make that usable for analysis I either need a very good XML parser or I need to copy, paste into a file, save as a different extension, and open. Assuming that it doesn't get mangled anywhere along the way.

As for exposing information, the execution plan will contain information about your database & table names, and schema, but it will not contain anything about usernames, passwords, etc. unless you have actually captured a query that has a password in plain text. To hide sensitive table names, column names, or data in queries, you can perform a search & replace on the .sqlplan file, just be careful to avoid words that might appear within the XML schema - (not an easy task). e.g. if you have a table named ShowPlan and you search and replace that with Foo, the execution plan becomes kind of useless. As Martin reminded me, entity names are escaped, so replacing [ShowPlan] with [Foo] should be safe.

Answer 2

For SQL Server, you can also upload the XML execution plan to PasteThePlan.com

Simply paste the XML form of the execution plan into the box, hit Submit, then share the link in your question or answer.

Answer 3

If you try to upload a longish PostgreSQL query plan, the best option is explain.depesz.com, I think. It provides a quite intuitive interface with which you can easily discover the critical parts.

Answer 4

dbfiddle.uk does a great job of displaying execution plans for SQL Server, Oracle, and Postgres. The default page (for each respective RDBMS) lists instructions which should be pretty self explanatory. It even handles the graphical XML output quite well for SQL which I wasn't expecting at all.