Datasheet

Chapter 1 ✦ The Information Architecture Principle

A well-written set-based query can perform the entire operation, reading each required page

only once, whereas a cursor-based solution will process each row independently. In tests, a

simple update operation using cursors takes 70 times as long as the same logic implemented

using set-based code.

Indexing

Indexes are the bridge between the query and data. They improve performance by reducing

the number of physical page reads required for a table read operation.

✦ Clustered indexes group rows together so they can be retrieved in one (or a few) physi-

cal page reads, rather than reading from numerous rows scattered throughout the

table.

✦ Indexes enable the query optimizer to seek directly at the data rows, similar to how a

book index can be used to find the correct page, instead of having to scan the entire

table to locate the correct rows. Once the row is determined, the optimizer will per-

form a bookmark lookup to jump to the data page.

Indexing is a key part of the physical schema design and is considered more of an art than a

science, but understanding the database engine’s query optimizer and how the index struc-

tures work, combined with a knowledge of the database’s specific schema and how the

queries will be accessing the data, can make index design more exact.

Index tuning strategies are discussed in Chapter 50, “Query Analysis and Index Tuning.”

Indexes have a downside as well. Although indexes help when reading from the table, they

can adversely affect write performance. When a row is inserted or updated, the indexes must

be also be kept in synch with the data. Therefore, when a table has multiple indexes, writes to

the table will be slower. In other words, there’s a tension between designing indexes for read-

ing versus writing. Because an update or delete operation must locate the affected rows,

write operations do benefit from frugal indexing. The different indexing requirements of read-

ing versus writing is a major difference between transactional databases and databases

designed for reporting or data warehousing.

Partitioning

Partitioning, or spreading the data across multiple disk spindles, is a method of improving

the performance of very large databases (VLDs).

Chapter 53, “Scaling Very Large Databases,” details SQL Server 2005’s partitioning features.

Caching

Caching is the means of pre-fetching data from the physical hard drive so the data is in mem-

ory when required by a database operation. While caching is the job of the database engine,

providing it with enough memory makes a difference.

Cross-

Reference

Cross-

Reference

05_542567 ch01.qxp 9/27/06 9:58 PM Page 17