Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 240

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.

Cost-Based Optimization
Cost-based optimization is a process of examining a tree of possible permeations, determining the best
plan out of the possibilities, and attaching an estimated cost on each step of a plan. However, for the sake
of speed it is not guaranteed that all the possibilities are examined. The cost model determines possible
predicate results from stored statistics and applies complex algorithms to the results to come up with
an estimated CPU and I/O cost. When you are examining the execution plan, every operator has a cost
associated with it, but don't get hung up thinking that these numbers represent specific resource utiliza-
tion or actual query runtimes. These numbers are the result of row count estimates and internal formulas
for typical disk I/O and sort time per row and other heuristics. The actual runtimes of a query depend
upon many things like caching, other requests on same resources, and locking, among other things.
(See the following sections on minimizing caching effects in your tuning process.) Table 9-1 contains the
important things to remember about cost-based optimization and the effects on your tuning process.

Table 9-1: Cost versus Performance

Cost Model Element Performance Tuning

Use of statistics for indexes and tables. If statistics are not up-to-date, optimizer gets incorrect

row counts and may choose suboptimal joins or
access methods.

Selectivity Formulas to estimate predicate
results.

Statistics are also heavily used here. Provide more
information about relations between tables. For
example, a.A

= b.A AND b.A = c.A AND a.A = c.A.

Formulas to estimate cost per Operator.
Order of existing data and requested data
streams are considered.

Ordering of results can alter join options that
optimizer can use.