Blog

In our last cliffhanger episode, I said that if we ran this query:

And we had this index:

Then we would have to do all of these things:

1. Look up user #643 on the clustered index by doing a seek – because thankfully our kind developer included the clustered primary key in the where clause
2. (If the relevant clustered index pages for this user aren’t in memory, fetch them from disk)
3. Get user #643’s LastAccessDate, Id, DisplayName, and Age so that we can look them up quickly on the gray index
4. Look up that LastAccessDate, Id, DisplayName, and Age in the IX_LastAccessDate_Id_DisplayName_Age
5. (If the relevant nonclustered index pages aren’t in memory, fetch them from disk)
6. Lock these rows that need to be updated
7. Write to the transaction log file indicating what we’re going to change
8. Change the data page(s) for the clustered index
9. Change the data page(s) for the nonclustered index, including moving a row around to different pages if necessary (like if the key values changed enough to merit being on an entirely new page)
10. Later – asynchronously – write the changed data pages to disk

So what parts can we skip if we just include DisplayName and Age?

Are included columns somehow lighter weight? Let’s say we have this index:

Let’s examine the work required with the old index:

1. Look up user #643 on the clustered index by doing a seek – because thankfully our kind developer included the clustered primary key in the where clause – we still have to do this
2. (If the relevant clustered index pages for this user aren’t in memory, fetch them from disk) – we still have to do this
3. Get user #643’s LastAccessDate, Id, DisplayName, and Age so that we can look them up quickly on the gray index – we still have to do this, but we only need LastAccessDate and Id – not a real reduction in work here
4. Look up that LastAccessDate, Id, DisplayName, and Age in the IX_LastAccessDate_Id_DisplayName_Age – we still have to do this, it’s just that we’re looking it up in IX_LastAccessDate_Id_Includes
5. (If the relevant nonclustered index pages aren’t in memory, fetch them from disk) – we still have to do this
6. Lock these rows that need to be updated – we still have to do this
7. Write to the transaction log file indicating what we’re going to change – we still have to do this
8. Change the data page(s) for the clustered index – we still have to do this
9. Change the data page(s) for the nonclustered index, including moving a row around to different pages if necessary (like if the key values changed enough to merit being on an entirely new page) – we still have to do this – it’s just that we don’t have to move the row around on the page…or do we?
10. Later – asynchronously – write the changed data pages to disk – we still have to do this

Even before I get tricky with the answer, you can see that we still have to do every single one of those steps! (And just to be clear, there are even more steps than this in dealing with updates, but these are by far the biggest ones, and I’m concentrating on the bulk of the workload here, not tiny edge case differences – let’s focus on the big picture for now.)

The only possible reduction in these 10 steps is a slight tweak to #9, the ability to skip moving rows around between pages.

Well, we still save a little in step 9, right?

Keep thinking, dear reader, by examining the index page involved. User #643, Sarcastic, is at the top right of the page:

If you update Sarcastic to be 31 years old instead of 32, does he move on the page?

If you updated his name to be Optimistic instead of Sarcastic, do you have to move him around?

Even if you somehow changed his Id, do you have to move him then?

As long as the first column or two (or both) are fairly unique, the sorting on the third, fourth, fifth, etc keys on an index don’t matter nearly as much as the first column or two in the keys. This is one of the (many) reasons why folks often suggest that the first and second keys on an index should be very selective: they should help you quickly narrow down your search space. If that first key or two changes, then sure, we’re going to need to move a row around – but for subsequent keys, not quite so much, assuming that you did a good job of picking your first and second keys. We dig into picking index key column order in way more detail in my Fundamentals of Index Tuning class.

There are absolutely cases in this big, wide, wonderful world where it makes a stunning difference whether the 3rd and 4th columns are keys as opposed to included columns. However, they’re probably not the cases that you’re facing. In your environment, out of the 10 steps involved with updating a field, the big question isn’t, “Should this column be in the key or the includes?”

The big question is, “Should this column even be in the index at all?”

In a perfect world, we don’t index very hot data: data that changes constantly, forcing us to take locks on not just the clustered index, but all of the nonclustered indexes that contain that data, too. The more columns you include – especially hot columns – the more blocking problems you’re going to run into, and the slower your storage becomes (because you’re doing so many writes per update.)

In that perfect world, we probably wouldn’t index LastAccessDate, for example – a value that changes every single time a user logs in or visits a page. After a few days of users logging in, imagine what this 8KB page above is going to look like – and we’ll talk about that soon, too.

The point to take away:
design the right leaf pages first.

When you’re designing an index, start by asking:

1. “Does this index enable me to seek to just the rows I need, and only read those, and no extras?”
   • “If not, what are the extra reads, and am I okay with that?”
2. “Does this index give me all the columns I need?”
   • “If not, what are the additional key lookups required, and am I okay with that?”

Don’t get hung up on keys versus includes before you answer those first four questions. Once you’ve answered those, then you can start obsessing about keys vs includes – but so often, I see people obsessing over what to put in the includes, thinking that it’s somehow going to be noticeably cheaper for performance purposes. Most of the time, it ain’t – but even worse, they haven’t designed the index to answer those four questions above to begin with.

And I get it – it’s hard, especially when the missing index recommendations seem to be working against you. We’ll talk about that later this week.

In our last episode, in between crab rangoons, I had you create either one of these two indexes:

And I said that the leaf pages of either index would look the same:

In terms of the space they take up on the leaf pages, it doesn’t matter whether columns are in the keys of an index or in the includes. It’s the same amount of space on the leaf pages. To see it, run sp_BlitzIndex focused on that table:

And note that the two highlighted indexes at the bottom – which are the two indexes I told you to choose from – take 12.4MB and 12.5MB. The difference in size has to do with the tree pages that help SQL Server get to a specific leaf page. If you wanna learn more about that, pick up a book on SQL Server internals – any of them will do, since this part of the topic hasn’t changed in decades.

Somehow, people think included columns are cheap.

I don’t know how this happened, but an urban legend developed around INCLUDE columns that they’re somehow blazing fast and inexpensive. To understand why, think about this query:

If we ONLY have this index:

Then here’s how the update’s execution plan looks:

Remember, we read plans right to left, so here’s the work we did, although not necessarily in order – and note that I’m including more details than the plan shows graphically because there’s more work to be done than is immediately visually apparent:

1. Look up user #643 on the clustered index by doing a seek – because thankfully our kind developer included the clustered primary key in the where clause
2. (If the relevant clustered index pages for this user aren’t in memory, fetch them from disk)
3. Get user #643’s LastAccessDate, Id, DisplayName, and Age so that we can look them up quickly on the gray index
4. Look up that LastAccessDate, Id, DisplayName, and Age in the IX_LastAccessDate_Id_DisplayName_Age
5. (If the relevant nonclustered index pages aren’t in memory, fetch them from disk)
6. Lock these rows that need to be updated
7. Write to the transaction log file indicating what we’re going to change
8. Change the data page(s) for the clustered index
9. Change the data page(s) for the nonclustered index, including moving a row around to different pages if necessary (like if the key values changed enough to merit being on an entirely new page)
10. Later – asynchronously – write the changed data pages to disk

What’s that, you say? You didn’t see all that on the plan? Well, a lot of it is hidden at first glance, and you have to hover your mouse over various tooltips (or infer stuff to figure it out.) For example, here’s the nonclustered index update, hidden in the clustered index update:

Do included columns require less work?

You tell me: take a look at that 10-point checklist above, and tell me what would be different if we had this index:

And we ran this specific query:

And I’ll give you a hint: the user involved is at the top right of this gray page:

So you can work through the query yourself with a pen and paper to figure out how much better off we’d be if DisplayName and Age were only included columns rather than part of the key. You can rant and throw things in the comments, and I’ll follow up with the answers in our next episode in this series.

In our last episode, we were running into problems with these two queries:

When SQL Server saw the function in the second query’s WHERE clause, it punted out with a 1-row estimate, which caused us problems. SQL Server did an index seek + key lookup where it wasn’t appropriate.

Well, if we’re not allowed to change the query, we could improve the situation by building a wider covering index. Right now we have this index:

But let’s say we add either of these indexes:

The resulting leaf pages of the index will look like this:

Now, look, dear reader: I know you. You and I go way back. I know how you think, and your first reaction is going to be to scroll down to the comment section and start banging away loudly on your keyboard, debating the difference between putting columns in the include versus putting them in the keys of the index. Hold that thought. We’re going to get to that in the next post in the series, I swear on my standing desk. For now, just roll with this as-is.

Add either one of those indexes – seriously, I don’t care which one you pick, but just only pick one – and the query plans now look identical for ANY date range, even for the entire table:

Ah, the magic of covering indexes. Make an index wide enough, and the queries will beat a path to your door. Now, SQL Server doesn’t have to worry about the tipping point because this index works perfectly no matter how many rows we’re bringing back.

So the plans are the same, right?

Well, no. Come closer, dear reader, and click on the Messages tab. SQL Server is hiding a dirty secret from you:

The bottom query actually read more pages even though it returned the same number of rows! Why is that? Well, hover your mouse over each index seek operator and look at the number of rows read. Here’s the top query:

In the top one, where our dates are clearly defined, SQL Server was able to seek directly into the index at the right point, read the 24,380 matching rows, and then bail out. In the bottom one, however:

\

SQL Server was like me at the Chinese buffet: it either didn’t know where to start, or it didn’t know when to finish, because it put way more rows on its plate than it really needed to. (For the record, even when crab rangoons are bad, they’re still good.) We’ll cover the reason for that (a residual predicate) later in this series.

For now, don’t sweat 60-70 logical reads between friends. (Also, are you going to finish those rangoons?) Don’t get hung up on this index seek not being as good as it could possibly be, because it’s still way the hell better than the 73,225 logical reads we were getting in the last post, and better than the 7,405 reads that a table scan would require.

60 reads isn’t a big deal,
but here are 3 things that are.

Here are three things you SHOULD sweat:

1. The estimated number of rows (1) is still completely inaccurate, which will wreak havoc on further operations in your query, like if you join to additional tables
2. Even when you’re looking at an actual plan, the cost percentages shown are still estimates – SQL Server never goes back and recalculates “actual” query costs
3. The estimated query costs are pretty much irrelevant to reality:

SQL Server’s saying the top query is 97% of the cost of the batch, and the bottom query is 3%. That’s flat out not true: the bottom query does MORE reads than the top query! The whole reason the bottom query shows a lowly 3% cost is all back to SQL Server’s inaccurate estimate that only 1 row would return.

If the bottom query’s function really did only produce 1 row, AND if SQL Server was smart enough to only read the rows it really needed to read (which isn’t the case with this function), then sure, the cost of the bottom query would be cheaper. But it’s not – so the costs, much like my insistence that I can quit crab rangoons whenever I want, is a lie.

Now if you’ll excuse me, I have an errand to run. When I’m done with that, we’ll talk about whether columns should go in an index’s key or in the includes.