Free Fundamentals of Index Tuning Week: Part 1, Indexing for the WHERE Clause

You’re cooped up at home due to quarantines, and you wanna learn SQL Server. You’re already familiar with my How to Think Like the Engine class. Well, let’s have a free week of training: let’s do my Fundamentals of Index Tuning class right here on the blog. But follow along, because on April 1, they’re going to disappear. Let’s get started!

Each day’s post will have:

  • A video of my lecture
  • The demo scripts I use in the lecture
  • A hands-on lab for you to do
  • A video of me doing the same lab (but don’t watch that lab until you do it first!)

To follow along with the demos, download a Stack Overflow database. I’ll be using the medium-sized 50GB StackOverflow2013 database, but if you’ve got a different one handy (either the smaller 10GB or larger 300+GB one), that’s fine too – the same concepts will still apply. If you’re on a slow desktop/laptop, feel free to use the small 10GB StackOverflow2010 database.

Attach it to any supported version of SQL Server, can be Standard, Enterprise, or Developer. (Sorry, SQL Server 2008 and Express Edition won’t cut it here.) If you want a free development or evaluation version of SQL Server, hit up SQLServerUpdates.com.

Let’s get started!

Lecture: Indexing for the WHERE Clause (33 min)

It sounds easy at first: just look at the list of fields in the WHERE clause, and put them in the index keys. But what order should you put them in?

There’s a common misconception that the most selective (or unique) fields should go first. For example, if you have a column with a million distinct values, and another column with only a hundred, then the million distinct values must be more selective. That’s true – but only if you’re doing an equality search.

We’ll look at equality searches, range searches, and combinations to come up with rules for key ordering.

 

Lecture 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.

Now, it’s your turn: in the Stack Overflow database (any size is fine, including the small 10GB one), take the below queries individually and design the right nonclustered indexes to make them dramatically faster.

As you work through the list:

  • Try different combinations of key and include fields for each query
  • Use index hints to measure the logical reads of each index option
  • Try to keep the number of indexes on the Users table to 5 or less

I’ll get you started by walking you through the beginning of it in this 13-minute video:

And now here are the scripts you’ll be working with:

Think you’ve solved it? Check your answers against mine. (41m)

Next, we’ll dig into indexing for ORDER BY, then Wednesday we’ll cover joins, and then Thursday we’ll analyze Clippy’s index recommendations.

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
Updated First Responder Kit and Consultant Toolkit for February 2020
Next Post
Free Fundamentals of Index Tuning Week: Part 2, Indexing for ORDER BY

30 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.

Menu
{"cart_token":"","hash":"","cart_data":""}