Chapter 41. Speeding up your queries with index covering

 

Alex Kuznetsov

When a nonclustered index contains all the columns from one table needed to satisfy a query, that index is called covering with respect to that query, and that query is covered by that index. In many cases, creating a covering index is a quick and easy way to boost the performance of a query. But covering indexes aren’t free—they use additional storage, slow down most updates, and may cause concurrency issues such as lock contention and deadlocks. This means that speeding up some selects may and usually will cause some modifications to run slower. Usually, you don’t need to optimize standalone queries; in most cases, you should aim to improve the system’s overall productivity. To accomplish that, you need to find some balance to provide a system with selects that run faster and updates that still perform reasonably well. You’ll see several examples demonstrating these concepts in this chapter. The first example will demonstrate how a covering index speeds up selects.

Index covering speeds up selects

Some rules of thumb about indexes aren’t true for covering indexes

Covering indexes usually slow down modifications

One index should cover many queries

One index can both cover queries and implement uniqueness

Summary

About the author