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

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 proi le 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 match join 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 i nal example shows the use of a JOIN query hint. Using this forces both joins within the query
to use the join type specii ed:
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.