Microsoft® SQL Server 2012 Unleashed

Large Complex Queries

For complex queries with a large number of tables and join conditions, the number of possible execution plans can be enormous. The full optimization phase of the Query Optimizer has a time limit to restrict how long it spends analyzing all the possible query plans. There is no known general and effective shortcut to arrive at the optimal plan. To deal with such a large selection of plans, SQL Server 2012 implements a number of heuristics to deal with very large queries and attempt to come up with an efficient query plan within the time available. When it is not possible to analyze the entire set of plan alternatives and the heuristics are applied, it is not uncommon to encounter suboptimal query plans being chosen.

When ...