The SQL Server query optimizer normally chooses join strategies and access paths from statistics and cost estimates. Query, join, and table hints can modify that choice, but they can also lock a query into a poor plan.
Types of hints
Query hints appear in OPTION clauses; join hints can influence join methods; table hints can affect table access and locking. Hints should be reviewed as version-sensitive tuning techniques rather than routine SQL style.
Example: a limited experiment
SELECT o.OrderID, c.CustomerName
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
ON o.CustomerID = c.CustomerID
OPTION (RECOMPILE);Why not start with hints
First inspect cardinality estimates, stale statistics, parameter sensitivity, missing or excess indexes, and the actual execution plan. OPTION(RECOMPILE) has its own CPU compilation overhead and is not universally helpful.
Historical plan caches
Old documentation refers to syscacheobjects and SQL Trace. Modern investigations often use Query Store, Extended Events, and appropriate sys.dm_exec_* views. Check platform and permission requirements.
Governance
Record why each hint exists, the benchmark that justified it, the SQL Server version, and a test for removing it after upgrades.
AI disclosure and reuse: This AI-generated article is rewritten from an existing Open Education post (ID 69256). Additional explanations, code examples, and version notes may have been added. You may reuse the newly generated article text with or without attribution. Third-party trademarks and linked works retain their own rights. AI can make mistakes. Check examples, permissions, supported SQL Server versions, and documentation before use in any live system.
