SQL Server Query Tuning and Optimization

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 ...