Key Lookups and Index Creation
Chapters
- 00:00:00 – Introduction
- 00:01:30 – Index Creation and Key Lookups
- 00:04:28 – Query Plan Analysis
- 00:06:45 – Outdated Statistics Impact
- 00:09:16 – Key Lookup During Index Creation
Full Transcript
Erik Darling here with Darling Data… that happen uh when you create indexes like the index create query plan uses a key lookup it happens it’s in the world uh down in the video description you’ll see all sorts of helpful links incredibly helpful links uh you can hire me for consulting you can buy my training at a discount there are coupons attached to that uh you can become a official supporting member of the channel with money if you if you think that this content is maybe worth a few bucks a month uh and of course you can ask me office hours questions for free at least for now anyway so i’m gonna have to start charging tokens for these because i’m doing some serious thinking over i never thought all this thinking would would be would have an attributable attributable metric to it but it turns out i use tokens too weird right uh and of course like subscribe tell a friend all that good stuff uh if you need absolutely free gratis on the house sql server and or postgres uh performance monitoring my free performance monitoring tool does both of them for free i cannot stress how free it is so if you don’t have monitoring or you’re unhappy with that paid tool that costs a ton of money and sucks because of course it does because it’s a paid monitoring tool and look at them all uh you try mine it doesn’t suck i’ll be in seattle november 9th through 11th for past summit west i have a pre-con there where i’ll be teaching you about advanced t-SQL where even if you you know you’re not writing it anymore you might still have to look at it once in a while and you might still have to tell a robot when it’s being an idiot i can help you they’re gonna help you understand when the robots are being idiots that’s that’s my angle on this um someone still has to do it because the robots are still idiots uh sometimes other times pretty okay what times you got to argue with them a lot right uh and i also have a regular session about isolation levels called read committed is cruel by default it is what i hope to be the last time i will half be forced to talk to you about why you should not use read committed sql server’s default isolation level and why you should use a better isolation level because this time you might listen to me i don’t know crazy out there but it is still october we are still october festing and feasting and so let’s let’s do this thing right so uh in sql server management studio i have i have created an index on just creation date and now i am going to expand that index right i’m going uh and i’m going to add a filter to that index i could have added the filter up here would have would have done the same thing but uh it’s a little bit more interesting uh but normally when you create an index uh you know it’s a nonclustered index usually we can create a non-clustered index uh it it involves scanning the clustered index of your table right and scanning the clustered index of your table and sometimes be awful right most most most of the time when you do this it’s going to scan the whole table it’s going to apply a sort operator uh to sort your data into the order of your new nonclustered index and then bam you’ve got an index and your queries start flying everyone’s happy uh some knucklehead is off in the corner wheedling away about fragmentation and and and your inserts are going to be three nanoseconds slower you you can you can continue to ignore them they are useless people but uh if what now if we go and create this index i’ve got query plans turned on all right and that happens pretty quickly uh mostly this happens pretty quickly because okay look two reasons one no no data meets that filter criteria right it’s a year past anything that happens in the stack overflow 2013 database so it technically does not find any rows uh but more importantly um it’s like the data in the votes table is weird like a lot of the creation date columns in stack overflow have like you know uh apart from like the normal dates and times uh they actually have like millisecond as accurate as milliseconds are for a date time uh not in the votes table though the votes table they’re all there it’s it’s kind of weird but um if you look at the query plan for this index we will see that this this index uh did a key lookup right right right there all right we create we did an index seek into our creation date index right we found uh well zero rows to actually do anything with but had we found rows right uh well we would have uh used this key lookup to output put the post id column and add it to our index right and a lot of people don’t look at this stuff and notice this stuff but i noticed this stuff uh for a while i thought that this was a pattern that only happened under read committed snapshot isolation uh but i was wrong it happens under all anyone can do it it’s probably crappier under read committed because the object level shared blocks that you get with uh lookups and under that one but maybe aside of the point but you know well this is like a cool artifact it’s not really what i want to talk about well it is but in a different way so like where this can be kind of awful is when maybe you have out of date stats uh and sql server is like oh i just have to do a few key lookups but nine fraulein uh you end up doing a lot of them right so uh this is this is something that i’ve seen it uh helping indexes tune in helping it clients tune indexes and i wish indexes tune clients that’d be a fun one right i don’t know how that would work anyway uh what i’ve done is i’ve created a table uh called votes stale uh it’s just a copy of the votes table because it’s 53 million rows and it’s good for this sort of thing uh and i’ve pre-updated some of the rows and i’ve created a couple indexes and then we’re gonna we’re gonna widen this index right here this index was just to make this index create a little fast it’s it’s a whole thing stop bothering me and then just to make this a little bit easier to simulate um i have i turned off auto update stats and i updated 20 of the table rows in a loop and uh that brings us to this point in time where uh well we don’t need this we don’t need max.8 in there that’s it’s a bit silly but let’s just say uh some some developer was just like ah hey we need to um now with our query we need to also return this creation date column and you say all right no problem you do some testing and you’re like crap if we had creation date where we don’t use this index anymore we got to widen it right you got to make sure that we keep using this nice narrow index we’re not scanning our clustered index and so uh you start creating this index and uh we should turn on actual execution plans and you realize that creating this index is rather slow and i seem to have closed my sp who is active window so we’re just going to reuse this one execute dbo dot sp underscore who is active at get plans equals one and if we run this it’ll be just in time to miss our other index getting created uh i’ve said this in other videos if you ever need to bully a query into finishing just open a new tab start typing sp who is active in there and it’ll be like hurry up get going right so uh we’ve created this index and um it took 15 almost 15 seconds and you can see that we have once again done a key lookup right uh because sql server’s estimates are off because the stats were out of date so you know when people say why does this do this why does this why does this happen why god why uh well this is this is why right or this is one reason why there are many many reasons why estimates may be off this is this is but one of them so sql server anticipated uh doing 1069 lookups that was the original sort of um seed update that i did just like ah there are some modifications and then i i blew a bunch of other updates in there i was like ah now we’re in trouble um but this is a case where like index creation might take a lot longer because sql server is making maybe a not good choice about uh what would be happening in here right like scanning the clustered index dot 8 53 million rows ah that’ll go pretty quick doing uh all the the lookups and the the rest of it um not not not really as fast right uh so if you’re ever creating maybe widening a filtered index and you’re like why is this taking so long uh look at the execution plan for it there’s a chance that you’re you’re doing a key lookup maybe your stats are out of date and maybe maybe you have a new thing to think about in your life anyway uh i i thought all this was uh incredibly fun and interesting uh i hope you enjoyed yourselves i hope you learned something and uh well this will this will be the this will be the friday video so i will see you next tuesday for office hours all right thank you for watching
Going Further
If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.