Accessing a query plan

To access estimated plans, which are a direct result of the optimization process, we can use either T-SQL commands or graphical tools. For the examples shown in this chapter, we use SQL Server Management Studio (SSMS).

Note

For most users, query plans in text format are harder to read and analyze; therefore, we will use graphical query plan examples throughout the book.

The SET command options SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML provide text-based information on query plans with different degrees of detail. Using any of these commands means the SQL Database Engine will not execute the T-SQL statements but show the query plan as produced by the Query Optimizer.

Take an example of a query that can be executed in the scope of the AdventureWorks sample database:

SELECT pp.FirstName, pp.LastName, pa.AddressLine1, pa.City, pa.PostalCode
FROM Person.Address AS pa
INNER JOIN Person.BusinessEntityAddress AS pbea ON pa.AddressID = pbea.AddressID...