SQL: Adding many single column SQL Server indexes is usually counterproductive

I’ve just finished delivering a bunch of presentations across New Zealand, and one of the sessions that I’ve delivered in several places is Things I Wish Developers Knew About SQL Server. In that session, I mentioned briefly that most single column SQL Server indexes that people create are at best pointless, and at worst counterproductive.

I often see people making a poor attempt at indexing, and when they don’t know what they need to do, they often add individual indexes on many columns in their tables. This isn’t a good idea.

After those sessions I had a few emails from people puzzled about my comments. In particular, one had thought he’d heard that if you had a whole bunch of indexes like that, that SQL Server would mix and match what it needs out of those indexes.

However, if you look at the usage statistics for those indexes, chances are they are never seeked, looked-up, or scanned. They are only updated (ie: causing work to need to be done for no good outcome).

There are times when SQL Server will decide to read data from more than one index on a table but they aren’t common situations. Even when it does this, it’s rarely a particularly selective (ie: desirable) outcome. Yes, I can construct queries where it would do that, but they aren’t common queries.

SQL Server will at times read an entire index (ie: an index scan). Usually when it does this, it’s because it wants the value from every row, and the index happened to contain the required column. It’s way less work to read the whole index than it is to read the whole table.

Rather than a large number of single column indexes, what you generally need is a set of indexes that cover the most important queries. You can’t cover every query but you can do the most important ones.

Another key skill is being able to merge multiple indexes into a single index, without adversely affecting performance much. We call this, along with removing duplicate and subset indexes, “rationalizing” your indexes.

If you’d like to learn more about how to do this work, we have just released a new online on-demand course:

Designing Effective Indexes for SQL Server

We think it’s great value normally but until the end of September, it’s offered at a 30% discount.

If you need to find out which queries are causing you issues, and that you should focus on, we also have a new free online on-demand course:

4 Steps to Faster SQL Server Applications

I’d encourage you to take a look at them. You’ll find these courses, and our other upcoming courses at https://training.sqldownunder.com .

2018-08-27