There are few more beneficial things you can do to speed up query performance than to construct usable, efficient indexes. The name of the game with large data banks is I/O—you want to avoid as much of it as you can. Caching helps. Processing power helps. Fast hard drives help. But nothing affects query performance as fundamentally or as profoundly as indexing.

Without a useful index, SQL Server has little choice but to scan the entire table or tables to find the data you need. If you're joining two or more tables, SQL Server may have to scan some of them multiple times to find all the data needed to satisfy the query. Indexes can dramatically speed up the process of finding data as well as the process of joining tables together.

Storage ...

Get Guru's Guide to SQL Server Architecture and Internals, The now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.