I liked this video too. Although I already know most of Brent's teachings in this video, I'm still considering buying some of his courses. Brent has become my favorite YT publisher these days...
13:11 What about if I have a lot of String aggregation with XML PATH in SELECT, which columns should be indexeed? The columns I aggregate? Can an Index help with this process?
I love these classes. Thank you so much for publishing so much free content. I wonder if you can answer my question. At what point would you recommend the master classes?
oops, issue, @2:49 you are pointing at an "index scan" and speaking of it as an "index seek." Function employ causes the scan. Lack of a RID (key lookup in this case) improved the results but you are simply scanning a subset of fields instead of doubling back for the output.
@@BrentOzarUnlimited @2:44 "sql server's estimates are still wrong but it doesn't matter as he just was able to seek into this brand new wider index." Granted to your point of query costs, hence why I lead off with oops… I hate to split hairs like this sir but SQL Server IS NOT seeking into the index, it is scanning and, oh by the way, the cause is predicate via function. The correct statement could be "SQL Server is able to SCAN into the new cover index instead of performing a key lookup on the wide table..." Put it this way, I was trying to help clarify so others can properly read the plan and better understand the point you were making. Saving the costs of RID (key lookups) is noble and at the same time I was also pointing out possibly why the costs are inaccurate (there are plenty of other reasons why SQL Server muffs the costs but this one is generally addressable). I tried to do it delicately assuming you would see that… oops, missed the mark there I did. The performance optimization here, and this is a key point novices (and "experts") should understand, SQL Server is scanning due to a predicate issue. That issue can be addressed and the solution may net you better results even with a RID (or key lookup in this example). You likely discuss this point in other videos that I haven’t seen and won't likely… I was just evaluating your production to pass on as potential training sources, I like to know what I am recommending before I recommend it and offer corrections where they are needed in that recommendation when and where needed… your viewers are privy to that correction. Try this and you should see a seek in your example: SELECT Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate between '2014-07-01 00:00' AND '2014-07-31 23:59:59' This, by the way, is a very common issue... People don't generally think of the data being natively compared but how else would SQL Server be able to know what it is looking for? And yes, for the really sharp eyes I picked 23:59:59 and didn't add on the fractions of a second… it's supposed to be a readable example… :)
@@michaelorzo1120 I understand that you've got a concept that you want to teach, and that's great. That is *not* the concept I'm trying to teach here in this class. You should totally take up presenting and start sharing what you've learned. Jump on in, the water's fine! Just a word of caution, though: be carful when you scope out what you're going to cover in a session, and what you're going to leave out. There are always folks who want you to teach everything in every session - and that's just not doable in a limited amount of time.
@@BrentOzarUnlimited Hi Brent, thanks for the great video :) I have to back up Michael though, it sounds like you are talking about an index seek. An index seek is also what I would have expected to be used for this query, rather than the index scan on the slide.
Really appreciate these free sessions, they helped me so much in my optimization task as a junior backend dev with terrible knowledge of SQL Server.
Glad I could help!
I liked this video too. Although I already know most of Brent's teachings in this video, I'm still considering buying some of his courses. Brent has become my favorite YT publisher these days...
Thanks, glad you liked it!
Woah! This is great! I don’t think you have ever formally published this talk as free on UA-cam. Thanks Brent!
Glad you liked it!
Thank you for the class, very insightful :)
Looking forward to the next ones!
13:11 What about if I have a lot of String aggregation with XML PATH in SELECT, which columns should be indexeed? The columns I aggregate? Can an Index help with this process?
I love these classes. Thank you so much for publishing so much free content. I wonder if you can answer my question. At what point would you recommend the master classes?
After you've finished the Fundamentals classes, and you find yourself needing the techniques described in the Mastering class descriptions.
@@BrentOzarUnlimited Thanks for answering :)
Roll on black Friday :)
Does anyone know if these concepts are transferable to postgres?
Great content but lacks the all important link to buy t-shirt :-)
HA!
oops, issue, @2:49 you are pointing at an "index scan" and speaking of it as an "index seek." Function employ causes the scan. Lack of a RID (key lookup in this case) improved the results but you are simply scanning a subset of fields instead of doubling back for the output.
I didn't call it a seek. I specifically called out logical reads, CPU time, and query duration.
@@BrentOzarUnlimited @2:44 "sql server's estimates are still wrong but it doesn't matter as he just was able to seek into this brand new wider index."
Granted to your point of query costs, hence why I lead off with oops…
I hate to split hairs like this sir but SQL Server IS NOT seeking into the index, it is scanning and, oh by the way, the cause is predicate via function. The correct statement could be "SQL Server is able to SCAN into the new cover index instead of performing a key lookup on the wide table..." Put it this way, I was trying to help clarify so others can properly read the plan and better understand the point you were making.
Saving the costs of RID (key lookups) is noble and at the same time I was also pointing out possibly why the costs are inaccurate (there are plenty of other reasons why SQL Server muffs the costs but this one is generally addressable). I tried to do it delicately assuming you would see that… oops, missed the mark there I did.
The performance optimization here, and this is a key point novices (and "experts") should understand, SQL Server is scanning due to a predicate issue. That issue can be addressed and the solution may net you better results even with a RID (or key lookup in this example). You likely discuss this point in other videos that I haven’t seen and won't likely… I was just evaluating your production to pass on as potential training sources, I like to know what I am recommending before I recommend it and offer corrections where they are needed in that recommendation when and where needed… your viewers are privy to that correction.
Try this and you should see a seek in your example:
SELECT Id, DisplayName, Age
FROM dbo.Users
WHERE LastAccessDate between '2014-07-01 00:00' AND '2014-07-31 23:59:59'
This, by the way, is a very common issue... People don't generally think of the data being natively compared but how else would SQL Server be able to know what it is looking for? And yes, for the really sharp eyes I picked 23:59:59 and didn't add on the fractions of a second… it's supposed to be a readable example… :)
@@michaelorzo1120 I understand that you've got a concept that you want to teach, and that's great. That is *not* the concept I'm trying to teach here in this class. You should totally take up presenting and start sharing what you've learned. Jump on in, the water's fine!
Just a word of caution, though: be carful when you scope out what you're going to cover in a session, and what you're going to leave out. There are always folks who want you to teach everything in every session - and that's just not doable in a limited amount of time.
@@BrentOzarUnlimited Hi Brent, thanks for the great video :) I have to back up Michael though, it sounds like you are talking about an index seek. An index seek is also what I would have expected to be used for this query, rather than the index scan on the slide.
@@satori8626 OK, make sure to read my replies to Michael. Thanks!
Classes are great!! Music is way way too loud though
TURN DOWN FOR WHAT