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

You can specify which physical operator (Loop, Merge, or Hash) should be used in your query.
For example:
--use
--OPTION(LOOP JOIN) for Nested Loop Join
--OPTION(HASH JOIN)for HashJoin
--OPTION(MERGEJOIN) for Merge Join
SELECT
sh.*
FROM
SalesOrdHeaderDemo AS sh
JOIN
SalesOrdDetailDemo AS sd
ON
sh.SalesOrderID=sd.SalesOrderID
WHERE
sh.SalesOrderID=43659
OPTION(HASH JOIN)
A word of caution, worth mentioning here, is that SQL Server 2012's optimizer
is very smart and it always makes a good choice for your query, so it is better
to let the optimizer do its work. Query hint OPTION should be used as a
last resort by expert database developer or administrator who knows what
actually is going to be done with the query hint. You can use OPTION in your
development environment to check the effect of different joins while working
on performance tuning, but it is not recommended on the production server. If
UNION is involved in the query, OPTION will go with the last query only.
Finding table/index scans in execution plan
and fixing them
In most cases, especially while working with small amount of data from big tables, table scan/
index scan should not be the desired way to go for. It becomes mandatory to find and resolve it
in order to improve the performance, because scanning process goes through each and every
row available in table/index, looks for match with the criteria provided, and returns the result
set. This is really a time and resource consuming, heavy process. While working on performance
tuning, people are afraid of several major bottleneck issues, mentioned as follows:
F CPU
F Network
F Disk I/O