SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headache

Ever open a huge query plan to track down a performance issue and instead find yourself overwhelmed by the sheer number of objects on the screen? How do you figure out which part of the plan.

[@drstrangebudgie4052]

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

[@neryanatanov385]

Very helpful. Thank you

[@BalvinderSingh-uh3my]

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.

[@johnulysses3827]

I agree that it's quality content but I wouldn't put any value at all on KZbin stats and comments and dislikes.

[@BalvinderSingh-uh3my]

@@johnulysses3827 Just saw that a late Happy New year to you 🙂

[@andeslam7370]

this is still gold now. thanks

[@Rafian1924]

This is the ultimate performance tuning video. Hats off to you!!

[@alexanderfleming2078]

I cannot say enough great things about this video. Great job

[@lazykitten4356]

THIS IS GOLD!!!!

[@massefromla2554]

I learned a lot from this video , thanks .. big thumbs up 👍

[@mokarem75]

Many thanks for uploading such an informative lecture.

[@jmwinpath]

Very much informative. Great work..

[@akshaybarakoti3643]

Thanks Fidela for uploading it again

[@arnesbeganovic]

This is very useful. Thanks a lot.

[@lazykitten4356]

Can anyone explain to me why he keeps using "cross apply" instead of 'inner join'? there are no tablar functions.

[@liyanxu782]

Thanks for a great video!

[@आरंभ-भ5ल]

Excellent information, Great thanks.

[@monday6740]

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

[@codewiserelizondo]

Many thanks for sharing! is the sql shown available somewhere?

[@jez9999]

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.

[@Maxvm]

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 ?????

[@coachtroop]

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.

[@dineshmanoharan2423]

44:40 Sort

[@evald80]

@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

[@erwinekkel9482]

evald80 the new column was outside the current index. thus the index was no longer optimal.

[@rjcwaring]

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