Microsoft SQL Server includes an intelligent cost-based query optimizer which, given an ad-hoc query, can quickly determine the best access method for retrieving the data, including the order in which to join tables and whether or not to use indexes that might be on those tables. Because SQL Server uses a cost-based query optimizer, you do not have to determine the most efficient way of structuring the query to get optimal performance ¾ instead, the optimizer looks at all possible join orders, and the cost of using each index, and picks the plan with the lowest estimated cost in terms of page I/Os.
Detailed information on the final access method that the optimizer chooses can be displayed by executing the SET SHOWPLAN ON statement, which shows each step that the optimizer uses in joining tables and which, if any, indexes it chooses to be the least-cost method of accessing the data. This can be beneficial when analyzing certain queries to determine if the indexes that have been defined on a table are actually being considered by the optimizer as useful in getting to the data.
This chapter defines and explains each of the output messages from SHOWPLAN and gives example queries and output. Wherever possible, the queries use the existing tables and indexes, unaltered, from the SQL Server pubs sample database.
Note ISQL/w and the Query window in SQL Enterprise Manager present graphical views of the SHOWPLAN output. The information provided in this chapter is for users who want to interpret the raw SHOWPLAN output.