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

Tuning T-SQL
Tuning T-SQL statements for performance is a primary task for most DBAs. The task is essentially a
contest between a human being and SQL Server's query optimizer. An analogy can be seen in the
world of chess. Numerous chess-playing computer programs have been developed over the years.
Early chess programs were weak. However, the programs got better and began to surpass most
people's ability to play. Yet there are human players who can still beat the best chess programs in
the world.
The world of T-SQL tuning is no different. In the early days of database engines, the query
optimizers were weak. In fact, an early tuning technique was a simple re-arrangement of table
order in a query. By simply reordering the tables, vast improvements could be made. However,
over the years optimizers have gotten better and can now automatically handle situations that pre-
viously had to be handled by a person. Don't make the mistake of expending large amounts of effort
to create a report that torpedoes thousands of write operations. The days of tweaking an existing
query to get large gains in performance are rapidly diminishing, so this type of tuning really isn't
an option anymore.
In the first section, like the two different approaches that machines and humans take to
playing chess, you'll look at the algorithmic approach to playing chess — how to analyze the cost
components of each step in a query execution process. You'll examine in detail the pieces of the
game as the optimizer sees them. In the second section, you'll apply human intelligence that is agile
and can apply abstractive reasoning. You'll discover the things that you can do to avoid expensive
operations, techniques for reducing specific cost components, and how to go about examining the
performance data you'll need to tune your T-SQL for performance.
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