Free Fundamentals of Index Tuning Week: Part 3, Indexing for Joins

Monday, we covered Part 1: Indexing for the WHERE Clause, and yesterday we covered ORDER BY. Today, let’s tackle joins. Make sure to keep up – on April 1, these posts will magically disappear, no foolin’.

Lecture (31 min)

When you join multiple tables together like this, and you’ve got multiple indexes, how does SQL Server choose which index to use? Would it use an index on Comments.UserId, or one on Comments.Score? Or will it use both, since it needs to both filter and sort?

We’ll show how selectivity influences the engine’s choice of indexes (just like it did for WHERE and ORDER BY), and why you might start out indexing foreign keys, but then have to discard those along the way.

Lecture Demo Scripts

If you want to follow along with me during the class, this will make it a little easier. I often go off-script and tweak demos based on attendee questions – this is just my starting point:

Hands-on demo: now it’s your turn.

Take the below queries individually and design the right nonclustered indexes to make them dramatically faster. You’re going to be joining between the small Users table and a few larger tables, so if you haven’t created indexes on the larger tables before, be aware that index creation will take longer depending on your hardware.

In this one, it’s all you right from the start: you’ve learned a process, and you can run with it.

Here are the scripts you’ll work with:

Checking your answers against mine (28m)

Tomorrow we’ll finish the lectures & labs by analyzing Clippy’s index recommendations, and Friday we’ll have a recap.

If you like this, you can keep the learning going with:

Folks who wanna take ’em all can get my Live Class Season Pass or a bundle of the Masters Class Recordings.

Previous Post
Free Fundamentals of Index Tuning Week: Part 2, Indexing for ORDER BY
Next Post
Free Fundamentals of Index Tuning Week: Part 4, Clippy’s Index Recommendations

8 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.