In SQL Server, you can use the SET SHOWPLAN_TEXT statement to return detailed information about how a T-SQL statement is executed.

SHOWPLAN_TEXT returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. 

It’s similar to SHOWPLAN_ALL, except that it returns less details. It’s therefore aimed towards applications that can’t handle the extra details that SHOWPLAN_ALL provides.

You can set SHOWPLAN_TEXT to either ON or OFF.

When SHOWPLAN_TEXT is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).

Example

Here’s an example to demonstrate.

SET SHOWPLAN_TEXT ON;
GO

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
GO

Note that SET SHOWPLAN_TEXT cannot be specified inside a stored procedure, and it must be the only statement in a batch.

Here’s what the result looks like in Azure Data Studio:

And here’s what it looks like in mssql-cli (command line interface):

Commands completed successfully.
+------------+
| StmtText   |
|------------|
| 
SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;            |
+------------+
(1 row affected)
+--------------------------------------------------------------------------------------------------------------------------------------+
| StmtText                                                                                                                             |
|--------------------------------------------------------------------------------------------------------------------------------------|
|   |--Hash Match(Inner Join, HASH:([d].[DogName])=([Expr1003]), RESIDUAL:([Expr1003]=[Test].[dbo].[Dogs].[DogName] as [d].[DogName])) |
|        |--Table Scan(OBJECT:([Test].[dbo].[Dogs] AS [d]))                                                                            |
|        |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(nvarchar(255),[Test].[dbo].[Cats].[CatName] as [c].[CatName],0)))       |
|             |--Clustered Index Scan(OBJECT:([Test].[dbo].[Cats].[PK__Cats__6A1C8AFAB3EF1A8F] AS [c]))                                |
+--------------------------------------------------------------------------------------------------------------------------------------+
(4 rows affected)
Commands completed successfully.

How to Turn it Off

You can turn it off by using SET SHOWPLAN_TEXT OFF.

Once you’ve done this, subsequent statements will execute as normal.

SET SHOWPLAN_TEXT OFF;
GO

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
GO

Result:

Commands completed successfully.
+---------+-----------+---------+-----------+-----------+
| CatId   | CatName   | DogId   | DogName   | GoodDog   |
|---------+-----------+---------+-----------+-----------|
| 2       | Fluffy    | 2       | Fluffy    | 0         |
+---------+-----------+---------+-----------+-----------+
(1 row affected)
Commands completed successfully.

Deprecated or Not?

According to this old MSDN article, SET SHOWPLAN_TEXT is scheduled for deprecation in future showplan versions, and it is recommended that you use SET SHOWPLAN_XML instead.

However, that MSDN article is for SQL Server 2008 R2, and as I write this, it doesn’t seem to have been deprecated in SQL Server 2019. In fact, the current documentation makes no mention of deprecation, and it isn’t included when I run a list of deprecated items in SQL Server.

Either way, it probably pays to bear this in mind before using it in future releases.

Graphical Execution Plan

If you’re using a graphical tool such as SSMS or Azure Data Studio, you might have the option to view the estimated graphical query execution plan for the current query.

• In SSMS you can use Ctrl + L to do this. Or you can click the Display Estimated Execution Plan icon, or right-click in the query window and select Display Estimated Execution Plan.
• In Azure Data Studio, you can click the Explain button above the query window.

You can also use SET SHOWPLAN_XML ON to enable it, and SET SHOWPLAN_XML OFF to disable it.