Query Design Analysis - SQL Server Query Performance Tuning - page 359

Since it is usually difficult to outsmart the optimizer, the usual recommendation is to avoid optimizer hints.
Generally, it is beneficial to let the optimizer determine a cost-effective processing strategy based on the data
distribution statistics, indexes, and other factors. Forcing the optimizer (with hints) to use a specific processing
strategy hurts performance more often than not, as shown in the following examples for these hints:

•

JOIN hint

•

INDEX hint

JOIN Hint
As explained in Chapter 6, the optimizer dynamically determines a cost-effective JOIN strategy between two data
sets based on the table/index structure and data. Table 18-2 presents a summary of the JOIN types supported by
SQL Server 2012 for easy reference.

Table 18-2. JOIN Types Supported by SQL Server 2014
JOIN Type

Index on Joining Columns Usual Size of Joining Tables Presorted JOIN Clause

Nested loop Inner table a must Small Optional

Outer table preferable

Merge Both tables a must Large Yes

Optimal condition: clustered
or covering index on both

Hash Inner table not indexed Any No

Optimal condition: Inner table
large, outer table small

■ Note the outer table is usually the smaller of the two joining tables.

You can instruct SQL Server to use a specific JOIN type by using the JOIN hints in Table 18-3 .

Table 18-3. JOIN Hints

JOIN Type JOIN Hint

LOOP JOIN Nested loop

MERGE JOIN

Merge

HASH JOIN Hash