Today I was doing a tuning operation for a query on a customer's database and, when asking to the System Administrator if he can send me the query execution plan, I've received on my mailbox a nice JPG image, a screenshot of the customer's desktop where the query execution plan was displayed. Ok... better than nothing... :P
It's not well documented, but SQL Server 2005 has a nice feature that permits you to save a query execution plan in a format that can re-loaded or can be sent to another person for example via email. How to do it?
If you have a query to examine, you can turn on the query execution plan and you'll obtain something like this:
If you activate the SET SHOWPLAN_XML feature, SQL Server 2005 permits you to obtain the query plan in a special XML format. The result is a new XML column and this is what happens:
If you click on the hyperlink, you can see the XML result. Now save the newly created XML file by giving the .sqlplan extension.
By saving the XML file with the new extension, you can see that magically your file will change the displayed icon:
Now try to reload this file with SQL Server Management Studio and magically you'll see your saved execution plan:
By using the .sqlplan format you can distribute your query execution plan as you want (for example you can mail it) and this is a great way to use for troubleshooting.
I don't undertand why SQL Server Management Studio doesn't show the .sqlplan format on the "Save as" dialog box when you try to save the XML file... I think Microsoft should have to add this "feature".
UPDATE: Ayende has signalled this post on his blog by giving the short way:
