Indexes Helping Indexes

This post isn’t going to solve any problems for you

It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn’t believe it at first, so I thought I’d share with the class. Blog. You, Your Name Here. Hiya!

So there I was, creating some indexes. Since I was in demo mode, I had execution plans turned on. I’m actually terrible about remembering to turn them off. There have been times when I’ve run loops to create some REALLY big tables, and left them turned on. SSMS crashed pretty quickly. Apparently generating execution plans isn’t free! Keep that in mind when you’re timing/tuning queries.

Since they were there, I took a look at them. They were basically what I expected. The only way to index data is to read it and sort it and blah blah blah. But there was a little something extra!

Cycle of life.
Cycle of life.

Monkey’s uncle

Well would you look at that? Missing index requests. While creating indexes. If that isn’t the ultimate cry for help, I don’t know what is. The first time I created them, it took 1:07. Which isn’t even bad for five indexes on a table the size of Posts in Stack Overflow.

But I figured, hey, science. Let’s see how much an index can help when creating indexes, and I created a fairly ridiculous index based on all the missing index requests for the five I’m making.

So it’s one key column, and five includes. SQL doesn’t care about the order of includes. They’re not sorted anywhere, they just hang out at the leaf level of the index and make the optimizer happy when it doesn’t have to do a million key lookups back to the clustered index.

Better than fingers.
Better than fingers.

Then I went back and created my indexes again. It took 44 seconds this time. That’s an exciting drop of, like… 16 seconds plus 7 seconds. Math math math. 23 seconds! It only took about 66% as long as before.

So not only was there a decent drop in time, but SQL did this crazy index seek on my god awful one key column and seven include index.

I think it’s pretty neat that SQL is smart enough to do this, and not just strangle your clustered index every time it creates a new index. I mean, it still may, if you don’t have any other helpful indexes, but that’s to be expected.

Just in case you don’t believe me, here are the execution plans from after I added my silly index. Of course, you probably shouldn’t go out and create indexes to help you create indexes to help you create indexes… You get my drift. It’s not worth the time and effort.

My point here is that it’s nice that SQL will naturally use appropriate indexes during index creation.

Sorting is horrible.
Sorting is horrible.

What did we learn?

SQL cares! Not about you, really. But it cares about resources. It will take all sorts of stuff into account when it needs to fetch you some data. If it can use smaller structures and hog up less resources, it will do that.

Thanks for reading!

Brent says: SQL Server says: “Creating this index sure would be faster if I had an index, man.”

Previous Post
Psst… Hey buddy, you want some E-Discovery?
Next Post
Happy Fifth Birthday to Us, 50% Off for You.

20 Comments. Leave new

  • James Chorlton
    March 30, 2016 9:16 am

    Is it bad that I find this immensely interesting? Need to add a new index to a large table in production? Is it taking longer than the maintenance window? Perhaps if you added another index to help your index and then dropped that index afterwards it would be faster…. what if there was an index that made that index index the index faster? Sods law index three would be sped up by index one; does sql server support recursive index creation when creating indexes for indexes?

    >> I will leave now.

  • So the thing that has always confused me about creating indexes is why creating the index is quicker then running the query you needed it for. It would seem to me that to create the index is essentially had to do the same thing as was needed in your query

  • Finagle’s Law: In order to effectively study a subject, you should understand it thoroughly before you start. Sounds like that applies to indexes in SQL Server!

  • Ervin Steckl
    March 31, 2016 3:28 am

    That’s cool. For large, non-repeating maintenance this is useful to know, it may decrease the time needed to finish.
    Like it.

  • This is not directly related but often an Index Re-Organize will complete much more quickly if you update Statistics (full scan) just prior to the Alter Index ReOrg

  • Quick question. Was the PostTypeID the key column in the “helper” index because of the WHERE clause of the first indexes?? Thanks!

  • Stephen Schissler
    March 31, 2016 10:31 am

    Would love to see an index creation syntax that would allow you to define multiple indexes on a table at once. Where indexes would be created in parallel and only read through the table once, rather than each time per index creation. Seems like that would be a nice performance improvement.

  • Sounds a bit like:

    “If we had some ham, we could have ham and eggs, if we had some eggs.”

  • If index creation needs to read the data for the index, why would the query plan for the read NOT be optimized? Makes sense to me that it would go through the optimizer.

  • Since nobody else has asked it. I’ll ask the obvious question. How long did your helper index take to create? If it was more then the 23 seconds of help I would not say it was a huge help. If it was still less then it could be seen as an effective part of a maintenance plan especially on transitory data with large reload datasets.

    • Erik Darling
      March 31, 2016 3:00 pm

      Only a few seconds, but that’s not surprising considering the type of column in the key, small number of includes, and most if not all of the pages already being in memory.


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.