Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Choosing Indexes: Query Versus Update Performance

I/O is the primary factor in determining query performance. The challenge for the database designer is to build a physical data model that provides efficient data access. Creating indexes on database tables allows SQL Server to access data with a reduced number of I/Os. Defining useful indexes during the logical and physical data modeling step is crucial. The SQL Server optimizer relies heavily on index key distribution and index density to determine which indexes to use for a query. The optimizer in SQL Server can use multiple indexes in a query (through index intersection) to reduce the number of I/Os to retrieve information. In the absence of indexes, the optimizer performs a table scan, which ...