0:00:00 intro 0:05:30 how query plans work 0:17:59 top five quick check iterators 0:26:47 lookup 0:34:51 spool 0:44:18 sort 0:51:13 hash match 0:55:45 serial nested loops 1:02:41 scan (seek predicates vs residual predicates) 1:11:28 outro
Best video on performance without question, I was really worried I thought I'd save this video. It was out before this date. Anyway super happy that I found it again, I'm amazed and sadly shocked that I’m the first person to leave a thanks it's truly excellent. Forget Moore’s law if every DBA saw this and understood it massive impact on the world speaking like the nerd that I am.
02:00 That is why, in 2035, the database will be tuning itself : the fixing mechanism is always the same, and is always logical. It could have been fixed by now, but programmers focus now lies on designing nice looking apps for phones that work like s
Can someone explaing me more why we shouldn't take into consideration %cost ? On EDX there is a course about query optimization and one of the first things during the query execution plans introduction is to look at %cost. Where is the catch :D ?????
His argument is 2 fold - one, it's an estimate. You can't possibly know the actual CPU/Processing cost on a given set of data until you actually run it - so it's statistics based. Second, stats often get out of alignment so it's at best a "guess." His argument was that those statistics that are estimated against are based on a machine that is not your own - so it's a best guess made against a guess on how a "comparable" machine would be running at some time in the past when the statistics were gathered in the first place.
@30min is shown how a query that took 1sec, after added an empty column, took 17 sec. How can be possible? this is not clear at all in the video or maybe i missed something
it's because he's selecting * (so the query needs to output that new column) but the new column is not included in the index, which means that SS needs to do an extra lookup in the clustered index - that nested lookup then becomes way more work. Alternatively, it might be that SS decides to do a clustered index scan to get the data instead of the non-clustered index seek that it could do before, when that non-clustered index covered the query. If what I've written sounds like tech-y gibberish, I recommend Brent Ozar's videos on performance tuning and "how to think like sql server" where he breaks this stuff down super-well - man, that guy's a good teacher
This why I'm not a DBA. It takes me a long time to understand why changing a SQL query results in the change of the execution plan that it does, and this guy skips over things way too quickly for me to really grasp.
0:00:00 intro
0:05:30 how query plans work
0:17:59 top five quick check iterators
0:26:47 lookup
0:34:51 spool
0:44:18 sort
0:51:13 hash match
0:55:45 serial nested loops
1:02:41 scan (seek predicates vs residual predicates)
1:11:28 outro
Very helpful. Thank you
Best video on performance without question, I was really worried I thought I'd save this video. It was out before this date. Anyway super happy that I found it again, I'm amazed and sadly shocked that I’m the first person to leave a thanks it's truly excellent. Forget Moore’s law if every DBA saw this and understood it massive impact on the world speaking like the nerd that I am.
I agree that it's quality content but I wouldn't put any value at all on UA-cam stats and comments and dislikes.
This is the ultimate performance tuning video. Hats off to you!!
I cannot say enough great things about this video. Great job
this is still gold now. thanks
I learned a lot from this video , thanks .. big thumbs up 👍
THIS IS GOLD!!!!
Thanks Fidela for uploading it again
This is very useful. Thanks a lot.
Many thanks for uploading such an informative lecture.
Thanks for a great video!
Very much informative. Great work..
Excellent information, Great thanks.
Many thanks for sharing! is the sql shown available somewhere?
02:00 That is why, in 2035, the database will be tuning itself : the fixing mechanism is always the same, and is always logical. It could have been fixed by now, but programmers focus now lies on designing nice looking apps for phones that work like s
Can anyone explain to me why he keeps using "cross apply" instead of 'inner join'? there are no tablar functions.
Can someone explaing me more why we shouldn't take into consideration %cost ? On EDX there is a course about query optimization and one of the first things during the query execution plans introduction is to look at %cost. Where is the catch :D ?????
His argument is 2 fold - one, it's an estimate. You can't possibly know the actual CPU/Processing cost on a given set of data until you actually run it - so it's statistics based. Second, stats often get out of alignment so it's at best a "guess." His argument was that those statistics that are estimated against are based on a machine that is not your own - so it's a best guess made against a guess on how a "comparable" machine would be running at some time in the past when the statistics were gathered in the first place.
@30min is shown how a query that took 1sec, after added an empty column, took 17 sec. How can be possible? this is not clear at all in the video or maybe i missed something
evald80 the new column was outside the current index. thus the index was no longer optimal.
it's because he's selecting * (so the query needs to output that new column) but the new column is not included in the index, which means that SS needs to do an extra lookup in the clustered index - that nested lookup then becomes way more work. Alternatively, it might be that SS decides to do a clustered index scan to get the data instead of the non-clustered index seek that it could do before, when that non-clustered index covered the query.
If what I've written sounds like tech-y gibberish, I recommend Brent Ozar's videos on performance tuning and "how to think like sql server" where he breaks this stuff down super-well - man, that guy's a good teacher
44:40 Sort
This why I'm not a DBA. It takes me a long time to understand why changing a SQL query results in the change of the execution plan that it does, and this guy skips over things way too quickly for me to really grasp.