0

I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query:

SELECT TOP 10 Q.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan( qs.plan_handle ) Q
WHERE Q.dbid = DB_ID( 'Northwind' )

When I click on one of the hyperlinked query_plans (ShowPlanXML=...), SSMS 2014 opens the link in a new text window in XML format. As I understand it, SSMS is supposed to open the link as a graphical execution plan.

Is there any way to configure SSMS to automatically open the ShowPlanXML link as a graphical execution plan without third party software and without updating the version I have of SSMS 2014?

  • 1
    Why do you not want to update SSMS? There are newer versions that are much better (and can still manage SQL Server 2014 just fine). – Aaron Bertrand Jul 31 at 14:06
  • I have definitely come across deficiencies in SSMS. Like trying to connect to Integration Services with any version that does not match. We have 2014 installed with v18 for that exact reason. – Jacob H Jul 31 at 14:48
  • @JacobH Sure, if you need to maintain compatibility with version-specific things like SSIS, but that doesn't mean you have to use that version of SSMS for everything you do (like viewing execution plans). – Aaron Bertrand Jul 31 at 14:49
  • This is true, viewing execution plans (even the live query statistics) seems to be backwards compatible. – Jacob H Jul 31 at 14:52
2

You need to ensure the System Registry contains the correct file-extension-mapping for .sqlplan files.

Open the Registry Editor, navigate to the following key:

Computer\HKEY_CLASSES_ROOT\.SQLPlan

Confirm the (Default) value contains something like:

ssms.SQLPlan.14.0

(the version number might be different for SSMS 2014).

This is a screenshot of Registry Editor showing the key and value:

enter image description here

You'll also need to ensure the following value is set correctly:

enter image description here

When I say "correctly", I mean appropriately for your system. If this approach doesn't work, I'd try simply re-installing SSMS 2014. Of course, I'd actually recommend installing the latest version of SSMS, v18, which is available for free directly from Microsoft.

  • I have the following values: ssms.SQLPlan.12.0 C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.exe" "%1 I appreciate the idea. I read elsewhere that this could be caused if you have 64-bit SSMS. The application fails silently to open the graphical view and then opens the XML in plain text format. – Clark Jul 31 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.