Why Indexes Are the Key to Cutting Logical Reads and Speeding Up SQL

Many database systems suffer poor performance because they contain many inefficient SQL statements that generate excessive logical reads – the number of 8 KB data pages accessed during execution. Fewer logical reads mean less memory and CPU usage, resulting in faster query execution.

Creating indexes is the most effective way to cut logical reads, because a well‑designed index can satisfy a query without scanning the full table. The quality of an index is judged by how many logical reads it eliminates.

Choosing Columns for Indexes

Columns that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses are good candidates.

Columns with high selectivity (many distinct values) benefit most.

Columns that are constantly updated or have low selectivity (e.g., boolean flags) often do not improve query speed when indexed.

Clustered vs. Non‑Clustered Indexes

A clustered index determines the physical order of rows in the table, while a non‑clustered index stores a separate structure that points to the data rows. Use a clustered index on columns that are frequently accessed in range scans or that define the natural ordering of the data. Non‑clustered indexes are suitable for lookup queries on other columns.

Example Queries

Consider the following statements: select id from t where num=10 or num=20 versus select id from t where num=10 and the combined form using UNION ALL:

select id from t where num=10
union all
select id from t where num=20

The first query scans rows for both values in a single pass, while the second isolates one value; the UNION ALL version may allow the optimizer to use separate index seeks, potentially reducing logical reads further.

When Indexes Do Not Help

Not every index speeds up a query. Indexes on columns that are not used in filter conditions, or on columns with very low cardinality, may add overhead without reducing logical reads.

Composite Clustered Indexes

Adding many columns to a clustered index does not always improve performance. If a composite index is queried by a subset of its leading columns, the optimizer can still use it efficiently; however, splitting a composite index into separate indexes may degrade performance for certain queries.