Database Animations: How Index Seeks Work

In my free How to Think Like the Engine class, I gloss over the entire concept of B-trees and just hand-wavily say “SQL Server can seek to the page that has the row you’re looking for.” That’s always kinda bothered me, so I finally gave in to temptation and whipped up an animated example of a clustered index on the Users table:

Let’s break down what’s happening.

It Starts with the Root Page at the Top.

Every index has a single 8KB page at the top of the B-tree, and it’s kinda like the table of contents.

It annoys the bejeezus out of me that every B-tree diagram I’ve ever seen is always shaped like a pyramid, with the “root page” at the top. Do database people ever actually touch grass?!? The root is down deep in the earth, party people. It’s not called a B-pyramid. Whatever. I didn’t wanna fight convention and I just stuck with it, with the root at the top.

In this case, the root page is in data file #1, and it’s the 100th 8KB page in the file, thus the label 1:100. SQL Server pages are always labeled like that – and the rest of the pages in the diagram all start with 1: for that reason. You can tell this index lives in the 1st data file, and that our database (like most) probably only has one data file, since everything is in file 1.

When our query runs, and SQL Server decides to use this index to get the data, the first thing it has to do is open up that root page. We’re looking for Id 12345, so SQL Server looks at the root page to ask, “Which leaf page has row 12345?” The root page itself doesn’t know that in this case – although it could, in much smaller tables – but here, we’ve got enough rows that the root page needs to point to other directory pages.

The root page knows that if it’s looking for Ids 1-500,000, that’s on page 1:200. In our case, those numbers are nice and round, but they would never be that round in real life, because your tree isn’t going to be this balanced, and you can fit oddball numbers of rows on pages. More on that over the coming weeks. (Don’t wanna overwhelm you with diagrams today.) So our process pops over to page 1:200 in order to keep tracking down our row.

The Intermediate Pages are Like Tour Guides.

The root page, like all the other pages, is only 8KB. It can’t fit a complete directory of the ranges of every value on every 8KB page in the Users table, all on one 8KB page. You can only write so small on a grain of sand. So rather than pointing directly at the specific 8KB data page that holds Id 12345, it has to point at another directory page, called an intermediate page.

The middle two sets of pages on our fancy diagram are intermediate pages, which serve as further directories. “Oh, you’re looking for ladies’ intimates? No judgment, sir, head over in that direction over there. Yes, yes of course it’s a gift, no need to explain.”

The more data pages your object has, and the fewer rows you can fit on each data page, the more levels of intermediate trees you’re going to need in order to go from the root page down to the data page. These page reads are considered part of your logical reads: while they’re cached in memory, SQL Server has to keep popping them open for every seek that it does. This is why you’ll see a single row index seek take a whole handful of logical reads, sometimes more, on larger tables: it takes that long to traverse the tree.

The Data Page is the Finish Line.

Finally, on intermediate page 1:411, we’re able to see that rows from 12,001 to 16,000 are stored on page 1:301, and we’re able to open the page that actually has the data we’re looking for.

The pages with the actual data are called leaves, and the leaves are at the bottom of the diagram, on the ground, because they’re computer science professors, not biologists. Or human beings.

Nonclustered Indexes Work the Same Way

Say we have a nonclustered index on the Location column, and we wanna find the people who live in Helsinki:

Now instead of looking for a particular number, we’re looking for a string, and these strings are organized in alphabetical order, so essentially it works the same as looking for a number. Helsinki is alphabetically between Geneva and Jakarta, so the intermediate tour guide pages direct us towards page 1:931.

Here’s where things get a little tricky: note that on the data pages, SQL Server isn’t storing the pages where the two people in Helsinki live. Instead, it lists their Id values. Our table has a clustered index on Id, so now when we want to do a key lookup, we’re going back to the top of this blog post, and we’re doing a clustered index seek on their Ids.

Essentially, an index seek + a key lookup = two index seeks: one on the nonclustered index, and one on the clustered index for the key lookup. Each index (the clustered and each nonclustered index) has its own root page and intermediate pages.

You might be thinking, “Wait, couldn’t we save a lot of time by, on the nonclustered leaf pages, saving the file_number:page_number where the corresponding clustered index row lives?” Well, you could save time on reads, but then you would spend a lot of time updating all of the indexes whenever we had to move a clustered index row around from one page to another. More on that in another post.

This Animated Diagram Has a Lot of Implications.

For example, look at the animation again, this time focusing on the page numbers. They’re scattered all over the file, and that’s completely fine. There’s no reason whatsoever that all these pages have to live next to each other in the data file. The index pages up at the top are very likely cached in RAM anyway, and RAM isn’t short for RAMEN. The R has a meaning, dear reader. Computers don’t need these pages next to each other in order to access them quickly.

This isn’t the only way to access the leaf pages, either. If you know you’re gonna hit all of them, you can start at either end, and read from one leaf page to the next. The leaf pages are doubly linked lists: every page has a pointer to the previous page and the next page, links if you will, so that you can keep paging down through the table, scanning it, without dealing with the intermediate pages.

These pointers also help when you need to read ranges of data. For example, if you want the Ids between 9500 and 13800, you can do a seek to 9500, and then jump from page to page without going back up the tree:

Like These Animations?

You’re gonna love my Fundamentals training classes. In Fundamentals of Index Tuning 2026, I use animations like these to explain composite indexes, page splits, index reorganization & rebuilds, b-tree hotspots, and more. The live enrollments are closed now because the classes are underway (literally, I’m teaching Fundamentals of Query Tuning today), but you can stream the recordings for $495.

And if you’re already a subscriber, good news: the updated Fundamentals of Index Tuning 2026 is already watchable in your account! Get in there and take advantage of the training you paid for. Or your manager paid for. Or your last manager, if you’re the kind of person who steals staplers from the office, which I wholeheartedly endorse.