Performance Tuning Quiz - Part 3

In Part 2 we managed to get performance down to very acceptable levels, but is that as far as we can go? If you haven't already, go back and watch Parts 1 & 2, load the files, and give it a try, and then watch this one where we experiment to see if a JOIN hint will change the game enough to matter.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Will Casey on 5/5/2010

great vid, always good to see how other people try to imoprove performance

Douglas Kemp on 5/5/2010

Excellent presentation - good job!

Natalia on 5/5/2010

Great ideas!

Alankar Chakravorty on 5/5/2010

Its cool to know about query hints.

paul on 5/5/2010

I never take the time to put in comments - but here I have to. Each part in this series is great. You can be the best OO developer in the world but if you don't know how to fine tune SQL you can be reduced to useless. I hope to see a part 5.

Tim ODell on 5/5/2010

dose with (nolock) improve performance any?

Tim ODell on 5/5/2010

does with (nolock) improve it more?

R Michael Lacy on 5/5/2010

Thanks, Andy! That was a great series of videos on performance tuning.

Kasey Wheeler on 5/5/2010

Nice video.

Joaquin Ortega on 5/5/2010

Very good, didn't know aobut INNER LOOP and INNER MERGE, this quick takes un to next expertise level

maximaxi on 5/5/2010

Very good article ... simple and clean example demostarte the difference.

Al on 5/5/2010

Good video!! Worth the watch

Munna Bhai on 5/5/2010

Andy, this is a great video. thank you so much for sharing.

Thomas Kelley on 5/5/2010

Thanks for the follow-up video. And I thought the next step after the Merge Join was to index the tables to prevent the extra sort operation?

Don Weigend on 5/6/2010

The MERGE and LOOP join methods are interesting, I will hve to look into those a bit more. Thanks for the tip!

Kevin on 5/6/2010

Very nice, though I would have liked to see why the Loop hint caused more reads. I guess I'll have to check on my own :)

Tim on 5/6/2010

Great series, one suggestion, try using a CTE instead of a sub query if you're not able to change database structure for any reason. Dropped the number of reads from around 447 to 39 on average.

sanjaykhed on 5/7/2010

This very good

Peter Kennedy on 5/10/2010

One result that was not highlighted with the INNER MERGE JOIN example was the impact on CPU & Duration - whilst reads were reduced with a merge join, there was an associated CPU / Duration cost imposed, so depending on what the ultimate goal is... It would also be worth noting the flexibility (or not) of the code with join hints in leveraging changes to undeerlying indexes...

Catto on 5/10/2010

Good Vid

Hansjörg on 5/10/2010

There is a high price for using index hints. Nice to know they are there and how to use them. But only in NOTHING else worked.

Hansjörg on 5/10/2010

got it down to 5 reads. Create an index on Registration on EventId and only include the column RegistrationStatus. This will cause the optimizer to use a hash match. But there is a CPU price for it. So I would go for the a normal covering index on registration with colummns (EventId, RegistrationStatus). This runs with 7 reads and 0 CPU cost.

8A7E4A6C54 on 5/18/2010

Excellent

Mark Holahan on 5/27/2010

Excellent tutorial.

mark brito on 5/29/2010

Merge and Loop join are new to me!

Kent on 6/6/2010

Another great presentaion on Performance Tunning!

Gail VanderKolk on 9/15/2010

Although inner loop joins and inner merge joins were described, they weren't defined to understand why they improve performance.

Bhushan on 7/9/2011

Hey ... I did not anything about LOOP and MERGE keywords for the INNER JOIN. So thanks for the video.