Learn T-SQL With Erik: Clustered Tables, Page Splits, and Readaheads

Erik Darling here with Darling Data. In today’s video, we are going to talk about, well, we’re going to use a snippet, a tiny little bibbidi-bobbidi-boop from my larger training corpus of material, the Learn T-SQL with Erik course. It’s available down in the video description. I’m going to say this in like two minutes again anyway, but down in the video description, there’s a link you can click on to buy this course at a date. I’m going to give you a discount because I care about you and your financial future in the world. But today we’re going to learn about clustered tables, how they differ from heap tables, which I guess was maybe fairly obvious if you watched yesterday’s video or if you’ve been around SQL Server for a bit. But there’s an interesting thing that I want to show you at the end of this that not a lot of people know about. So you’re just going to have to hold on to your diapers for that. Of course, like I said, I predicted this, right? I predicted these predictions. Erik Darling knows his stuff. Down in the video description, you will find all sorts of helpful links to hire me for consulting, buy my training, including the larger corpus of training material that makes up the Learn T-SQL with Erik course. And the links down there where the fingers are pointing. You can even do something kind of funny and be like, I’m juvenile. But you can also become a supporting member of the channel. Ask me office hours questions. And of course, if you enjoy this training content and you want to keep me going, you want to keep my batteries full, you want to keep my wind in my sails, I’m going to keep my powder dry here, then you should like, subscribe and tell a friend or many friends. Just don’t tell. I mean, you can tell the robot friends. Maybe they’ll learn a few things from me. I don’t know. They can’t subscribe. They can’t like, but you know, maybe maybe they’ll pick up on some helpful things. If you want free, absolutely free as in speech kind of SQL Server monitoring, you can you can get that from me. I have released completely open source, no charge, no email, no weird phone home telemetry. It’s available at my GitHub repo. It’s all the stuff that I would monitor if I cared about monitoring SQL Server, which I do. So I do now. It’s all in there. Wait stats, blocking, deadlocks, query stuff, you know, CPU, memory, disk, you name it, Erik Darling is on top of it.

And if you are a fan of our robot friends, if you want to have your favorite robot talk to your monitoring data, and maybe give it some like some better context on what you’re looking for, what your problems are, you can you can use the optional opt in built in MCP tools to have the robots go look at stuff for you and maybe get some faster answers than just poking through all sorts of charts and graphs on your own, because that can, that’s that can be difficult if you don’t know what you’re looking for. But the robots who have been trained on years of smart SQL Server people like me saying things for free online. Apparently, apparently, they can they can whiz bang, look at all your data and give you answers. Maybe, we’ll see. You can still hire me if they if you don’t like their answers. I’ll be your robot friend.

Of course, as long as as long as people still care, I will be bringing you human generated warmth, kindness, hugs, I don’t know, all that stuff out in the wider world. I will be at SQL day Poland, May 11 through 13. Shortly after pass on tour Chicago, May 7 and 8. Crazy, right? From Chicago, I go from New York to Chicago to Poland, and back to New York. And then like a month later, I go to data Saturday, Croatia. I can’t wait for that one. I hear it’s it’s going to be June. Unlike just the other side of Italy. And I’m excited about that. See some different water in my life. Be be a hell of a good time.

So you should you should come to all of those. You should just be like a like a like what do they call those people who follow like the Grateful Dead around? Yeah, you should do that with me, right? You can be a data head or something. Sounds unfortunate. And then that June is it for me for a little bit, I guess. And then past summit in Seattle, Washington, November 9th through 11th. And well, I don’t know, maybe I’ll find some way to fill up my calendar between June and November. But well, we’ll have to see what’s out there. Anyway, it is my favorite time of year. Baseball is in play. Right? Got to see a Mets game, got to see a Red Sox game all in the same day. It was glorious. Drink beer and listen to the sound of bats hitting balls. Well, that’s, that’s, that’s, that’s my, that’s my idea of a good time. Anyway, let’s talk about clustered tables. So we are going to use a somewhat different store procedure.

And if you are interested in what this store procedure does and what is happening behind the scenes, of course, this is all available in the bigger class materials. So you can click on the link and you can buy the class. But we’re going to load up our clustered table here. And we’re going to put a little over a million rows in it. Not because a million rows is an impressive number, because it’s just enough of a number to make some other numbers more obvious.

So we’re not trying to be crazy here. But the first thing I want to show you is that there are not forwarded records, unlike yesterday with the heap, there are not forwarded records in a heap, and there will never, in a clustered table, and there will never be forwarded records in a clustered table. All right. But right now, what we have is this many pages, 5862 in the clustered table, this many rows, which is the same in both the clustered table and the nonclustered index we created on the clustered table, and our average space used in percent is pretty good. This is not logical fragmentation. All right. This is not data pages being out of order on disk. This is the fullness of our data pages.

This is how many rows we have crammed onto these data pages. All right. So this is an interesting number for what we’re going to look at in a moment. Keep that in mind. Right. So just like yesterday, we want to find the middle row of the table. And the middle row for us is going to be, well, since I’m recording both videos the same day, it’s literally the same day, but whatever. So what we’re going to do is just quickly look at what we get back in here, right? So if we look at general performance with these queries and over in the messages tab, we will see that the read from the clustered table, and if you didn’t know about this, then it’s a neat thing. You can actually edit the text in the messages tab.

The reads against the clustered table, about 1.6 million, and the logical reads against the nonclustered index are about 5880. I’m not someone who pays a lot of attention to logical reads. I’m not someone who uses them as a tuning device. I find them to be quite a secondary indicator of a problem. For me, CPU and duration are far more important indicators of problems.

But I do want to show you that this does have an effect on things. And one easy way to show you that it has an effect on things is how many more reads the table does after some things happen. So yesterday, we looked at forwarded records or forwarded pages a second from the perfmon counters.

Today, we’re going to look at page splits because the heap did not have page splits. The heap had forwarded records. With a clustered table, SQL Server will split pages once they become full, and it will take about 50% of the rows, and it will move them to a new page. And this is sort of the different type of data movement within a table.

So like with the heap, it just took a row and moved it to whatever page had space on it and left behind a forwarded record pointer. With the clustered table, it takes half the rows and puts them on a new page and keeps things in sort of clustered key order. So we ran our update, and as far as page splits go beforehand, we had 647528.

I should have restarted my server before I did the zero. It’s a lot easier to compare zero to something. So we had 64752898 and then 6481308. So we changed the third significant digit there by one, which is, I suppose, interesting for us.

But we can see that the number of page splits that have occurred on the server have gone up a bit here. We can see that that number has increased. So if we look back at the table itself now, and we look in here, right, we’re still not going to have any forwarded fetches because that’s never a thing.

But we do have a lot more pages in the table now, and the average page space used in percent has gone down dramatically. This was at 99-something percent. We are now below 50%.

We are at 46% full. Well, that doesn’t feel too good. What this means is that SQL Server has a lot more pages in the table now that are a whole lot less full, which means we are sort of like yesterday when we deleted a bunch of data from the heap and we still read empty pages.

This is sort of like that, except now we’re reading a bunch of pages that are about half full, so it takes twice as many pages to get through things. This is far more of a problem if we are reading pages from disk into memory than if we’re just reading stuff from memory, or even if we’re just seeking into the table, this number doesn’t really matter, but it is something to keep track of, right?

Like this might be a good reason to rebuild an index because you want to reclaim page density, right? You want to read from smaller objects. You want to make sure your queries are not I.O. bound, reading lots of extra stuff from disk into memory.

You also want to make sure that your indexes are not taking up like twice as much space in your buffer pool as they need to because they are full of half full pages, right? So if we look at the stats.io performance here, come on, finish.

There we go. This one still does about 1.6 million, but this one does way more. Now, this was at like 5,800. Now, this is at 65,000, right?

So our query here that had to go do a bunch of, that just scanned the clustered index, or sorry, yeah, that scanned the clustered index. This is not a heap means this is the clustered index, right?

If we, did I turn on query plans? I didn’t. Well, dumb me. I’m, all right, let’s turn on query plans and look at that again so I can prove to you what I’m saying here because the, you know, clustered query plans do a lot more proving than anything else, right? So this one does a lookup against, or does a key lookup, right?

Hit the nonclustered index, do a lookup to the clustered index to get the stuff we care about. This one here just scans the clustered index. So this one here did far fewer logical reads before.

Now does a lot more now. So the page splits didn’t really change reads for the seeks, but it did for the scan. The thing is that neither CPU nor duration really went up for a lot of them, right?

Because we’re not just, we’re just not doing so much that it makes a difference, right? This is just a million row table. It’s not terribly hard to deal with performance on a million row table.

You’re not going to see big changes in performance on a million row table. Typically tables have to get pretty big before you should start caring about these things with them. But once they get to that size, or once they get to a size where they start making a difference for you, this is the kind of stuff that’s good to know about.

That’s why this is the kind of stuff that I’m teaching you about. So let’s turn off query plans. And let’s look at how things appear in our table now. Because remember yesterday, when we deleted all those rows from the heap, we’re using the cursor.

Remember that the table was the same size after we did all those deletes, because SQL Server didn’t deallocate the pages. It kept them around just in case we decided to load more data in, so it didn’t have to allocate pages while we’re loading data in.

That is an optimization that you get with heaps that you do not get with clustered tables. So query plans are turned off, and now let’s cursor through our table and wait for those deletes to finish. Remember that yesterday, the heap delete took about nine seconds.

I’m not promising that this is going to be any faster or any slower. It is just the way it worked. This took 11 seconds. So two seconds slower, I guess.

But now, if we look at the size of the table, we will see that we have a few root and maybe intermediate pages still kicking around, but this table is no longer 550 or so megs, and we have gotten rid of all of the data in there doing the deletes. Unlike with the heap, where you need a tablock hint or other manifestations of voodoo, this went sort of on its own.

And we’ll do our little checkpoint thing here, and we’ll find that pages are completely deallocated. So we just don’t have anything in there. It’s empty.

Anyway, the thing that I think is interesting, like index fragmentation, when people talk about it, they’re talking about logical fragmentation, it is largely just very goofy.

It is just like 2008 mentality. It’s like people who harp on about VLFs and other stuff like that. It’s like, come on, like, get with it, right? What year is it?

You’re treating SQL Server like it’s still 2008, and you’re goofy, and everyone thinks you smell funny. But one thing that I want to show is that having pages be less dense, right? Having the sort of physical fragmentation can decrease the efficiency of read-ahead reads, because you have to do way more small ones instead of fewer large ones.

This is a byproduct of lower scan density when your pages are not very full. This is one reason why I sort of get on people’s cases when they start messing with fill factor, because you’re sort of artificially introducing physical fragmentation to your tables or indexes.

The cluster table will suffer from sort of increased read-ahead. It’s not going to be a dramatic impact because the table is not huge, but just something to be aware of. So let’s reload both tables, right?

And what we’re going to do is run these two store procedures. Again, the code for these store procedures is available if you buy the full course material. But what we’re going to do is we’re going to test read-ahead against the heap and the cluster here.

Now, the query plan up here shows us the heap scan. That takes 125 milliseconds. This is the query plan for the clustered index scan, right?

That is 114 milliseconds. Those things are pretty close there. But if you look at the total number of read-aheads, right? We don’t have any forwarded records on these.

We’re not going to. But the heap did 9,400 read-aheads or 43,000 read-aheads a second. And the clustered index did 5,900 read-aheads. So the clustered index is pretty efficient on those read-aheads, right?

Did fewer read-aheads. So, because it could read lots of stuff. Now, we’re going to run these two things. And these two things, unfortunately, well, the update against the heap takes a real long time.

Not, like, terribly long, about 30 seconds or so. But the reason why the heap takes a long time is because it has to do all that forwarded pointer stuff, right? Every time a row outgrows a page, SQL Server has to go find a place to stick it.

Which means it has to keep looking at data pages and maybe sometimes even, like, adding a data page to put something on. And so that can just take a little while, right? It’s, like, an arbitrarily annoying amount of work.

So, if you’re, if you have heaps and you’re updating them and those updates are pretty slow, well, part of the reason just might be that you are, you’re making SQL Server do a lot of forwarded record work. Maybe, like I said yesterday, updating, heaps are not made for updates, right? So, if we look at the query plans for these, we’ll see that the heap update took 33 seconds, right?

It’s the same amount of work. And we’ll see that the clustered index update took about four seconds. That was far less time, right?

Updating that heap, not good. If you’re a heap updater, please stop. You’re causing problems for yourselves. Now, if we look at the readahead test for this, right? I’m going to run these.

Look at this. Now, things get, now things completely flipped, right? So, the heap is going to, like, the readahead stuff is going to get, is going to be harmed by the forwarded records, right? Because now we have a crap load of those in the heap, right?

And that takes one and a half seconds. Now, remember, that was, like, 112 milliseconds before. So, for the forwarded records in the heap, like, that can hurt readahead because this was, like, 43,000 things per second before. Now, it’s at 20,000.

Now, this one is interesting because the time didn’t really get hurt, but we do a lot more of these. We do a lot more smaller readaheads. Readaheads work best when they are big, right?

You want to do big readaheads. You want to max out your readaheads. If you’re only reading ahead small chunks, it’s far less efficient, right? So, this thing here, not so crazy about that, right? Because it took, we had to do a ton of very, very small readaheads.

Anyway, that’s all because of a thing called an allocation order scan. And even though you can get an allocation order scan with a clustered index, and you can get those readaheads, the underlying table is still not exactly the same as a heap member, that a clustered table is still a B-tree index.

So, it’s not the same flat structure that a heap has. Remember, heaps are not trees. They are just flat structures.

So, like, you still have this sort of navigational stuff that has to happen with them. So, while you can get allocation order scans, and they can be good with clustered indexes when readahead reads are very, like, wide and efficient, or very big and efficient, you don’t want lots of small ones, because it’s still not a one-to-one with the allocation order scan on a heap.

So, I don’t know. That’s all I had to say here. Clearly, that’s the end of the file. But there’s a lot more in the full course content. So, if this is the kind of stuff that you’re interested in, I would suggest that you buy it so that you can see more of this stuff.

It’s wonderful. Anyway, thank you for watching. I hope you enjoyed yourselves.

Hope you learned something. And I will see you in tomorrow’s video, where we are going to talk about nonclustered indexes and their relationship to the tables that they are created on. All right. Thank you for watching.