1042
Part VIII: Performance Tuning and Optimization
First, the Parser dissects the T-SQL code, ensures that it is valid code, and generates a Parse
Tree — a logical representation of the query.Next, the Algebrizer attempts to simplify any arguments, resolves any object names and
aliases, identifi es any data type conversions required, and binds any aggregate functions
(group by, count(*)). The result is a Query Processor Tree, which is a corrected version of
the Parse Tree ready for the Query Optimizer.Delayed Name Resolution means that SQL Server allows a stored procedure to be created even
if the objects it references don’t yet exist. The idea is that the object might be created by
the time the code is executed. Objects aren’t physically checked until the Algebrizer checks
for them.Best Practice
Delayed name resolution is one more reason why a simple parse check is insuffi cient when develop-
ing SQL code. Unit testing against sample data that exercises every use case, including dynamically
creating objects, is the only way to fully test any SQL code.The Query Optimizer will apply rules at three different optimization phases and evaluates
several possible execution plans to satisfy the statement and evaluate their effi ciency.
During these phases many different types of rules identify potential plans, such as join
ordering and physical join choice. At each phase of optimization, a cost fi gure evaluates
to determine if the query plan is “fast enough.” If it is, optimization ends, and the query
is submitted to the relational engine. The cost fi gure isn’t a value that correlates to actual
execution time but a relative cost of CPU and IO used to choose the best operation for
the given server. The cost per operation considers the amount of data involved, available
indexes, and the statistics to determine the best method for accessing the data.At the end of the day, the job of the query optimizer is to determine the amount of data
required by and produced by each operation and then chooses the best operations for that
amount of data.After the query execution plan generates, SQL Server stores it in the Plan Cache — a por-
tion of memory reserved for query plans — and informs the Query Processor that the plan
is ready to be executed.Chapter 44, “Interpreting Query Execution Plans,” has more details on reading and viewing the plans
and interpreting the query operations within the plans.c46.indd 1042c46.indd 1042 7/31/2012 10:22:58 AM7/31/2012 10:22:58 AM
http://www.it-ebooks.info