How SQL Server 2012 Uses Indexes

A good understanding of how indexes are used by SQL Server is also important in a good index design. In SQL Server, the Query Optimizer component determines the most cost-effective option to execute a query. The Query Optimizer evaluates a number of query execution plans and selects the execution plan with the lowest cost.

The execution plan selected by the Query Optimizer may or may not make efficient use of indexes, or it may not use indexes at all. The following sections describe how execution plans can use indexes.

Table scan

Indexes are not required by SQL Server to retrieve data requested by a query. In the absence of indexes or if determined to be least cost effective, SQL server scans every row of a table until the query is satisfied. This is known as a table scan. As you may suspect, table scans can bring forth expensive IO operations for large tables. SQL Server has to read every single data page until it finds the data that satisfies the query. A table scan can take from a couple of seconds to several minutes. Some users may even experience time-outs by applications with short response-time thresholds.

Table scans generally occur when there is no clustered indexed available; in other words, when the table is a heap.

Index scan and index seek

An index scan is similar to a table scan in that SQL Server has to read every single data page in the index until it finds the data that satisfies the query. Index scans can be both IO and memory intensive operations.

An index seek on the other hand, is a more efficient way of retrieving data because only data pages and rows that satisfy the query are read. Index seeks result in less data pages read, hence reducing IO and memory consumption.

Depending on how selective a query is, meaning what percentage of the total number of rows in a table is requested, SQL Server Query Optimizer can choose to do an index scan rather than an index

seek. The tipping point at which an index scan is preferred by the SQL Server Query Optimizer is not always a definitive percentage. There are many factors such as parallelism settings, memory availability, and number of rows that contribute in the decision for the more cost-effective option.

Bookmark lookup

It is quite common to see queries that require additional columns than the ones included in a non-clustered index. To retrieve these additional columns, SQL Server needs to retrieve additional data pages to cover all requested columns. Bookmark lookups can become expensive operations when dealing with a large number of rows because more data pages need to be retrieved from disk and loaded into memory.

To avoid excessive bookmark lookup operations, the required columns that need to be covered by the query can be included in the index definition. These types of indexes are known as covering indexes.