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!
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.
CREATE NONCLUSTERED INDEX [ix_helper] ON dbo.[Posts] ([PostTypeId])
INCLUDE ([Id], [OwnerUserId], [AnswerCount], [CommentCount], [FavoriteCount], [Score], [ViewCount])
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.
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.
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.”