Microsoft® SQL Server™ offers three ways to present information on how it navigates tables and uses indexes to access the data for a query:
In SQL Server Query Analyzer, click Query and select Display Execution Plan. After executing a query, you can select the Execution Plan tab to see a graphical representation of execution plan output. For more information, see Graphically Displaying the Execution Plan Using SQL Server Query Analyzer.
After this statement is executed, SQL Server returns the execution plan information for each query. For more information, see SET SHOWPLAN_TEXT.
This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a concise format. For more information, see SET SHOWPLAN_ALL.
When you display the execution plan, the statements you submit to the server are not executed; instead, SQL Server analyzes the query and displays how the statements would have been executed as a series of operators.
Note Because statements are not executed when the execution plan is displayed, Transact-SQL operations such as creating a table do not cause the table to be created. Therefore, subsequent operations involving the table return errors because the table does not exist.
The best execution plan used by the query engine for individual data manipulation language (DML) and Transact-SQL statements is displayed, and reveals compile-time information about stored procedures, triggers invoked by a batch, and called stored procedures and triggers invoked to an arbitrary number of calling levels. For example, executing a SELECT statement can show that SQL Server uses a table scan to obtain the data. Alternatively, an index scan may have been used instead if the index was determined to be a faster method of retrieving the data from the table.
The results returned by the SHOWPLAN_TEXT and SHOWPLAN_ALL statements are a tabular representation (rows and columns) of a tree structure. The execution plan tree structure uses one row in the result set for each node in the tree, each node representing a logical or physical operator used to manipulate the data to produce expected results. SQL Server Query Analyzer instead graphically displays each logical and physical operator as an icon. For more information, see Logical and Physical Operators.