SHOWPLAN_ALL and SHOWPLAN_TEXT

SHOWPLAN_ALL and SHOWPLAN_TEXT

In addition to the graphical query plan available in Query Analyzer, SQL Server provides two versions of SHOWPLAN options to display the execution plan in a text format. These options are SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL . When enabled, SQL Server will return the execution plan that is generated for the query, but no results because the query is not run. This is similar to the pre-7.0 options of SET SHOWPLAN ON and SET NOEXEC ON .

You can turn on the textual output in a couple of ways. You can execute the SET SHOWPLAN_TEXT ON or SET SHOWPLAN_ALL ON command directly in the Query Analyzer window. These commands must be executed in a separate batch by themselves before running a query. The SHOWPLAN_TEXT option can also be enabled by choosing the Options item from the Tools menu, or by choosing the Current Connection Properties item from the Query menu. On the Connection Properties tab within either dialog box, check the "Set showplan_text" option.

TIP

Before enabling SHOWPLAN_TEXT or SHOWPLAN_ALL options, be sure to disable the Show Execution Plan option; otherwise, the showplan options will have no effect. Also, if you turn on the showplan option by executing the command in the query window, make sure the Show Server Trace option in the Query menu is disabled. Otherwise , you will keep getting an annoying Access Denied error message, stating that you need to be a system administrator to proceed.

Typing the following command in a Query Analyzer window will turn on the SHOWPLAN_TEXT option:

 SET SHOWPLAN_TEXT ON  GO 

Setting this option will cause the textual showplan output to be displayed in the results panel, but not execute the query.

The SHOWPLAN_TEXT option displays a textual representation of the query plan. Listing 36.1 shows a sample for a simple inner join query.

Listing 36.1 Example of the SHOWPLAN_TEXT Output

[View full width]

 set showplan_text on go select st.stor_name, ord_date, qty from stores st join sales_noclust s on st.stor_id = s.stor_id where st.stor_id between 'B100' and 'B199' go StmtText ------------------------------------------------------------------------------- select st.stor_name, ord_date, qty from stores st join sales_noclust s on st.stor_id = s.stor_id where st.stor_id between 'B100' and 'B199' (1 row(s) affected) StmtText -------------------------------------------------------------------------------  --Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([bigpubs2000].[dbo].[sales_noclust] AS  [s]))    --Nested Loops(Inner Join, OUTER REFERENCES:([st].[stor_id]))        --Clustered Index Seek(OBJECT:([bigpubs2000].[dbo].[stores].[UPK_storeid] AS  [st]), SEEK:([st].[stor_id] >= 'B100' AND [st].[stor_id] <= 'B199') ORDERED FORWARD)        --Index Seek(OBJECT:([bigpubs2000].[dbo].[sales_noclust].[idx1] AS [s]), SEEK:(  [s].[stor_id]=[st].[stor_id]) ORDERED FORWARD) 

The output is read from right to left, similar to the graphical query plan. Each line represents a physical/logical operator. The text displayed matches the logical and physical operator names displayed in the graphical query plan. If you can read the graphical query plan, you should have no trouble reading the SHOWPLAN_TEXT output.

In the example in Listing 36.1, SQL Server is performing a clustered index seek on the stores table using the UPK_storedid index, and a nonclustered index seek on sales_noclust using index idx1. The inputs are being combined using a nested loop join. Finally, a bookmark lookup is being performed to retrieve the ord_date and qty information from the sales_noclust table.

To turn off the textual showplan output, type the following command:

  SET SHOWPLAN_TEXT OFF   GO  

The SHOWPLAN_ALL option displays the same textual query plan as the SHOWPLAN_TEXT option, but also provides additional columns of output for each row of textual output. These columns provide the same information that is viewable in the graphical showplan ToolTips, and the column headings correspond to the ToolTip items listed in the "Analyzer ToolTips" section earlier in this chapter.

NOTE

The SHOWPLAN_ALL option is actually what provides the information to the Show Estimated Execution Plan option in Query Analyzer. In essence, you are looking at the same information, just without the pretty pictures.