How to Use Multiple Hints Together for a Query? – Interview Question of the Week #241

Question: How do you use multiple query hints together? Put compatible hints in one OPTION clause, separated by commas.

A health-check client wanted a join algorithm hint and a join-order hint in the same query. This WideWorldImporters example shows the progression from no hints to one hint, then two.

USE WideWorldImporters;
GO
SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines AS il
INNER JOIN Sales.Invoices AS i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003;

SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines AS il
INNER JOIN Sales.Invoices AS i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003
OPTION (MERGE JOIN);

SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines AS il
INNER JOIN Sales.Invoices AS i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003
OPTION (MERGE JOIN, FORCE ORDER);

MERGE JOIN restricts join algorithm choices to merge joins. FORCE ORDER preserves the join order written in the query during optimization, while still allowing implementation details such as role reversal. The keyword is OPTION, not OPTIONS. Don’t put each hint in its own OPTION clause.

Which unhinted plan you see here depends on the installed indexes, version, compatibility level and statistics. If you see an Adaptive Join, remember that it chooses between Hash Join and Nested Loops at runtime; a merge join is not one of its choices. A large visual change is not guaranteed merely by adding the second hint.

Compare actual plans and measurements if you are investigating a real problem. A compatible syntax combination can still push the optimizer into a slower plan; conflicting hints or constraints can prevent it from producing a plan at all.

I prefer to understand the query, indexes and estimates before using a hint. A hint can be a useful, measured intervention, but it’s not a substitute for that diagnosis.

A second hint is not a second OPTION clause, it is one more item after a comma, and every hint takes a choice away from the optimizer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.