Guru's Guide to SQL Server Architecture and Internals, The

Optimization Limits

The optimizer uses multiple techniques to ensure that the process of comparing the cost of operators and plans does not continue indefinitely or even exorbitantly long. At some point during the optimization process, the optimizer can begin to reach a point of diminishing returns. After all, if the time required by the evaluation process far exceeds the execution time of even the most expensive plans, the optimization process may not be able to save that much processing time overall. In a pathological situation, it may be even more expensive than not optimizing at all. The optimizer is designed to avoid situations like this and, to the extent possible, ensure that there is a genuine performance benefit associated with its cost. ...