Index Sortables

Yelling and Screaming

If you saw my post about parameterized TOPs, one thing you may have immediately hated is the index I created.

And rightfully so — it was a terrible index for reasons we’ll discuss.

If that index made you mad, congratulations, you’re a smart cookie.

Yes, my friends, this index is wrong.

It’s not just wrong because we’ve got the column we’re filtering on is second, but because there’s no reason for it to be second.

 

We Index Pretty

The reason I sometimes see columns appearĀ first in an index is to avoid having to physically sort data.

If I run the proc without any nonclustered indexes, this is our query plan:

A sort, a spill, kablooey. We’re not having any fun, here.

With the original index, our data is organized in the order that we’re asking for it to be returned in the ORDER BY.

This caused all sorts of issues when we were looking for VoteTypeIds that were spread throughout the index, where we couldn’t satisfy the TOP quickly.

There was no Sort in the plan when we had the “wrong” index added.

Primal

B-Tree Equality

We can also avoid it by having the order by column second in the index, because our filter is an equality.

Having the filter column first also helps us avoid the longer running query issue when we look for VoteTypeId 4.

I like you better.

Solving for Sort

If you’ve been following my blogging for a while, you’ve likely seen me say this stuff before, because Sorts have some issues.

  • They’re locally blocking, in that every row has to arrive before they can run
  • They require additional memory space to order data the way you want
  • They may spill to disk if they don’t get enough memory
  • They may ask for quite a bit of extra memory if estimations are incorrect
  • They may end up in a query plan even when you don’t explicitly ask for them

There are plenty of times when these things aren’t problems, but it’s good to know when they are, or when they might turn into a problem.

Thanks for reading!

Leave a Reply

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