How would you use query hints to influence the optimizer's behavior when you have determined the default execution plan is genuinely suboptimal?

You can add specific query hints, such as forcing a particular join type, a specific index, or a maximum degree of parallelism, directly in your query using the OPTION clause, though this should be done carefully and only after confirming through testing that the hint genuinely produces better performance, since hints can also prevent the optimizer from adapting to future changes in your data.

SELECT * FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
OPTION (HASH JOIN);