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?


migrated from dba.stackexchange.com Sep 26 '12 at 15:08

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

Should we only discuss this for SQL Server? If yes, I'd suggest to add a corresponding tag. – a_horse_with_no_name Oct 7 '12 at 21:28
@a_horse_with_no_name it was tagged when it was a proper question. It doesn't appear there is a sql-server tag on meta, and I can't create tags. Hopefully whoever migrated this, or another privileged user, will step up. – nosilleg Oct 8 '12 at 8:47
up vote 20 down vote accepted

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.

+1 Was just writing a similar response. I think that in the plan XML all object names (except for in the query text) are quoted so replacing [ShowPlan] with [Foo] ought to be quite safe. – Martin Smith Sep 26 '12 at 12:25
@MartinSmith yes, that's a good point, will update. – Aaron Bertrand Sep 26 '12 at 12:32
+1 I was about to say the same when I read the question. sqlplan is definitely the way to go. – ivanmp Sep 26 '12 at 12:37
It might be nice if the site had a built-in facility for this (like image hosting). – Paul White Sep 27 '12 at 7:03
This seems more like a regular question than a meta question to me: "How to provide showplan output" or some such thing. Attachment capability would be great. – Eric Higgins Sep 28 '12 at 16:53
Just as a note, User Experience uses a special plugin(for lack of better terms) for mockups. – hexafraction Oct 1 '12 at 22:31

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.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .