The JOIN query hint applies to all joins within the query. While this is similar to the join hint that can be specified for an individual join between a pair of tables within a large more complex query, the query hint applies to all joins within the query, whereas the join hint applies only to the pair of tables in the join with which it is associated.
NOTE:
Before using any query hints, run a web search for the latest information on issues with query hints. Try searching on the keywords “SQL Server Query Hints” and look specifically for anything by Craig Freedman, who has written several great blog entries on some of the issues you can encounter when using query hints.
To see how this works, here is an example query using the AdventureWorks2012 database that joins three tables.
NOTE:
You can run the following example against the AdventureWorks2012 database.
The first example shows the basic query with no join hints.
use AdventureWorks2012 go set statistics profile on go select p.title, p.firstname, p.middlename, p.lastname , a.addressline1, a.addressline2, a.city, a.postalcode from person.person as p inner join person.businessentityaddress as b on p.businessentityid = b.businessentityid inner join person.address as a on b.addressid = a.addressid go set statistics profile off go
This returns two result sets. The first is the output from the query, and returns 18,798 rows; the second result set is the additional output after enabling the set statistics profile option. One interesting piece of information in the statistics profile output is the totalsubtreecost column. To see the cost for the entire query, look at the top row. On my test machine, this query is costed at 4.649578.
The following shows just the PhysicalOpcolumn from the statistics profile output, which displays the operator used for each step of the plan:
PHYSICALOP NULL Merge Join Clustered Index Scan Sort Merge Join Clustered Index Scan Index Scan
The next example shows the same query but illustrates the use of a table hint. In this example the join hint applies only to the join between person.person and person.businessentity:
use AdventureWorks2012 go set statistics profile on go select p.title, p.firstname, p.middlename, p.lastname , a.addressline1, a.addressline2, a.city, a.postalcode from person.person as p inner loop join person.businessentityaddress as b on p.businessentityid = b.businessentityid inner join person.address as a on b.addressid = a.addressid go set statistics profile off go
The totalsubtree cost for this option is 8.155532, which is quite a bit higher than the plan that SQL chose, and indicates that our meddling with the optimization process has had a negative impact on performance.
The PhysicalOp column of the statistics profile output is shown next. This indicates that the entire order of the query has been dramatically changed; the merge joins have been replaced with a loop join as requested, but this forced the Query Optimizer to use a hash matchjoin for the other join. You can also see that the Optimizer chose to use a parallel plan, and even this has not reduced the cost:
PhysicalOp NULL Parallelism Hash Match Parallelism Nested Loops Clustered Index Scan Clustered Index Seek Parallelism Index Scan
The final example shows the use of a JOIN query hint. Using this forces both joins within the query to use the join type specified:
use AdventureWorks2012 go set statistics profile on go select p.title, p.firstname, p.middlename, p.lastname , a.addressline1, a.addressline2, a.city, a.postalcode from person.person as p inner join person.businessentityaddress as b on p.businessentityid = b.businessentityid inner join person.address as a on b.addressid = a.addressid option (hash join ) go set statistics profile off go
The total subtreecost for this plan is 5.097726. This is better than the previous option but still worse than the plan chosen by SQL Server.
The PhysicalOp column of the following statistics profile output indicates that both joins are now hash joins:
PhysicalOp NULL Parallelism Hash Match Parallelism Hash Match Parallelism Index Scan Parallelism Clustered Index Scan Parallelism Index Scan
Using a query hint can cause both compile-time and runtime issues. The compile-time issues are likely to happen when SQL Server is unable to create a plan due to the query hint. Runtime issues are likely to occur when the data has changed enough that the Query Optimizer needs to create a new plan using a different join strategy but it is locked into using the joins defined in the query hint.
[…] [Loop| Merge | Hash] JOIN […]