SQL Server™ 2005 Bible

Part VI. Optimization Strategies

In This Part

• Chapter 49

• Measuring Performance

• Chapter 50

• Query Analysis and Index Tuning

• Chapter 51

• Managing Transactions, Locking, and Blocking

• Chapter 52

• Providing High Availability

• Chapter 53

• Scaling Very Large Databases

• Chapter 54

• Designing High-Performance Data Access Providers

This book opened with the Information Architecture Principle, which stated that information must be made readily available. Optimization theory explains the dependencies between various optimization techniques. This final part puts optimization theory into action with practical optimization strategies.

Schema design—The first layer of optimization theory is the schema, discussed in Chapters 1, 2 and 17. A clean schema that avoids over complexity enables better queries.

Set-based queries—Layer two makes the most use of the Query Optimizer. Part II devotes 10 chapters to writing set-based queries, and Chapter 20, "Kill the Cursor!" takes iterative programming head on.

Indexing—Indexes are the high-performance bridge between the schema and queries, so they depend on the schema design and set-based queries to be effective. Chapter 50 details analyzing query execution plans and tuning indexes.

Concurrency—Locking and blocking can be a cascading performance nightmare, but excellence in schema design, queries, and indexing yields short transaction durations and enables fine-tuning transactions and locks. Concurrency tuning is covered in Chapter 51, "Managing Transactions, Locking, and Blocking." ...