Accessing a query plan

To access the estimated plans, which are the 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).

The SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML commands provide text-based information on query plans with different degrees of detail. Using any of these commands means SQL Server 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
INNER JOIN Person.Person AS pp ON pbea.BusinessEntityID = pp.BusinessEntityID
WHERE pa.AddressID = 100;

Let's see what each of the following options provides in terms of query plan view:

• SHOWPLAN_TEXT: This option shows all the steps involved in processing the query, including the type of join that was used, the order in which tables are accessed, and the indexes used for each table:

• SHOWPLAN_ALL: This option shows the same estimated plan as SHOWPLAN_TEXT. This option represents a text output tree, but adds details on each of the physical operations that would be executed, such as the estimated size of the result rows, the estimated CPU time, and the total cost estimations. Notice the amount of information produced:

• SHOWPLAN_XML: This option produces the same estimated plan but as an XML output tree:

Because it is generated as a link when used in SSMS, it can be interpreted by SSMS as a graphical estimated plan, and clicking the link will display this graphical plan:

Notice that because it is an estimated plan, the arrows are all the same width. This is because there's no actual data movement between operators given that this plan was not executed. To access all the properties returned by SHOWPLAN_ALL, plus many more, right-click the SELECT operator and click on Properties. We will see these properties in greater detail in the Query plan properties of interest section.

SHOWPLAN_XML is the option is used by SSMS when the Display Estimated Execution Plan (CTRL+L) button is clicked:

To access the actual plans, which are the optimized plans after being executed, we can again use either T-SQL commands or graphical tools. The STATISTICS PROFILE and STATISTICS XML commands provide text-based information on query plans with different degrees of detail. Using either of these commands means SQL Server will execute the T-SQL statements, and generate the actual plan, or query execution plan.

• STATISTICS PROFILE: This option shows the same plan as SHOWPLAN_ALL, incremented with the actual rows and executes to display an actual plan, or a query execution plan:

• STATISTICS XML: This option is the actual plan counterpart of SHOWPLAN_XML. In the following screenshot, we see what appears to be the same output as SHOWPLAN_XML:

However, expanding the XML (or if using SSMS, clicking on the link), we see we have the actual plan, or the query execution plan:

STATISTICS XML is the option used by SSMS when the Display Actual Execution Plan (CTRL+M) button is clicked:

To access all the properties already seen with SHOWPLAN_XML incremented with runtime statistics and warnings (if any), right-click the SELECT operator and click on Properties. Again, we will see these properties in greater detail in the Query plan properties of interest section.