Sample output with and without an index

Using statistics io to perform a query on a table without an index and the same query on the same table with an index shows how important good indexes can be to query and system performance. Here is a sample query:

select title 
from titles 
where title_id = "T5652"

statistics io without an index

With no index on title_id, statistics io reports these values, using 2K I/O:

Table: titles scan count 1, logical reads:(regular=624 apf=0 total=624), physical reads:(regular=230 apf=394 total=624), apf IOs used=394
Total actual I/O cost for this command: 12480.
Total writes for this command: 0

This output shows that:

The query performed a total of 624 logical I/Os, all regular logical I/Os.
The query performed 624 physical reads. Of these, 230 were regular asynchronous reads, and 394 were asynchronous prefetch reads.
All of the pages read by APF were used by the query.

statistics io with an Index

With a clustered index on title_id, statistics io reports these values for the same query, also using 2K I/O:

Table: titles  scan count 1,  logical reads: (regular=3 apf=0 total=3),
physical reads: (regular=3 apf=0 total=3),  apf IOs used=0 
Total actual I/O cost for this command: 60.
Total writes for this command: 0

The output shows that:

The query performed 3 logical reads.
The query performed 3 physical reads: 2 reads for the index pages and 1 read for the data page.