How to Think Like the Engine Engine 2019 Part 3

Why does one query get wildly different execution plans? Learn how statistics influence your query plans, discover how to see your own statistics, and understand how stats help build memory grants.

[@plahovivan557]

"Oracle does" was very funny :)

[@RIDDLE0MASTER]

I love those SQL Server voicings XD

[@CodeRealtime]

Thank You Brent :)

[@stpieraf]

Just a small point on this.
WE (People) know by looking at that data that having Displayname and Age in the sort for the first index won't impact it because LastAccessDate is the most selective. The real question is - Does SQL know? Even though we know it won't, SQL still has to check EVERY TIME to make sure it doesn't have to reorder the index based on Display Name and Age. That cost might be small, but doesn't that still play a factor in making SQL do as little work as possible?
Good video though!

[@BrentOzarUnlimited]

You say "SQL still has to check every time" as if it's a hugely expensive operation. The page is already in memory. You're talking about the tiniest fraction of nanoseconds. The answer there is a hard no.

[@stpieraf]

@@BrentOzarUnlimited Isn't that assuming that it's still in memory at all? What if it got flushed? What if it's called thousands of times per minute?
I'm not disagreeing that in this case, in this instance, a few fractions of a nanosecond are not going to be an issue. But as a general rule, surely some consideration should be given as to what the purpose behind the index is for, and how often it will be called. The answer "It Depends", of course, but even a few fractions of a nanosecond is still greater than 0. If you want to eek out every last drop of performance, I don't agree that a "hard no" applies, as it rarely applies to anything in SQL.
Now if we wanted it sorted by display name then age, we'd be having a whole different discussion :)

[@BrentOzarUnlimited]

@@stpieraf when you update a row, it has to be in memory. SQL Server only works on data pages when they're in memory. Whether the field is included or part of the key, you still have to (1) get the page in memory (2) make the change to it (3) log it to the transaction log (4) write the page back down to disk. Those 4 units of work don't change. Thanks for the discussion though!

[@stpieraf]

@@BrentOzarUnlimited Good discussion indeed! Addendum: I probably should have phrased it "what if the table is updated thousands of times per minute", because I know read isn't the concern, just the writes.
Just one more question, and probably requires more in-depth knowledge of how SQL operates that you have and I don't.
Once step 2 occurs (Make the change to it) in memory, does SQL know once it goes to update it, that based on the selectivity of LastAccesseDate that it's going to append that to the end of the index, and it doesn't need to check anything else? In other words, does it go, "Oh look, LastAccessDate is being updated to right now, so it absolutely will be the last record in this index, I can completely ignore the rest of the order by in this index"? If it does, that'd be cool!
Thanks for responding!

[@BrentOzarUnlimited]

@@stpieraf Bingo - SQL Server already has to check whether LastAccessDate is updated (and the other fields in the key) and find the right place to put the row.