Didactically one of the best summary in the short time. I already know the trick with the calculated column, but just the overview of how to read the plan and correctly estimate the individual operator costs is worth its weight in gold. Thank you very much!
Very good video, thanks. I've taken a step up in my skills and knowledge with all you have taught me. Made a slow query run in 160 ms instead of originals 38 seconds today. The satisfaction 💪😁.
Great to watch and 100% made sense! I wonder, though, if you could have just created the statistics instead of creating a computed column. In-lining the UDF was great too. If that wasn't going to be an option, the original results without the UDF could have been dumped to a #tempTable (parallel plan) then queried out with the UDF call. Great watching someone else work though!
Ben - the cool part about all of my training is that I only use open-source stuff, so you're welcome to download the database and give it a shot to answer your questions. Go for it!
Regarding the statistic comment, I thought this also. When it came to it and I tested it, I realised you can't create statistics in the format col1+col2+col3, only on col,col2,col3 (or in the this case, the single computed column)
Great video...as usual learn lots from your demo(s). Something worth noting for future videos? Even though I tried following along, using SQL Server 2019, no matter what I did, the estimated and actual number of rows were always the same (yes I switched compatibility levels)...any ideas what the problem was? Okay if you didn't glance at the answer already, startup server parameters (i.e. -T2453 and/or -T4199...forget which one (possibly both))!! Took me a while to figure that one out!!
Great video, thanks for sharing, just one note - column names are not the same for the original and updated query At ua-cam.com/video/7hv4vD7Cfy0/v-deo.html they are “PostType, Posts” At ua-cam.com/video/7hv4vD7Cfy0/v-deo.html they are “PostTypeName, Posts” It is a small misprint, but if they use some app to call this procedure, it may break it. And depending on the size, bureaucracy and politics in this organisation it can end up with “This guy broke our app, let’s revert his changes and do reindex more often.” 😊
Would it make a difference replacing "WHERE (u.DownVotes + u.UpVotes + u.Reputation + u.Views) > 1000000" with "WHERE MagicInterestingTotal > 1000000" ?
Download the Stack Overflow database and give 'er a shot. That's why I use these open source tools for training, so you can answer your own questions quickly.
@@svorskemattias yep, just only so much I can teach per video. I talk about the details of things like that in other videos. One of the toughest things about teaching is wanting to teach everything in every single video - but that's why I teach training classes.
Thank you Brent for everything you do for SQL DBA community..You are making a big difference..
Awesome! I love how you thinking out loud while tuning!!!
Loved your presentation, Awesome ☺☺ Thank you
Didactically one of the best summary in the short time.
I already know the trick with the calculated column, but just the overview of how to read the plan and correctly estimate the individual operator costs is worth its weight in gold. Thank you very much!
You're welcome!
This one was intense.. I took a lot of notes. Need a beer now, thanks Brent!
Glad you liked it!
Very good video, thanks. I've taken a step up in my skills and knowledge with all you have taught me. Made a slow query run in 160 ms instead of originals 38 seconds today. The satisfaction 💪😁.
Very interesting the trick of adding a computed column to let the Query Optimizer use his statistic. Good job, Brent!
Thank you for providing the free first responder kit. Awesome!
You bet!
Hey brent this was a great class, the computed column stuff was brilliant. Thanks very much
Glad you enjoyed it!
This is awesome, you don't even know how grateful I'm with you. Thanks Brent!
Great to watch and 100% made sense! I wonder, though, if you could have just created the statistics instead of creating a computed column.
In-lining the UDF was great too. If that wasn't going to be an option, the original results without the UDF could have been dumped to a #tempTable (parallel plan) then queried out with the UDF call.
Great watching someone else work though!
Ben - the cool part about all of my training is that I only use open-source stuff, so you're welcome to download the database and give it a shot to answer your questions. Go for it!
Regarding the statistic comment, I thought this also. When it came to it and I tested it, I realised you can't create statistics in the format col1+col2+col3, only on col,col2,col3 (or in the this case, the single computed column)
Fantastic to see your thought process to tuning queries. Thanks for this.
Thanks for your help to the SQL community!
Loved that video. Fantastic explanation.
Glad you liked it!
Great work Brent!
Awesome! How much do your charge?
You can click on Consulting or Training at the top of my site if you'd like to hire me. Thanks!
Great video...as usual learn lots from your demo(s). Something worth noting for future videos? Even though I tried following along, using SQL Server 2019, no matter what I did, the estimated and actual number of rows were always the same (yes I switched compatibility levels)...any ideas what the problem was? Okay if you didn't glance at the answer already, startup server parameters (i.e. -T2453 and/or -T4199...forget which one (possibly both))!! Took me a while to figure that one out!!
That was awesome!
Thanks, glad you liked it!
Great video, thanks for sharing, just one note - column names are not the same for the original and updated query
At ua-cam.com/video/7hv4vD7Cfy0/v-deo.html they are “PostType, Posts”
At ua-cam.com/video/7hv4vD7Cfy0/v-deo.html they are “PostTypeName, Posts”
It is a small misprint, but if they use some app to call this procedure, it may break it. And depending on the size, bureaucracy and politics in this organisation it can end up with “This guy broke our app, let’s revert his changes and do reindex more often.” 😊
Awsome!
Would it make a difference replacing
"WHERE (u.DownVotes + u.UpVotes + u.Reputation + u.Views) > 1000000"
with
"WHERE MagicInterestingTotal > 1000000"
?
Download the Stack Overflow database and give 'er a shot. That's why I use these open source tools for training, so you can answer your own questions quickly.
Go ahead and download the database to find out. Enjoy!
hi @Brent I'm using StackOverflow2010 (10GB version) and it does not have function dbo.fnGetPostType
just note, it's okay I can create one for myself
Yep, you have to go to the URL in the video to get the code.
ua-cam.com/video/7hv4vD7Cfy0/v-deo.html
Why do you keep saying you don't like us?
Because you've got really bad habits. Get your finger out of your nose. You don't know where that finger's been.
@@BrentOzarUnlimited smh
Your computed column... it could cause unions or other set operations to fail somewhere else
@@svorskemattias yep, just only so much I can teach per video. I talk about the details of things like that in other videos. One of the toughest things about teaching is wanting to teach everything in every single video - but that's why I teach training classes.