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!
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.
|
1 2 |
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.”
20 Comments. Leave new
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.
THE INDEX IS COMING FROM INSIDE THE INDEX
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
Thanks Tim for your comment, you inspired me to write a blog post. Please see http://www.sqldoubleg.com/2016/03/31/index-creation-vs-ssms-query/ to find the answer to your question.
Pd. Sorry Erik, I didn’t mean to make spam in your post 🙂
No problem! Good follow up!
Thanks!
Cool thanks for the info
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!
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
POIDH
Quick question. Was the PostTypeID the key column in the “helper” index because of the WHERE clause of the first indexes?? Thanks!
Yessir.
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.
Have you taken a look at the inline index creation syntax?
https://msdn.microsoft.com/en-us/library/ms174979(v=sql.130).aspx
I think table creation is the only time this is feasible. SQL would be potentially be juggling a lot of data at once if you were allowed to do that on established tables. Think about DATE ASC vs DATE DESC, and things like that. Filter definitions. BE NICE TO YOUR SQL!
Sounds a bit like:
“If we had some ham, we could have ham and eggs, if we had some eggs.”
If I had some scotch we could go on a date if you were pretty.
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.
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.