Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234

Question: Can an index reduce SELECT performance? Yes. An index can lead to an inferior access plan, and even an index absent from that plan can add costs elsewhere in a busy workload.

This question often gets an immediate answer: indexes slow writes and help reads. That is a useful beginning, but it isn’t the whole answer. I raised the question in my newsletter because many discussions stopped there.

Separate two situations. If the SELECT uses a poor index choice, excessive lookups or bad estimates, the access plan itself can be slow. If the SELECT never uses the extra index, a different explanation is needed. Other sessions still have to maintain that index when they change relevant data. Their extra work, logging and blocking can affect a concurrent reader. The reader does not pay an index-update cost merely by issuing a SELECT.

Don’t attribute a slowdown to an unused index simply because it exists. Compare the same workload, inspect the actual plan and waits, and measure the effect of the index under comparable conditions. A read-only single-session test and a busy mixed workload answer different questions.

The original discussion and demonstration

• An Index Reduces Performance of SELECT Queries, the fuller example and video.
• Three follow-up questions: why create the index, what else improves performance, and how do you identify unused indexes?
• Coffee Mug, Indexes and SELECT Queries. My friend Harvey Powell sent me a mug after the discussion, a more memorable response than most execution plans!

A low usage counter is evidence from its observation period, not permission to drop an index. Consider resets, occasional reports and whether the index enforces a key before testing a removal.

Have you measured an example? The query and test conditions make the discussion useful.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.