Chapter 12: Understanding Query Hints

Query optimization is an inherently complex problem, not only for SQL Server but also for other relational database systems. Even though query optimization research dates back to the early 1970s, challenges in some fundamental areas are still being addressed today. The first major impediment to a query optimizer finding an optimal plan is the fact that, for many queries, it is just not possible to explore the entire search space. An effect known as combinatorial explosion makes this exhaustive enumeration impossible because the number of possible plans grows very rapidly, depending on the number of tables joined in the query. To make searching a manageable process, heuristics are used to limit the search space (that is, the number of possible plans to be considered), as covered in Chapter 3, The Query Optimizer. However, if a query optimizer can’t explore the entire search space, there is no way to prove that you can get an optimal plan...