A Little About: Old vs New Cardinality Estimators In SQL Server
Video Summary
In this video, I delve into the reasons why I often prefer the legacy cardinality estimator over the default one in SQL Server. Using a practical example involving an identity column backfill process, I demonstrate how the legacy CE can provide more efficient execution plans compared to the new CE. By running the same query with both estimators and comparing their performance and execution plans, I highlight key differences that might influence your choice of cardinality estimator during query tuning. Whether you’re looking for a straightforward explanation or want to see real-world implications, this video offers valuable insights into when and how to leverage the legacy CE for better query optimization.
Full Transcript
Erik Darling here with Darling Data and we are going to talk in this video because I actually answered an Office Hours question recently where someone asked why I generally prefer the old cardinality estimator, old or legacy, compared to the new cardinality estimator or default cardinality estimator as Microsoft and all its blue Azure-y hubris calls it. And I’m just going to show you an example today of a query. Now, I know this is an example of one query, but it’s just a good example of kind of generally why I tend to prefer the legacy cardinality estimator and why when I am tuning queries and I am using a version of SQL Server and a database whose compatibility level dictates that we are using using the default cardinality estimator using the default cardinality estimator and using the legacy one. This is not, of course, a thorough undoing of everything that the new cardinality estimation model attempts to, you know, do differently than the legacy model, but it’s just an example of why I tend to prefer it and why I will always try it out. There are, of course, worst times in my query tuning life when I’m maybe using the legacy one and I might say, hey, let’s give the newer one a shot. Let’s give it a try. Let’s see how it goes. The worst thing that happens is that either the query finishes in the same amount of time or takes longer and we can say that didn’t work. Let’s try something else. So here we go with that. But before we do, of course, I mentioned Office Hours. If you want to ask me questions privately that I answer publicly, that link right there is how you do it. It’s down in the video description. There’s also a link where if you think that this channel is worth is as little as $4 a month, leaving your bank account and going into my bank account, you can sign up for a channel membership to support all of this wonderful material that I produce. I am also an acting SQL Server consultant active, maybe play play one on TV, play one on YouTube. If you need help with your SQL Server, you can hire me. And as always, my rates are reasonable. Hooray for reasonable rates. Anyway, my performance tuning training, if you want it for about 150 bucks for the rest of your life, that’s the link, that’s the discount code. The forming pattern here is that it is also in the video description.
My T-SQL course, Learn T-SQL with Eric is also available currently at a pre-sale price of $250. I recently finished recording all of the beginner material and am hard at work working on the advanced material now. So that will get done after the summer. The price of the video course will go up to $500 when that is complete. So I suggest you do that sooner than later. I am speaking a lot all over the place this summer. New York City, Dallas and Utrecht, August 18th to 19th, 15th to 16th, and October 1st to 2nd. Of course, all that is right before I go to Seattle for Pass Data Community Summit. Kendra Little and I will be delivering two T-SQL pre-cons at Pass Summit together over two days. So I hope to see you at both of those. But with that out of the way, let’s talk about this turkey here.
Now, let’s say that we have a table and that table and it has an identity column. Let’s pretend it’s called the votes table and let’s pretend it’s in the database called Stack Overflow 2013. Think that’s reasonable, right? The reasonable set of things that we can pretend. And we realize one day that our ID column is not as contiguous as we would like. And maybe we would like to go and backfill it. So we start designing a process to find all of the missing rows in the votes table. In this case, our job is to find the first, the lowest value that is missing from the votes table and then assign that to something and then do an insert to start backfilling rows in there.
Maybe that sounds a little silly, but I’ve seen plenty of places start needing to backfill their identity columns. And, you know, depending on various local factors, this might be a reasonable way of doing it. So if we just select the top 10 from the votes table and we look at the ID column, when compared to the row numbering column, right, this is not a column from the table.
This is just the row numbering that comes back from SQL Server Management Studio. We’ll notice about right here that things go a little amok on us, right? We are clearly missing ID 8, right?
We are like this goes 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. This goes 1, 2, 3, 4, 5, 6, 7, 9. Ah, boy, we’re missing ID 8. All right, so our goal is to write a query that will find us the earliest missing ID.
Sounds pretty easy. One way of doing that would be to write a query that looks about like this. You would say I want to select the top 1 missing ID, which in this case, because of what we’re trying to accomplish, we would need to add 1 to the ID column here.
And we’re going to say where not exists, select doesn’t matter from the votes table, where v2.id minus 1 equals v.id. And we will order our results by v.id to make sure that they stay perfectly deterministic.
Since ID is already the clustered primary key of the votes table, it is guaranteed to be unique. And since it is indexed, it is presented to us in order. Now, you might look at this and scream a lot about sargability and whatnot, and I hear you.
I hear you. There’s a lot to be said for sargability. But we’re going to run this query in two ways. Right now, my database is in compatibility level 160.
1.6.0 for SQL Server 2022. So I’m going to run this query once using compatibility level 160 and all its attendant properties. And then we’re going to run the exact same query the exact same way down here, except we’re going to add in this use hint to force legacy cardinality estimation.
All right? So that’s the only difference between these two things is one is using the default cardinality estimator, and the other one is using the legacy cardinality estimator.
You might notice that it’s been a little while since things started running. You would be a very observant person if you picked up on that. These both return ID8, right?
They both return the correct result, but the execution plans are quite different. So this top plan is using the default cardinality estimator. You’ll notice that it took 8.6 seconds right here.
And if we scroll over here a little bit, yeah, my head’s not in the way. We’re off to a great start, aren’t we? We spent 1.7 seconds fully scanning the votes table. We spent 2.2 seconds fully scanning the votes table here.
We’re going to ignore the timing on the repartition streams operators for now because the wall clock timing on parallel exchanges is a complete disaster. So we’re going to ignore that for the time being.
And we’re going to look at this. So this is just about this whole section in here is where 8.5 seconds winds up, right? It’s not like something weird happened over here.
Like we have this top end sort, but this top end sort wasn’t like spilling a bajillion, 52 million rows to disk. And we were like, ah, God, we can’t take it. It’s tempdb.
We broke tempdb. It’s not that. If we look down here at this query where we use the default legacy cardinality estimator, oh, dear old me, this query chose a completely different execution plan, right?
One, it’s single threaded, right? I mean, first off, you might want to know it’s right here. It takes one millisecond.
This is a single threaded execution plan. There is no parallelism at all in any of this whole entire thing. But there are some funny looking numbers.
Like, for example, 900 of 1, 4, 5, 4, 6, 0, 0, 0. So that’s eight digit number. That’s 14 million rows, 14 and a half million rows.
It only took 900 rows for us to find it. So the big difference here is if we look at the properties of the clustered index scan here, there will be this row, estimated rows without row goal.
So SQL Server estimated that it would have to read this many rows to get stuff out of there. But, you know, the number of rows that it took was 900, right? So the actual number, we only needed 900.
SQL Server was like, it might take a while, but we only actually ever needed 900. But the important thing here is that this exists here, right? So we have estimated rows without row goal here.
We have estimated rows without row goal for the second time we touch the votes table. But if we click on these up here, that estimated rows without row goal thing disappears, right? It’s not in here, right?
Even though we have a top in there, SQL Server used a top end sort up here. So this one just used a regular top. So some slight visual differences in the execution plans. But this is sort of in general why, again, when I’m tuning queries and I am using the default cardinality estimator and I get a rather suspicious looking plan, I say to myself, Eric, we should check in on that legacy cardinality estimate.
We should see how Legacy Cardinality Estimator is doing today. Let’s see. Maybe we can bring it some snacks or, you know, just go give it a call. Have a little chat with it.
See how it’s hanging in there. Because a lot of the times, even if you don’t see like a performance difference this drastic from like eight and a half seconds to one millisecond, you can at least, you know, get some feedback from it and see if there are any differences.
And, you know, sometimes you do see something this dramatic, just like I did. Again, you know, you can go on and on about sargability and subtracting one from something that you’re comparing here. But, you know, Legacy Cardinality Estimator just does a better job here.
So you might find this in your queries as well as you are going through and tuning things. And like I said earlier, you may also find the opposite is true sometimes. You may find that the Legacy Cardinality Estimator does a rather rotten job of things occasionally.
And you might find that testing out the new Cardinality Estimator will do a better job. If you want to test out the new one, what you can do is say force default Cardinality Estimation. And you can use that use hint to test your queries out using the default Cardinality Estimator.
But anyway, that’s enough for now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you over in the next video where I’m going to talk about… So I did a couple of videos sort of recently about writing batch mode friendly queries. And I realized one thing that might be useful for people would be a small bit of education on how to recognize query patterns where batch mode may be useful.
So we will do that. And one of these queries might even make an appearance in there. You might even see this exact same starting point.
So that’ll be fun for us anyway, won’t it? All right. Thank you for watching. All right.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.