SQL Server equivalent to MySQL's EXPLAIN

Question

I was reading an SQL tutorial which used the keyword EXPLAIN to see how a query is executed. I tried it in SQL Server 2008 with no success.

How do I get the equivalent result?

Solution 1

I believe that the EXPLAIN keyword is an MySQL concept - the equivalent Microsoft SQL server concept is the execution plan.

The simplest way of getting an execution plan is to turn on the "Show actual execution plan" menu item (in the query menu) in SQL server management studio. Alternatively you can read a more in-depth guide on execution plans here:

• http://www.simple-talk.com/sql/performance/execution-plan-basics/

This article goes into a lot more detail on what execution plans are, how to obtain an execution plan, and the different execution plan formats.

Solution 2

The MySql EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MySQL executes a SELECT statement.

The closest equivalent statement for SQL Server is:

SET SHOWPLAN_ALL (Transact-SQL)
or
SET SHOWPLAN_XML (Transact-SQL)

From a SQL Server Management Studio query window, you could run SET SHOWPLAN_ALL ON or SET SHOWPLAN_XML ON and then your query. At that point It will not return the result set of the query, but the actual execution plan. When you then run SET SHOWPLAN_ALL OFF or SET SHOWPLAN_XML OFF and then run your query, you will then again get a result set.

Solution 3

In SSMS (I got 18.3.1) highlight the query in question and hit CTRL+L

(that does what Tobias mentioned - Query->Display Estimated Query Plan)

Solution 4

Be aware that Microsoft added an EXPLAIN command to TSQL syntax in SQL 2012, however it only applies to Azure SQL Data Warehouse and Parallel Data Warehouse - so not the regular RDBMS product.

It provides an execution plan in XML format, and helpfully shows the parts of the plan that will be distributed across the warehouse nodes.

Source: TSQL EXPLAIN

View more solutions

Comments

[Tobiasopdenbrouw]

For (a lot) more info on execution plans, see here: simple-talk.com/sql/performance/execution-plan-basics. In some versions of SSMS the menu item will be called 'Display Estimated Query Plan'

[Ash Burlaczenko]

Thanks, found it now. @tobia +1, @kragen +1.

[Markus]

It's also available in Oracle: SET AUTOTRACE OFF --ON and then you SQL stmt. You can display the plan with SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY());

[Alkanshel]

"SET SHOWPLAN_ALL ON" -- thank you, why isn't this the accepted answer rather than the one telling me to consult a GUI?

[RichardW11]

This should've been the answer.