CODEWITHSUNDEEP
sql-serverssmsssms-16
Victor Öhrström
Victor Öhrström

Reputation: 238

SQL Server Management Studio 2016 → Activity monitor → Show execution plan

I have a problem when I want to see the execution plan of an expensive recent query. The problem is that the result is displayed in XML and not as a design over the execution plan.

Does anyone know how to fix this?

Upvotes: 4

Views: 1199

Answers (3)

Dinesh vishe
Dinesh vishe

Reputation: 3598

users must have the appropriate permissions to execute the Transact-SQL queries for which a graphical execution plan is being generated, and they must be granted the SHOWPLAN permission for all databases referenced by the query

Upvotes: 0

Selim Balci
Selim Balci

Reputation: 920

I'm not sure if this will help but you could try execute SET SHOWPLAN_ALL OFF in a query window select the query you want to execute and press CTRL + L (by default, unless you've changed it) to view the graphical execution plan in the query window without actually executing your query.

Also, you could play around these SET commands:

SET SHOWPLAN_XML ON | OFF
SET SHOWPLAN_TEXT ON | OFF
SET SHOWPLAN_ALL ON | OFF
SET STATISTICS XML ON | OFF
SET STATISTICS PROFILE ON | OFF
SET STATISTICS IO ON | OFF
SET STATISTICS TIME ON | OFF

For further info, check this technet article: https://technet.microsoft.com/en-us/library/ms180765(v=sql.105).aspx

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

First of all, for me it works out of the box. Microsoft SQL Server Management Studio 13.0.15700.28. Make sure that you have the latest version. The one that I used for this test is not the very latest, but it works.

I open Activity Monitor in SSMS, expand the Recent Expensive Queries tab, right-click on a query and choose Show Execution Plan in the popup menu, then SSMS opens a new window with the graphical view of the plan.

If I right-click the graphical view of the plan there are commands "Save Execution Plan As..." and "Show Execution Plan XML" in the popup menu, which allow to save XML file with the plan.

Maybe all this works because I have SQL Sentry Plan Explorer installed.

In any case, if you have an XML file with the plan you can open it in SSMS as a graphical view. Change extension of the file from .xml to .sqlplan. Then open this file in SSMS using standard File - Open command.

I would highly recommend to use SentryOne Plan Explorer for analyzing the execution plans. It is free and significantly better than SSMS. It can open .xml and .sqlplan files with the plan.

Upvotes: 3

Related Questions