Chapter 3. Index DMVs
This chapter covers
Indexes are used to improve the performance of data retrieval, to order data, and sometimes to enforce uniqueness. It’s the first of these uses, improving data retrieval, that I’ll focus on in this chapter. We’ll use the DMVs to identify indexes that may be suboptimal or unnecessary, as well as indexes the optimizer would like to use but are missing. All these aspects of index optimality can affect the performance of your SQL queries, sometimes significantly. I’ll provide discussions within each code snippet on index optimization.
After we’ve examined various code snippets relating to index DMVs, I’ll summarize the conflicting requirements of indexes with regard to data retrieval and modification and offer a holistic view of how index usage can be balanced. Let’s begin by examining why indexes are important.
Because tables in production systems may easily contain many millions of rows, reading an entire table’s content without using indexes would be impractical. Without an index, the entire table may need to be read to satisfy any queries run against it. Indexes typically enable queries to run quickly and efficiently.