Free Fundamentals of Index Tuning Week: Part 2, Indexing for ORDER BY

Yesterday, we covered Part 1: Indexing for the WHERE Clause, and today we’ll cover ORDER BY. Make sure to keep up – on April 1, these posts will magically disappear.

Lecture (28 min)

Just like designing indexes for WHERE, this is another topic that seems simple on the outside, but unravels quickly. If your query only has equality searches in the WHERE, then the ORDER BY fields can just get added right onto the index keys, in…order. (See what I did there?)

But if your query has INequality searches, that’s where things get weird. You can end up with sorts in your execution plan even though the ORDER BY fields are in the index keys. Throw in a TOP, and your ORDER BY fields might even need to go first in the index!

 

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.

Now, it’s your turn: take the below queries individually and design the right nonclustered indexes to make them dramatically faster. For bonus points, you have a new challenge this time around: as you’re creating indexes to speed up queries, try to predict whether your execution plan will include a sort operator.

As you work through the list:

  • Try different combinations of key and include fields for each query
  • Bonus points: guess whether the query plan will include a sort or not
  • 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
  • Bonus points: if I told you your goal was to reduce sorts, think about how your index key choices might be different

I do the first one, and then you tackle the rest:

This time, you’re going to be writing some of the queries yourself, too! It helps you understand the schema better, and helps you learn to predict what indexes will help the query even as you’re writing it:

Checking your answers against mine (18m)

Tomorrow 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
Free Fundamentals of Index Tuning Week: Part 1, Indexing for the WHERE Clause
Next Post
Free Fundamentals of Index Tuning Week: Part 3, Indexing for Joins

5 Comments. Leave new

  • Thanks for posting this, it’s a massive bonus for those of us who can’t access your courses due to GDPR and it’s definitely rare to find something of such high quality for free, so massive thanks again for doing it.

    Reply
  • Vinícius Napoleão D. Ribeiro
    March 18, 2020 8:01 pm

    Brent, I from Brasil and It’s incredible the contents of these free vídeos! My home-office is paying of!f
    Thank you!

    Reply
  • Hi Brent,

    Enjoyed this one about sorts! Learning a lot. Sorry I seem to open every post with this 🙂

    Question: Sometimes I see I my query plans that I get a sort operator after the index scan or seek operator while I am not using a sort in my query. Do you know why this happens and how I could fix it? Or do you need to first see the plan and the query to answer my question? Thanks again!

    Reply

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":""}