How to Think Like the Engine Part 3
Вставка
- Опубліковано 5 вер 2024
- Now that we have two different indexes on the same table, how does SQL Server choose between them when building execution plans? Let's learn how a cost-based query optimizer uses statistics.
I understand the desire to emphasise SQL Server's need to to produce a plan before it starts, and I understand that's why you de-emphasised the viewer's comment about non-sargability. But when you were discussing that comment I thought it was slightly misleading, because you said "it can use the index" - but that's equivocating on the meaning of "can use". The data required by the predicate is certainly in the index, so in that sense it "can be used", and there's more data relevant to that predicate on each page, so we can do less IO So we "can use" the index, and it might even be advantageous in terms of IO (and it is, as you demonstrated).
But at 29:10 where you say "we did the index seek followed by a key lookup" - we didn't do an index seek. We did a scan. And that was due to the non-sargability. If the predicate would otherwise have been selective, that would of course matter. So the commenter wasn't wrong, you were just trying to emphasise a different point.
Brent's voice for the SQL Server engine sounds like Alex Jones.
Thank you!
@1:01 - can we remove the IX_LastAccessDate_Id index and re-create it with the new needed parts ???
Absolutely - but for this class, I'm keeping both because I use them later in the class to demonstrate differences between them.
Shouldnt you be talking about Bind Parameters and how they can help us re-use Execution plans if you're planning on running queries 20 million times, as you were talking about?
I do indeed talk about those in my more advanced Fundamentals of Query Tuning and Fundamentals of Parameter Sniffing classes.
Why is SQL Server a 'He' and not 'It' ? 😜
As I mention earlier in the series, he's stubborn and refuses to ask for directions.
Thank you!