Microsoft SQL Server 2014 Unleashed

Choosing Indexes: Query versus Update Performance

I/O is the primary factor in determining query performance. The challenge for a 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 fewer I/Os. Defining useful indexes during the logical and physical data modeling step is crucial. The SQL Server Query Optimizer relies heavily on index key distribution and index density to determine which indexes to use for a query. The Query Optimizer in SQL Server can use multiple indexes in a query (through index intersection) to reduce the I/O required to retrieve information. In the absence of indexes, the Query Optimizer performs a table scan, which ...