Free Fundamentals of Index Tuning Week: Part 4, Clippy’s Index Recommendations

Hey buddy!

Monday, we covered Part 1: Indexing for the WHERE Clause, then we covered ORDER BY, and yesterday we did indexing for joins. Today, let’s cover SQL Server’s built-in index recommendations, aka Clippy. Make sure to keep up – on April 1, these posts will magically disappear.

Lecture (33 min)

As you’ve been working through the labs, you’ve surely caught Clippy’s missing index hints in some of your execution plans. You might have even looked at some of them and thought, “Well, that last one made sense, but this one here – this seems totally different than what Brent is teaching me to make. Who’s right, Brent or Clippy?”

In this module, you’ll learn the rules that the SQL Server engine uses when building missing index recommendations. You’ll understand where he’s coming from, but then also how to take his recommendations and adapt them to work better for real-life queries.


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:

Bonus Demo

Want to see how Clippy builds index recommendations? You can prove it:

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

Now, it’s your turn: take the below queries and run them all at once without looking at ’em. Then, use sp_BlitzIndex to read the missing index recommendations, interpret them, and try to craft better indexes WITHOUT LOOKING AT THE QUERIES. Then, after you’ve made the list of indexes you want to create, go through the queries and try to guess which query triggered which missing index request – and whether your index is a good fit.

This first demo script runs a bunch of different queries against the Stack Overflow database in order to populate your missing index DMVs:

Run that, and it’ll run a bunch of queries 25 times. It’s okay if you start working with the next demo script even while that last one is still running – it’s just going to keep populating more of the same missing index requests.

Now start with:

Checking your answers against Brent’s (10m)

Tomorrow we’ll finish up with 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 3, Indexing for Joins
Next Post
Free Fundamentals of Index Tuning Week: Part 5, Recap

1 Comment. 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.