How to view execution plans in SQL Server on Linux

4.6k views Asked by At

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?

2

There are 2 answers

4
TheGameiswar On BEST ANSWER

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

enter image description here

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

enter image description here

To view estimated execution plan :

Just press the ICON shown below

enter image description here

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
enter image description here 4.Right click actual query plan shortcut and say add key binding with a key of your choice(for me it is CTRL+M

enter image description here

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;

enter image description here

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

Below is a screenshot of above XML

enter image description here

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

enter image description here

0
Arun Prasanth On
SET STATISTICS XML ON;

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