SQL Server™ 2005 Bible

Chapter 50. Query Analysis and Index Tuning

In This Chapter

• Indexing for improving performance

• Interpreting query execution plans

• A database strategy for improving performance

The Information Architecture Principle, detailed in Chapter 1, promotes meeting the performance requirement as a key objective for any data store.

Optimization theory tackles that objective and provides a framework for designing high-performance databases by explaining the dependencies between the layers of optimization.

Indexes are right in the middle of the optimization theory layers. They depend on a well-designed normalized schema, and well-written queries. Those two foundations enable good indexing, but without them, it is difficult to design an indexing strategy or get good results from the indexing.

There's no doubt that indexing is vital to a high-performance database. Just keep indexes in perspective; they depend upon the schema and query layers, and they enable concurrency and high-scalability server tuning.

Query analysis and indexing tuning is an area in which SQL Server 2005 excels. SQL Server exposes copious information about the query execution plan, and the indexes are fast, balanced-tree (b-tree) style indexes that are easy to set up and can be finely tuned or almost left on auto-pilot.

A Holistic Approach to Index Tuning

SQL is a declarative language, meaning that the SQL query describes the question and SQL Server decides how to execute the query. This means that much of the optimization is being ...