Tales From Overindexing: Too Many One Column Indexes

Master Plan

Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti.

Usually, there’s a view involved.

Usually.

Sometimes, there’s just really weird indexing.

Here’s A Thing, Look At It

Promises

This is a query against one table. No views, no functions, no triggers.

How To Think Like An Index

When most people think about the way a query can use indexes, they usually think it’s limited to one, or sometimes two, if you do a key lookup. And most of the time, that’s about what happens.

A key lookup is when SQL Server gives your nonclustered index inadequacy issues. It’s basically saying “I’m busy Saturday, let’s hang out on Tuesday.”

If we have this index, and this query, we’ll get a Key Lookup.

Just Coffee

A Key Lookup is basically a join between the clustered index and nonclustered index. This is one reason why clustered index key columns are in all your nonclustered indexes, and you should be really careful how you choose your clustered index key columns.

That Seek Predicate down the bottom is the join relationship between the clustered and nonclustered indexes, which is the clustered index key column.

For every row that comes out of the nonclustered index, we grab the associated key column value, and join it to the clustered index to produce the columns that aren’t in the nonclustered index.

Look at me. I’m you.

You can’t control what kind of join gets used here (though that would be neat, maybe).

This is the most common, but not the only way that SQL Server can use multiple indexes.

What Happens With A Bunch Of Narrow Indexes?

Like, say, one on almost every column in Posts, but with only one column in each index.

And then a query that kinda sorta does some self-like joins with some specific predicates.

Unnatural Selection

You end up with the query plan I showed you earlier.

Promises

What’s happening here is called index intersection.

It’s pretty rare to see it for a variety of reasons. Most people don’t create a single column index on every eligible column of the table. There’s a special place in hell for people who do that.

By hell, I mean a soup restaurant in a Faraday Cage, with no liquor license, and live comedy.

It’s sort of an expensive process, joining a bunch of nonclustered indexes together, so the optimizer has to really think it’s a worthwhile strategy. You’re talking about reading N number of separate objects, hoping they’re not locked, joining all those objects together, etc.

Remember that you’re probably not joining any of these indexes together on their leading key column. Many of the join types are hash joins, or merge joins that require sorts. Both of those things will drive up the query memory grant.

In all, this query uses 10 nonclustered indexes, and does three separate key lookups back to the clustered index. You could really cut down on the amount of joining, sorting, and hashing, by adding some composite indexes that let our query get all its data from a single source.

Thanks for reading!

Brent says: this blog post stems from a query we saw in the wild leveraging something like 8 different indexes on the same table – even though the table was only specified twice in the FROM clause! I was so impressed by the sheer number of indexes that I said we should blog about it, except that it would probably take days of experimenting to make this happen with the Stack Overflow database. And of course Erik did it that day.

Previous Post
How to Troubleshoot Blocking and Deadlocking with Scripts and Tools
Next Post
Updated First Responder Kit for November 2018: Get Into Sports Dummy

8 Comments. Leave new

  • Excellent article. This came just in time i was looking at similar query/ issue. Thank you.

    Reply
  • Both horrible and beautiful. Nice repro!

    Reply
  • Peter Vandivier
    November 29, 2018 4:46 pm

    How timely! I just linked this on a running PR where I’m begging the devs to cut down on single-column indexes. Left out the color commentary though…

    Reply
  • “It’s pretty rare to see it for a variety of reasons. Most people don’t create a single column index on every eligible column of the table. There’s a special place in hell for people who do that.”

    When I first joined the company I work at, they had a process in the application that a developer could press that would add an index for every column of every table in all databases. That is the first process I removed from the system and I am still cleaning up databases to this day because of it.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}