SQL Server – Understanding Table Hints with Examples – 2

This is my plain language companion piece on understanding table hints and the other hints SQL Server offers. I want to answer three simple questions: what a hint is, which kinds exist, and what to check before you use one.

A hint is an instruction you add to a SELECT, INSERT, UPDATE, DELETE or MERGE statement that tells the query optimizer to do something a specific way. Normally the optimizer looks at your indexes and statistics and picks the plan it thinks is cheapest. A hint takes part of that choice away from it.

The three kinds of hints

• Join hints go between the join keywords, as in INNER HASH JOIN. They force how that join is done: LOOP, HASH, MERGE or REMOTE.
• Query hints go in an OPTION clause at the very end of the statement, for example OPTION (RECOMPILE) or OPTION (MAXDOP 1). They apply to the whole statement.
• Table hints go in a WITH clause right after a table name, for example WITH (NOLOCK) or WITH (TABLOCK). They change how that one table is locked or read.

Table hints are the easiest to add, so they are easy to overuse. NOLOCK is the famous example. It means the same as READUNCOMMITTED, so the query can read changes that another transaction has not committed yet and may still roll back. That might be fine for a rough count, and a real problem for anything that has to be exact.

Join hints have a side effect too: a join hint on any two tables also fixes the join order for every table in the query, based on where the ON keywords sit.

My checklist before adding a hint

• Read the actual execution plan first and see why the optimizer chose it.
• Check the basics: are the statistics up to date, and is there an index that supports the query?
• Test with and without the hint on realistic data.
• Leave a comment that explains why the hint is there.
• Review it when the data grows or after an upgrade, because a hint stays fixed while your data changes.

The optimizer is very good at its job, so I treat hints as a last step for experienced people, or as a learning tool on a development box. I also wrote a fuller version of this article that runs the same query with each join hint so you can compare the plans.

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