Professional SQL Server™ 2005 Administration

Chapter 14. Performance Tuning T-SQL

Performance tuning T-SQL is very interesting, but also quite frequently frustrating. It is interesting because there is so much involved in tuning that knowledge of the product's architecture and internals plays a very large role in doing it well. Of course, knowledge alone is not sufficient without the right tools, which you will learn about in this chapter. If you have tuned a query and reduced its runtime, you may have jumped up and down, but sometimes you cannot achieve that result even after losing sleep for many nights, and that's when you get frustrated. In this chapter you will learn how to gather the data for query tuning, the tools for query tuning, the stages a query goes through before execution, and how to analyze the execution plan. It is very important to understand which stages a query passes through before actually being executed by the execution engine, so we'll start with physical query processing.

Physical Query Processing

SQL Server performs two main steps when a query is fired to produce the desired result. As you would guess, the first step is query compilation, which generates the query plan; and the second step is the execution of the query plan. The compilation phase in SQL Server 2005 goes through three steps: parsing, algebrization, and optimization. In SQL Server 2000, there was a normalization phase, which has been replaced with the algebrization piece in SQL Server 2005. The SQL Server team has spent much effort to ...