How to view execution plans in SQL Server on Linux

Question

I have SQL Server installed on Linux. It was installed from Microsoft's repos as described here: https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

In MySql I used to write EXPLAIN in front of my query to see the execution plan. In SQL Server it doesn't seem to work. But I don't have the studio program installed, only just SQL Server and the sqlcmd tool.

How do I see the execution plan of a query in SQL Server on Linux?

Athodyd

Take a look here: technet.microsoft.com/en-us/library/ms180765(v=sql.105).aspx – Politic

This issue has been fixed.Please check – Adala

Thank you! It works – Athodyd

@Gherman:See updated for one more option which is more easy to work with – Adala

Answer 1

Microsoft released a new tool called SQL Operations studio,this is similar to SSMS,but is available on Windows,Linux,Macos.

location for download:
https://learn.microsoft.com/en-us/sql/sql-operations-studio/download

Below is a screenshot of how it looks like

To view actual execution plan using sqlopsstudio(steps same for all platforms)

• Press CTRL+SHIFT+P
• Type run query with actual execution plan as shown below and select the highlighted, you will get an actual execution plan

To view estimated execution plan :

Just press the ICON shown below

You can also use a keybinding to view actual execution plan .Below are the steps

1.Press CTRL+SHIFT+P
2.Type keyboard shortcuts
3.In the search plan type actual as shown below
4.Right click actual query plan shortcut and say add key binding with a key of your choice(for me it is CTRL+M

Below Part of the answer was written during the time when SQLOPS studio is not available.This can ben helpfull for any one who don't have SQLopsstudio:

Currently viewing execution plan is supported only if you are on Windows,using SSMS or some third party tool like SQLSentry..

There is a feature request being tracked here :Return ShowPlan data as Text or XML with Query Execution

one more option is to connect using VSCODE on linux and set show plan xml as shown in screenshot below..this provides xml of execution plan

SET showplan_xml ON;

you can take that xml and upload it Paste The Plan website and can view plans

Below is a screenshot of above XML

you can also view it in SQLSENTRY plan explorer as well(Windows only) for more indepth analysis

Adala

I copied the xml of plan to this site but it says:The supplied XML is not a valid SQL Server query plan xml. I have the XML but I can't read it – Athodyd

I am working with the author of this .I have supplied the repro as well,Author says it should work and he will get back to me..I will have an update once i hear from him – Adala

This is great news – Athodyd

Any alternative to this to view the explain plan? – Peabody

Answer 2

SET STATISTICS XML ON;

try this command in Azure data studio to get actual execution plan.

Zooplankton