Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 132

Some of the columns' output from this query is shown in Figure 5-10.
FIGURE 5-10
Another place to look for query plans is in the plan cache itself. When dealing with a lot of queries
on a busy production system, it's often necessary to i nd the query plan for a particular query that's
currently being used. To do this, use the following T-SQL to return either the XML for the plan or
the text of the plan:
Select *
From sys.dm_exec_query_plan(plan_handle)
Select *
From sys.dm_exec_text_query_plan(plan_handle)
Note that you can use two DMFs here: One refers to returning the XML plan; whereas the name of
the other implies it will return the text of the plan, suggesting it would be similar to the showplan _
text output; but, in fact, both return the XML format of the plan. The difference is that the data
type of the query_plan column in one is XML, whereas the data type in the other result set is text.
Query Plan Operators
The Query Optimizer can use many different operators to create your plan. Covering them all is
beyond the scope of this topic, so this section instead focuses on some examples demonstrating the
most common operators you will come across. For a full list of operators, refer to SQL Server Books
Online (SQL BOL). Search for the topic “Showplan Logical and Physical Operators Reference.”
Join Operators
Join operators enable SQL Server to i nd matching rows between two tables. Prior to SQL Server
2005, there was only a single join type, the nested loop join , but since then additional join types
have been added, and SQL Server now provides the three join types described in Table 5-1. These
join types handle rows from two tables; for a self-join, the inputs may be different sets of rows from
the same table.