Professional SQL Server® 2005 Performance Tuning

9.1. Opening Move: The Optimizer's Gameplan

SQL is a declarative language — at least it is supposed to be, although it isn't always. The idea is that you should use the language constructs to describe what you want. It is the job of the algebrizer and optimizer to figure out how to best get what you want. Performance issues can be created by poorly written T-SQL that cannot be optimized or, conversely, the incorrect optimization of well-written T-SQL. Where the optimizer makes poor decisions, it is helpful to understand the severe restrictions that the optimization componentry has

• Examining alternate plans in a limited to small time frame

• Executing test queries to gather actual meaningful statistics or key performance indicators (KPIs) is not allowed

• Predicting costs based on imperfect cost plan data

• Throwing out edge-case scenarios evaluation

• Ignoring advantageous database schema changes to tables or indexes (it can't make them)

Many turn this into a mortal battle of us against the optimizer. It's not much of a battle, because you actually have the unfair advantage. You have unlimited time to execute alternative queries, capture real KPI metrics, and evaluate edge cases to figure out the best query plans for your purposes. You can rearrange tables and alter index schemes. You should never lose against the optimizer. Tie? That should be the worst case scenario. Learn the ways of the optimizer — not to beat it, but to provide the most efficient communication of your SQL-based intentions. ...