Chapter 9. Analyzing indexes
Indexes, as I’ve mentioned in previous chapters, are hugely important to SQL Server. They also represent a complete compromise: every index has the potential to help certain types of query run faster, while slowing other types. It’s extremely, extremely, extremely important that you understand what indexes are, how they work, and what you can do with them. This is also a good time to analyze an existing database to see what indexes it has. Becoming familiar with a database’s indexing strategy is a real key to helping understand any performance issues that may crop up.
Indexes in SQL Server apply to tables, not to entire databases, but when we talk about indexes we tend to think about their impact on overall database application performance. An index is, in a very real sense, a lot like an old-fashioned paper telephone book. Like a phone book, an index is designed to speed up queries, in this case, queries that need to find particular pieces of information, or put those pieces of information into a particular order.
Just like the data in a table, indexes are stored in 8 KB chunks called pages. These pages are stored in the database’s files on disk, and they’re moved into and out of the buffer cache in SQL Server’s memory. SQL Server always reads and writes index pages in 8 KB increments, which becomes a key to understanding index performance.