Optimizer hints have a somewhat unfortunate name. They are not just hints; they are actually directives for the query execution. You can use them with the SELECT statement and the data modification language statements. There are three kinds of hints: table hints, query hints, and join hints.

————————————–

IMPORTANT: Use Optimizer Hints Carefully
When you use a hint, you change the query. SQL Server must execute the query or part of the query always in the same way. The query could be part of an application, so it could be difficult to change it. The data distribution might change over time, and although the hint might have improved the performance in the past, it might harm the performance over time. Use all other means, such as creating appropriate indexes, creating and updating statistics, and even using plan guides before moving to the hints. Use hints as the last resort, and after you use them, validate whether they are still useful from time to time.

——————————————

You specify query hints as part of the OPTION clause of the SELECT, INSERT, UPDATE, DELETE,and MERGE statements. You cannot use query hints in subqueries, only in the outermost query. If multiple queries are involved in the UNION operation, you can specify the OPTION clause only after the last query. You can specify query hints in an INSERT statement except when a SELECT clause is used inside the statement.

The following query hints are supported by SQL Server 2012:
■■ { HASH | ORDER } GROUP
■■ { CONCAT | HASH | MERGE } UNION
■■ { LOOP | MERGE | HASH } JOIN
■■ EXPAND VIEWS
■■ FAST number_rows
■■ FORCE ORDER
■■ IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
■■ KEEP PLAN
■■ KEEPFIXED PLAN
■■ MAXDOP number_of_processors
■■ MAXRECURSION number
■■ OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ ,…n ] )
■■ OPTIMIZE FOR UNKNOWN
■■ PARAMETERIZATION { SIMPLE | FORCED }
■■ RECOMPILE
■■ ROBUST PLAN
■■ USE PLAN N’xml_plan’
■■ TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]…n ] ]

———————————————————

More Info: Query Hints Details
It is out of the scope of this book to go into details for each of the hints. However, you will get familiar with some of them and how to use them through sample code and through the practice for this lesson.

—————————————————————

The following two queries return the same aggregated rowset; the first one allows SQL Server to decide which aggregation technique to use—SQL Server decides to use the hash aggregation—whereas the second one forces the stream aggregation.
— Hash match aggregate
SELECT qty, COUNT(*) AS num
FROM Sales.OrderDetails
GROUP BY qty;
— Forcing stream aggregate
SELECT qty, COUNT(*) AS num
FROM Sales.OrderDetails
GROUP BY qty
OPTION (ORDER GROUP);

Figure 17-21 shows the execution plan for both queries together because they were executed in a batch.

70-461-fm24

Figure 17-21 The plan for a hash aggregation and a forced stream aggregation.
In the second query, SQL Server used the Stream Aggregate operator. However, because this operator expects ordered input, SQL Server also added the Sort operator to the plan. Although the stream aggregation might be faster than the hash aggregation, the second query might be slower because of the additional sort operation.
You can give SQL Server a hint for a single table in a query. Table hints influence locking and the access method for a single table or view only. You can use the table hints in the FROM clause, and introduce them by using the WITH keyword. SQL Server supports the following table hints:
■■ NOEXPAND
■■ INDEX ( index_value [ ,…n ] ) | INDEX = ( index_value )
■■ FORCESEEK [ ( index_value ( index_column_name [ ,… ] ) ) ]
■■ FORCESCAN
■■ FORCESEEK
■■ KEEPIDENTITY
■■ KEEPDEFAULTS
■■ IGNORE_CONSTRAINTS
■■ IGNORE_TRIGGERS
■■ HOLDLOCK
■■ NOLOCK
■■ NOWAIT
■■ PAGLOCK

■■ READCOMMITTED
■■ READCOMMITTEDLOCK
■■ READPAST
■■ READUNCOMMITTED
■■ REPEATABLEREAD
■■ ROWLOCK
■■ SERIALIZABLE
■■ SPATIAL_WINDOW_MAX_CELLS = integer
■■ TABLOCK
■■ TABLOCKX
■■ UPDLOCK
■■ XLOCK

Maybe the most popular optimizer hint is the table hint that forces a specific index usage. The following two queries show an example of leaving it to SQL Server to choose the access method and of forcing usage of a nonclustered index.
— Clustered index scan
SELECT orderid, productid, qty
FROM Sales.OrderDetails
WHERE productid BETWEEN 10 AND 30
ORDER BY productid;
— Forcing a nonclustered index usage
SELECT orderid, productid, qty
FROM Sales.OrderDetails WITH (INDEX(idx_nc_productid))
WHERE productid BETWEEN 10 AND 30
ORDER BY productid;
Figure 17-22 shows the execution plan for this batch.
SQL Server 2012 also supports the following join hints in the FROM clause:
■■ LOOP
■■ HASH
■■ MERGE
■■ REMOTE

70-461-fm25

Figure 17-22 A plan for a scan and a forced nonclustered index seek.

The following two queries return the same result set again. For the first query, the selection of the join algorithm is left to SQL Server—SQL Server decides to use a nested loops

join—and the second query forces a merge join.
— Nested loops join
SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty
FROM Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
WHERE O.orderid < 10250;
— Forced merge join
SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty
FROM Sales.Orders AS O
INNER MERGE JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
WHERE O.orderid < 10250;

Figure 17-23 shows the execution plan for this batch

70-461-fm26

Figure 17-23 A plan for a nested loops and a forced merge join.