Tuning with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 116

There are some important parts (operator) that we should refer to in the execution plan in
the order to understand it and to find the pain point. Some of the very important operators
in execution plan, which we are going to cover in this chapter, are as follows:
F Join strategies: There are three physical join operators in SQL Server 2012, which are
as follows:
Hash Join
Merge Join
Nested Loop Join
Each join operator has its own pros and cons, which we are going to discuss in
this chapter.
F Scan and seek are two ways that SQL Server 2012 uses to read the data. Scan looks
at each and every row available in the table/index, whereas seek has address of each
row based on the key field value. So seek directly goes to that data page and fetches
the row if your predicate matches with the key field. This is an essential concept while
working with performance tuning and will be covered in this chapter.
F Key Lookups sometimes become a major performance issue. As in the situation of
Key Lookup, storage engine has to go to clustered index from non-clustered index, in
order to fetch the value of non-key field of non-clustered index. This round-trip always
consumes time.
Understanding Hash, Merge, and Nested
Loop Join strategies
SQL Server uses three physical join operators, listed as follows, to interpret the query
you execute:
F Hash Join
F Merge Join
F Nested Loop Join
None of the physical join operators are the "best" or "worst" for all situations. SQL Server 2012
chooses appropriate operator to perform query in an appropriate way. Join operators are being
used in SQL Server from earlier versions and is still available in SQL Server 2012 without
any change.