Query Tuning Archives | Page 55 of 147

Why Some Indexes Create Slower Than Others In SQL Server

Posted on June 13, 2024January 22, 2026 by Erik Darling

Why Some Indexes Create Slower Than Others In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into why certain indexes in SQL Server can create much more slowly than others. After waking up feeling unusually sleepy despite my usual morning caffeine fix, we decided to use that extra energy to explore the nuances of index builds on different columns. I demonstrate how non-selective columns lead to uneven thread distribution and significantly longer build times compared to selective columns. By examining the properties of these indexes, we uncover fascinating insights into SQL Server’s indexing behavior, particularly when using standard edition versus developer edition. The video also touches on a humorous anecdote about Microsoft support, highlighting the importance of accurate information in troubleshooting complex issues. Overall, this session provides valuable lessons for database administrators and developers looking to optimize their indexing strategies.

Full Transcript

Erik Darling here with Darling Data and a little sleepy. I don’t know why. I woke up this morning, shot out of a cannon, ready to go. And for some reason after drinking my customary two double shots of espresso, I got to feeling sleepy. I’m not really sure what the story with that is. It’s kind of a strange thing when your body reacts. the opposite way to something that usually has a pretty good effect. Anyway, today’s video, we’re going to talk about why some indexes create far more slowly than others. Now, if you’re on standard edition, this likely does not apply to you because you cannot create indexes with parallel threads. You are limited to offline index builds with a single thread, and you’re not going to have these problems because you just don’t have multiple threads to see this sort of stuff. So, if you’re on standard edition, I don’t know, you can create your indexes really slowly with a single thread or you can just watch this video. I don’t know, like maybe install developer edition to see how grand being able to create indexes quickly or more quickly is usually.

Sometimes for the most part. Now, what we’re going to do is we’re going to start, well, I’ve already created them because, I mean, it does, if we look under the armpit down here, let me zoom in under the armpit. I want to make sure that you get the full underarm experience from me. Where’s that time thing? Where are you? Where are you hiding from me? There we go. One minute and 18 seconds to create all these. And I didn’t want to sit there and make you wait for these things to pop up. Before we look at the plans for these, though, what I want to point out is that there’s a link up at the top there at my website, different index build strategies for SQL Server. And that’s not a post that, I mean, I did write the post, but really it’s a collection of links from 2006, back when Microsoft actually wrote useful things about SQL Server, not just like bland marketing material about how new feature is going to drive modernization data, blah, blah, blah, blah, blah, blah, blah. This was actually useful technical information. Good stuff. So there’s like six or seven posts up there at that link to old Microsoft posts about different index build strategies for SQL Server. And some of them you’ll see in here if you read all that stuff.

This link will be in the show notes as usual. So I’ll stick that in the old YouTube description. And without further ado, let’s look at some stuff. So what I just want to get the first four things up here. There’s four in total. But we have four indexes that got created, two of them on the votes table and two of them on the post table. And what I want to show you really quickly before we move on is the columns that these indexes got created on. So the vote type ID column is very not selective. There are like majority of vote types or upvotes or downvotes.

There are some, there’s a decent amount of question marked or rather answer marked as green check mark the answer in there. But then there’s a bunch of other things for like spam, offensive, whatever. And so it’s just not a terribly selective bunch of data. The second, the second index we created was on a column called post ID and post ID is much more selective. Granted, there are some posts with way more votes than others. Right. So it’s like skewed data, but it’s pretty selective generally.

For the post table, we did almost the same thing. We created one index on a very not selective column post type ID because most, most post types are going to be questions or answers. And then there’s like a smattering of other stuff in the table as well. And the next one is on owner user ID. Now, owner user ID is pretty similar to post ID up here. And that, you know, there’s going to be some skew towards users who ask more questions or post more answers. But in general, this is a fairly selective column.

At the far outlier of this is John Skeet, who in the 2013 version of Stack Overflow has around 27,000 or so answers or questions and answers combined. I think mostly answers, to be honest with you. I don’t think John Skeet has ever asked a question from thinking about things logically. At least a question that was not rhetorical. He’s, you know, one of those. Maybe he’s asked, you know, maybe he’s asked questions of other people in interviews, but, you know, I don’t think he’s ever asked a question he didn’t know the answer to.

Fascinating, fascinating way to live life. So let’s look at kind of what happens in here. Now, we’re going to go get the properties of all these things because that’s where all the helpful stuff lives. And we’re just going to expand the rows red thing a little bit. We can expand this too, but it’s not going to really make much of a difference. If you’ve watched other videos of mine, you know that this is the number of rows that a thread handled, and this is the number of rows that a thread produced.

So if we had, like, we don’t have a filter on this index. We had a filter on this index. These threads might have produced much lower numbers. But since we don’t have a filter or anything on here, these threads up here will produce the same number of rows that were read down here. All right. So good to know. Good to know. Good things to know.

And I don’t know what that accent was. It was very nonspecific. I wasn’t making fun of anyone. It was just a voice that came out of my body. Maybe I’m possessed. Maybe I’m just exhausted. Who knows? But if you look at the sort for this non-selective query, this is where things get a little jangly.

All right. If you look at all this stuff, some of these threads handled way more work than others. All right. This one handled a whole bunch. This one handled, I guess, a decent one. This one handled the most, though. If we, like, drew a line down under this 8, let’s see if I can draw a straight line with this thing.

Pretty good. Not bad. I haven’t had my morning drink yet, so it’s a little squiggly. A little shaky. But if we look at this, like, this thread number one handled far and away the most.

Like, no other number is quite as long as thread number one. And then thread number three did, like, nothing. And some of these handled, like, way fewer rows than others.

And that almost matches the distribution of data in the column. And I’m going to show you that in a second. And then if we look at the, let’s stick with the sort, because the sort seems to be where the interesting stuff happens.

And here, if we look at the sort for the index that got created on post ID, the numbers are much, much more even in here. All right. This is a much easier distribution of data. And if we pay attention to the times.

Go away, tooltip. No one needs your nonsense here. It took 40 seconds to create this index that leads with vote type ID. And it took about 18 seconds to create this index that leads with post type ID.

This is the same number of rows going into there. Right. There’s no filter on either of these. And the only thing that’s really different is the distribution of data.

Right. Like, even if you think about it, vote type ID is an integer. But it’s only ever, like, I think there are only, like, eight vote types. Right. So, like, you really only have the number one through eight.

If you have post ID, it’s also an integer. But it’s, like, you know, far bigger integers. So it’s not like there’s a, integers are all four bytes anyway. So it’s not like there’s a big difference in, like, the type of data we’re creating the index on.

It’s just the distribution that makes creating some indexes a lot slower. Right. And we’ll see almost the same pattern if we look at what happened in the index creation for the votes, for the post table. Sorry. If we look at what happened over here.

Holy cow. We only use three threads. And look at this distribution. You could think of that and that as questions and answers. And this is everything else.

Right. So we have about six million questions in the post table. They all ended up on one thread. We have about 11 million answers in the post table. They ended up in one thread. We have about 50,000 other things in the post table. And they all ended up on a third thread.

Now, you might be looking at this and saying, why in God’s name did SQL Server only use three threads to do this? Why wouldn’t we break these things up further? Why wouldn’t we use more threads?

Why wouldn’t we do that? And so you might even think about doing something insane like adding a max stop 8 hint to the index build. The index create script, sorry.

And you would be sorely disappointed to learn that max stop is not min dop. Now, it’s a short digression here. I was on a customer call recently where they had a support ticket open with Microsoft.

And when I say with Microsoft, I say that very loosely. Because Microsoft support is not all just Microsoft employees. Microsoft farms out support to like two or three other companies.

And this was a gentleman who worked for one of those two or three other companies. And the problem generally was that, well, the customer really wanted to get a parallel execution plan for this one query. They didn’t want to change any settings.

They didn’t want to add any hints to the query. They kept, you know, seeing all this stuff. Well, we want to get a parallel plan. And the gentleman from the third party support group working for Microsoft kept telling them, well, just try it with a max stop 8 hint. And I kept having to tell this gentleman that max stop is the maximum dop, but it is not the minimum dop.

If you can add a max stop 8 hint to anything, it’s not going to make that query go parallel. It’s going to tell SQL Server that that query can’t go more parallel than 8. And he refused to believe me.

Now, I’m just going to throw this out there. If you work for a company that’s paying Microsoft for support and you’re unhappy with it, you should talk to me instead. Because, at least for the client that I was working with, they paid $75,000 a year to Microsoft for support.

And this is the type of person who they would get on a call with. Someone with about 18 months of experience with SQL Server doesn’t know their butt from their elbow. I’m going to keep that one family friendly just in case you want to show that to your boss.

And they just don’t know anything. They’re, again, like 18 months of experience max with SQL Server. So, for about the price of one, for a little bit less than the cost of one core of Enterprise Edition, you could have a whole lot of help from me who actually knows something about SQL Server.

Wouldn’t that be grand? So, if we look, so this query, sorry, this index create only uses three threads, which is a little depressing. Max stop 8 doesn’t help because it doesn’t, again, doesn’t set the minimum dop.

There’s no min dop hint, much as I wish there was a min dop hint. We don’t get that. There’s a trace flag and there’s a use hint.

They’re both still technically, like, legally unsupported by Microsoft. But they do work, but they still don’t set a minimum dop. You can use the trace flag or the use hint with a maximum dop, but there’s no, like, you must use eight threads for this.

So, that’s a little bit silly, ain’t it? Anyway, if we look at the second index create for the post table, again, much more evenly distributed. Right?

Everything in there, pretty evenly distributed. Look at all those 21s all the way down. Very, very nice. And there’s, again, a pretty significant timing difference between creating the index on non-selective data versus creating the index on selective data.

Right? Now, there’s a big difference between the votes table and the post table. The votes table is about 53 million rows. The post table is about 17 million rows. So, there are significant timing differences between the two tables.

But within the two tables, creating the indexes with a non-selective leading column can really increase the amount of time you spend building that index. First, creating the index on a selective column first because you just get better distribution in there.

Now, to kind of round things out with this, for this video, I want to show you a couple things down here. Now, when we looked at the thread distribution for the non-selective columns in both the votes table and the post table, this is what the runtime counters per thread looked like.

Right? And I’ve omitted thread zero here because thread zero did not receive any rows. Thread zero generally does not receive any rows. And if we run this query, and we tuck this down a little bit, you’re going to see some numbers that look kind of familiar down here that you also see up here.

Now, I don’t have it completely mentally mapped out in my head, as I probably should. Let’s tuck that up a little bit. Oh, not that high.

Come on. Get down. There we go. Now we can see everything. But if you look… Oh, that didn’t do it. Come on, baby. If you look down here, we can see 733 here. And we can see 733 here.

Let’s see. There’s a 3511 733 here. There’s a 3511 733 here. Let’s see.

Do we have a 41? No, we don’t. Do we have a 203? We do. There’s a 2039371 there. Yeah, there’s a 2039371 there.

And then, you know, there’s a… Let’s see. Do we have an 818? Do we have an 818? Do we have 16? We have an 818477 up here. Look, this is very exciting stuff.

We have an 818477 up here. We have an 818477 right here. So there are a bunch of these threads that only got to work on like a single vote type ID. Some of them spread out a little bit more.

And some of them, you know, just kind of did their own thing. I think… Oh, there’s another good one. Look at this. 3-5-7-3-4-5-0.

3-5-7-3-4-5-0. So you get… Some of these threads did work on just one specific vote type ID. Other ones, you know, again, SQL Server kind of spread that out a little bit.

So that was nice of SQL Server, I suppose. Now, where things get interesting, too, is… And I kind of…

I kind of spoiled this one earlier when I talked about it, but we’ll do it anyway. If we were on this query on the post table, and we look at the post type IDs versus the actual rows, here’s post type ID 2 with 11 million.

Right there. Those are all your answers. There’s post type ID 1 with about 6 million questions. And there’s that there.

And then if we look at this number, 50597, I bet that would just about add up to what you have in here. Right?

So we’ve got a couple 25,000s plus a little. We’ve got a 167, a 166, a 4, and a 2. We’re going to bet if you added those numbers up, they would add up to 50597. So the three threads in here did sort of an unfortunate amount of work.

Like, I think what’s unfortunate about it is, like, if you think about what happened up here, like, SQL Server broke up, like, some of the bigger vote type IDs across multiple threads. It did not do that down here.

Right? We only got three threads. Right? Maybe, like, a fourth thread could have evened this out. And then we could have had, like, a 50,000, and then, like, a 5.5 million, and another 5.5 million, and then a 6 million.

That would have been a little bit nicer. But SQL Server did not choose that. So anyway, if you’re ever creating indexes, and you wonder why some indexes kind of create slower than others, this might be why.

You might be creating some indexes with leading non-selective columns, which sometimes you’ve got to do. Sometimes that’s the wise thing to do. Sometimes that’s what your where clause is on.

You’ve got to respect that where clause. And other times you might be creating indexes on fairly selective leading columns, and you might think, wow, that index created a lot faster.

And this would probably explain why. If you’re ever incredibly curious, and you get the actual execution plan for your index create statements, you might see stuff just like this, very uneven row distributions across threads.

Maybe, like in the case of the post table, you might not see very many threads involved at all. And that could also be part of it. And remember, especially if you are working for a Microsoft third-party support vendor out there, MacStop is not MinDop.

And again, if you are overpaying Microsoft for terrible support, I’m your mans. I can certainly do better than add a MacStop hint to make a query go parallel, because that’s a sure sign of a lack of knowledge.

I hope you enjoyed yourselves. Lord knows I did. I think I kind of pepped up a little bit as I was talking.

Maybe just sitting at my desk was what was making me a little sluggish feeling. Anyway, I hope you learned something. If you like this video, I do like thumbs-ups in appropriate places, and I do like nice comments.

And if you like this sort of SQL Server content, if you like learning more, if you want to know more about SQL Server than Microsoft Support does, and you want to keep watching these videos, a great way to get notified is to subscribe to my channel.

If you do that, you will join… Hang on, I’ve got to get the official number as of this recording. You will join nearly 3,574 other people and celebrating every time I post a video.

That would be fantastic, wouldn’t it? Wouldn’t that be just lovely for you? You wouldn’t have to keep refreshing the page. You would just get a little notification that said, Erik Darling did a thing.

And then you would be able to watch the thing. And you would be a smarter, happier, more well-informed person for doing so. Anyway, I’m going to…

I’ve got stuff to do. Actually, I’m finally getting a haircut in about a half hour. So I should probably prepare myself for that eventuality. And in the next video I record, I’m going to not look like some sort of, like, I don’t know, weird nerd.

It’s a curly Q thing over here. I don’t care that I’m… I don’t care that my hair is thinning. I care that my hair waves when I don’t want it to. I’m in my mid-40s.

My hair is probably going to get thin unless I intervene in some way. And I’d rather just shave my head. And the reason I’d rather shave my head because when you’re a guy with a shaved head, when it’s not due to illness, there is a tremendous amount of responsibility on you to maintain a reasonable weight because you don’t want to have a big face with a shaved head.

At least I don’t. It makes me look very… I look like Dr. Evil if I get chubby with a shaved head. So you don’t want to see that. So anyway, I’m going to go do my self-improvement stuff and I will see you in the next video.

Thank you, truly, from the bottom of my heart, for watching.

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.

How Poor Cardinality Estimates Can Lead To Worse Blocking And Deadlocking In SQL Server

Posted on June 12, 2024January 22, 2026 by Erik Darling

How Poor Cardinality Estimates Can Lead To Worse Blocking And Deadlocking In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into how poor cardinality estimates can exacerbate locking situations in SQL Server, particularly when dealing with updates and modifications. I demonstrate this through a series of queries and stored procedures, showing how bad cardinality estimates lead to excessive locks on the entire table instead of using an index effectively. By creating realistic examples and explaining the nuances of cardinality estimation, I highlight the importance of ensuring good indexes are in place and the potential pitfalls of using local variables. The video concludes with practical advice on fixing these issues, including the use of option recompile hints and parameterized dynamic SQL.

Full Transcript

Erik Darling here with Darling Data, trying to talk in a way that it would be really easy to train the AI off of. Hopefully someday I will be AI training worthy. Training AI worthy? I don’t know. Whatever they call it when AI steals your stuff, I guess. And in today’s video, we’re going to talk about how bad cardinality estimates can make locking situations worse in SQL Server. Alright. So, hope you’re ready. I realized that I don’t have a good affectionate name for all my viewers out there, my watchers. Maybe watchers is the right word. All 3,562 of you as of the recording of this video. Who knows? Maybe that will go up while I’m recording. You never can tell. I don’t want to call you my data darlings. That might get me in trouble with the misses. And I don’t want to call you data heads because it sounds like I’m about to say something a little bit more rude. So, we’re going to, I don’t know, we’re going to have to think about that. If you have a good idea, leave a comment.

Because I would love to know how you would like me to refer to you. Because I can’t possibly learn all your names. So, we’re going to have to come to some sort of group descriptor. Anyway, I’ve got this lovely index on the post table. And, you know, it’s good enough to prove a point. It’s not anything overly fancy. As soon as you get too fancy in demos, they start failing and people start staring at you like you’re an idiot. And what I want to show you is, well, how bad cardinality can make locking worse in SQL Server.

So, this is an easy one for SQL Server, right? This query right in here, very easy. We’re going to run this and we’re going to roll it back immediately because we don’t need to keep any of these changes. And I’m going to run this update in the transaction. Within the transaction, I’m going to select data out of my little helper function there called What’s Up Locks. It’s available at my GitHub repo somewhere. And what this is going to do is show us all the locks that were taken by this update.

All right. So, we go and we run this. Lo and behold, it runs pretty quickly. And if we zoom in over here, let me frame that up real nice for everyone at home. We see this request mode column right here. But notice only one of these rows has an X by it in itself. All right. That means this is the thing that actually took the locks. And the thing that actually took the locks was on 167 keys.

All right. So, that’s pretty easy. It’s pretty low, pretty lightweight. All right. It’s a serviceable number of locks. This thing finished pretty quickly. We didn’t have to worry too much about anything at all. All right. Pretty okay in here.

All right. And let’s go back to the query plan real quick. SQL Server started with a seek over here. And started with a very good cardinality estimate of 167.

Now, the thing that is important to note here is that when you start with a seek, you are most likely going to start with key locks. You start with a scan, you are most likely going to start with page locks. If you, I don’t know, I don’t think you can really start with much of those.

Unless you’re using a heap or something. But rid locks, maybe. Get some rid locks in your life.

And so, sort of generalized sort of advice there. The storage engine sees seeks low number of keys. Says, hey, key locks. And then from row or page, you might move up to an object level lock, which we’ll see in a minute.

But you will not go from row to page to object. You just go from row or page to object. Assuming that SQL Server finds valid reason to engage in an attempt at lock escalation and is successful.

If there are any competing locks on the table, it may not be successful. Now, the thing that almost no one, well, let’s see, what’s a good way to put this? The thing that almost everyone takes for granted is that their end users are not walking data dictionaries.

They do not have numerical meanings for different things printed out at their desk. We’re going to just look stuff up to make your job easier. PostTypeID equals three isn’t going to mean much to anyone at home.

No one’s going to memorize all the different post types in the post types table. It’s just not a thing that they’re going to do. So what they do know, usually, is the type of post that they want to find.

It could be question, it could be answer. For the sake of this demonstration, we’re going to be looking for wiki posts because those hit a relatively small number of rows. Posts and questions are, like, most of the table.

All the other things are the rest of the table, but, like, there’s 17 million rows in the post table. Like, 6 million are questions. Like, 11 million are answers.

And there’s, like, a few hundred thousand of the other stuff. But look what happens with this query. This is a real gosh darn shame what happens here. This is not a very quick finishing query at all, is it?

Not at all. It’s just four seconds. Right? Well, actually, how long did that take? Well, it was 87 milliseconds.

What’s your problem? What’s your gosh darn problem? Right? 1.8 seconds in here doing all this stuff. And, of course, 1.8 seconds over here.

Notice that we did not use our nice narrow little index that we created on the post table, on the post type ID column. We ignored it. SQL Server says, no, I’m not using that index.

Because I don’t want to do key lookups. If you want me to use this index, you have to put the column that you’re updating in the index. Good luck with that later.

We’ll see how that goes. But the reason why SQL Server doesn’t use it is because SQL Server makes a real crappy guess at cardinality. Right?

If you kind of look a little bit more closely about what happens in this query plan, this parallel distribute streams uses a partitioning type of broadcast. What broadcast means is that this one row gets sent out to eight threads because we’re running it max.8.

Right? We get one row from here. This thing turns that one row into eight copies of one row. And then when we come down here in the clustered index, we have some…

Let me get both of these things open so you can see a little bit better. There we go. We have eight threads in here that act cooperatively to scan all 17 million rows.

Right? These numbers in here will add up to 17 million. And then up in this section, we have the actual number of rows that got produced by each of those threads after that post type ID filter was applied. So, you know, there’s a decent spread here.

Nothing’s too, too off. I guess the 11 is a little bit low. But 30, 11, 19. This will add up to the 175 rows that we get here. So, all well and good.

And then when we finally do our join over here, that gets whittled down to 167 rows. Right? So, a lot of extra work.

And what’s kind of funny is that even if we tell SQL Server to use our index, right? If we say SQL Server, we created an index. It’s perfectly usable.

You’re being a goofball. Use our index. We still get, well, actually, you know, I should repeat myself. But if you were paying close attention to the output of this from the first demo, this does indeed lock the entire table. Right?

So, we get still over here a rather poor cardinality estimate down here. 167 of 2142770. So, that’s a seven-digit number.

So, I think 2.1 million rows are going to get hit over here. So, we don’t use it. And, of course, when SQL Server is like, holy cow, that’s a lot of locks. It escalates those up to the object.

And I’m just going to, just to make sure that you don’t think I’m being goofy here. I’m going to, that query did run a lot faster. It still took a lot of locks, though. So, if we rerun this, this is the one that takes about two seconds or so, I guess. De-da-de-dee.

This one also locks the entire object. We have this X locked. Let X lock at the object level. We lock the whole gosh darn thing. So, in practice, a lot of people will experiment with joins, with modifications. And, that’s not maybe so great.

Xist tends to work a bit better, unless you need to, like, join a table to another table to update the columns in one table to the columns in another table. Then, Xist does you no good.

But, like, in this case, we could use Xist. Maybe it would turn out a little bit better. But, that’s usually not the way most people are going to write that query the first time. Now, going back to our users not being data dictionaries, right? What we’re going to do is we’re going to create a store, I’m going to say, an incredibly realistic store procedure.

It’s like uncanny valley levels of realism for this store procedure, where we’re going to ask our users to supply a post type. We’re going to look that post type up for them.

And, then we are going to do an update based on the post type that we find, the post type ID that we find for them, right? So, let’s create this store procedure.

Let’s make sure we have this created, because there’s another copy of that that has a little fix for it. So, if we run this, and it’s going to do roughly the same thing as all the other ones, what do we get?

We get this big honking object level lock with the local variable in effect. The reason why is because SQL Server makes terrible guesses when we use local variables. Whomp and whomp.

167 out of 2.1 million. And, again, we are not using our nice narrow nonclustered index. We are using our big honking clustered index. All right.

SQL Server has said no. No to the nonclustered index. Yes to the clustered index. And SQL Server is once-ing again. Once-ing. Ah!

It’s a good time. Once-ing. Where’d that come from? Once-ing again.

Asking for an index that not only leads with our post type ID column, but also includes the column we are attempting to update, which is maybe not the greatest, the grandest of ideas.

So, of course, you know, local variables cause all sorts of problems. You may run into cardinality estimation issues for all sorts of other reasons, but local variables are just a very easy and convenient way to show you how crappy cardinality can get when you use them.

Of course, there’s a very easy fix for this, right? And what we’re going to do is just create or alter our store procedure, and we’re just going to stick an option recompile at the end, right?

And I just want to show you the difference here. It’s not really anything incredibly groundbreaking. Yeah. Local variables, option recompile.

That’s like the first step in the decision tree. Figure out how bad this thing is. Just run and do that. All right. So if we run this, what we’ll see is the same behavior as the, when we used a inlined literal value where we have the 167 exclusive locks, sorry, 167 exclusive key locks here, and then, you know, some other intent exclusive locks and other places that don’t really do anything because they don’t actually take the locks.

The only one that actually takes the locks is the one that has X by it. So when you’re writing modification queries, be very, very careful. Make sure that they have good, make sure that you have good indexes in place so that your queries can find the data they’re looking for to modify.

If you find yourself needing to use local variables, some ways to fix problems with them are, of course, option recompile hints using parameterized dynamic SQL or an enter a store procedure call to a store procedure that will actually do the update because those will treat whatever local variable you create outside of them as a parameter when you pass it into them.

If you’re using table variables for whatever reason, you know, they’re in memory only, right? Just try using a temp table instead. Usually get better cardinality estimates.

Table variables don’t get any sort of local histogram to the data that shows the data distributions in there, and that can cause some pretty big problems when you start joining them off to other tables. If you have, I don’t know, poorly written queries, overly complex join and where clauses, maybe out-of-date stats, you can hire me to do most of that stuff.

I’ll even update statistics for you if you feel like you need me to. One thing that is sometimes good to mess with, it wouldn’t, I tried it every which way in this demo, but sometimes changing the cardinality estimation model where your queries can be useful.

You have the new one and the legacy one. I have a strong preference for the legacy cardinality estimator for most of the things that I do. A lot of the demos that I write are using the new cardinality estimator where things just, you know, fly off the rails in a lot of ways.

Then there are other things that you might be doing in your queries that the optimizer does not reason terribly well with. If you have scalar-valued functions in a where clause or a join predicate, or if you have multi-statement table-valued functions with the return-a-table variable, you’re cross-applying or joining to those, you can hire me to rewrite those because I do that for fun.

Money. I do that for money so I can have fun. Keep. And of course, if you are in the midst of a modification query, if you are shredding XML or JSON and attempting to use some sort of join or where predicate or some sort of isolating predicate, you can also hire me to fix that because I do that sort of stuff also for money fun.

So anyway, we’ve learned today that poor cardinality estimates can lead to more intrusive locking.

Don’t let the intrusive locking win. Fix your queries so that when you modify data, you take as few locks as possible. You don’t try to escalate those locks all the time.

And then you cause all sorts of blocking and deadlocking issues. Of course, if you have all sorts of blocking and deadlocking issues, you can also hire me with money to fix that so I can have fun doing this.

All right? Good deal. All right? Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll hire me.

I don’t know why I’m pushing that so hard. I don’t know. It’s like I have vacation coming up. The more people who hire me, the harder it is to take vacation.

If you like this video, thumbs ups are nice. Just make sure that you put them, you thumbs up somewhere appropriate.

Nice comments are nice. Do you like those? Kissy face emojis. Always a winner. And if you like this sort of SQL Server content, you can subscribe to my channel.

So that, hold on, let’s drum roll this. So that you can join nearly 3,563 other lucky people who get notified when these videos are published.

So, yeah, that’s all that. Anyway, I’m going to go work because fun is over. I’ve had my designated playtime.

I’ve got my yard time today. So now it’s time to go back to work. Thank you for watching.

Going Further

A Little About Fill Factor And Fragmentation In SQL Server

Posted on June 11, 2024January 22, 2026 by Erik Darling

A Little About Fill Factor And Fragmentation In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the often misunderstood concepts of fill factor and fragmentation in SQL Server indexes. Drawing from my experience working closely with clients who rely on outdated practices, I share how setting a 32-bit mindset aside can lead to more efficient database management. I explore why regular index rebuilds might not be as necessary as many believe, especially given modern hardware capabilities that reduce the impact of fragmentation and improve overall performance. Through practical examples, including queries and index maintenance scripts, I demonstrate how fill factor settings can significantly affect both logical and physical page density, and explain why focusing on densely packed pages is crucial for optimal query performance.

Full Transcript

Erik Darling here with Darling Data. Two big capital D’s on the Darling and the Data. Just in case anyone at home was wondering. I’m a little blurry here. Is that too much? That’s too much. There we go. Now we’re nice and crisp. Now you can see the slight blemish on my face, which I’m very embarrassed about. Anyway, in today’s video, we are going to talk a little bit about fill factor and fragmentations. Because I had to talk about this with a client, the nice people who pay me to keep producing this free content. If you would like to hire me so that I can share my wit and wisdom with you and keep making free content, well, you can go to my website and contact me. I suppose that’s probably the best way to do it. I don’t know. I don’t take PayPal or Venmo or Bitcoin or really anything else. Just cash. Cash on the barrel head, pal. So this is a tricky subject because you have to talk a lot of people out of having a 32-bit mindset when it comes to these things.

Because unfortunately, it seems like everyone got their advice about things like fill factor and fragmentation from 20 years ago. And they just refused to change their mind about it. They cling desperately. They bitterly cling to these notions that these are still terribly relevant metrics to apply to SQL Server from a query performance perspective. And there are lots of times when I say to people, well, who’s responsible for tuning indexes at this company? And they say, oh, we have a script that rebuilds them. And they say, great. So who’s responsible for tuning indexes at the company? Because rebuilding indexes is not tuning indexes. Neither is reorganizing indexes, to be quite frank with you. So I’ve got this query here. So I’ve got this query here, which is going to show us all sorts of detailed stuff about a particular index that exists on the users table.

And I’m going to show you some funny things about the old frog lamentation. So my learned opinion, my experienced opinion, which, you know, perhaps anecdotal, but, you know, after many years of anecdotes, I’ve kind of just come to the conclusion that I’m right, is that index rebuilds should be reserved for special circumstances. Like, let’s say that you’ve deleted a lot of indexes. Like, let’s say that you’ve deleted a lot of data from a table. You may want to consider rebuilding indexes to densify all your data pages.

If you need to change something about the indexes. Some index changes do require rebuilding to apply those changes to them. And I guess it’s not technically an index, but if you have a heap that has a lot of forwarded fetches or has a lot of deletes against it, you may want to consider rebuilding that heap to get rid of forwarded record pointers and potentially empty data pages. Remember that when you delete data from heaps, empty data pages are not guaranteed to be deallocated from them.

In that situation, you probably just want to create a clustered index, though, anyway. Now, I started off the way a lot of other people did with the index maintenance stuff. And I was like, you know, we’re going to do it. Ride or die, we’re going to do it.

And so, you know, this was back around, oh, I don’t know, 2008, 2009. Or I don’t know, I guess given computer hardware back then, it made a little bit more sense. But, you know, there was a lot of talk about fragmentation.

And, you know, a lot of people sort of seemed to all say that it was bad. And so, you know, I didn’t know anything. So I said, you know, if all these smart people are saying it’s bad, it must be terrible. So I’m going to go ahead and rebuild this stuff.

And, you know, I guess without really any sense of how to measure for improvements or anything like that, it just seemed like, well, it seemed like it’s maybe, maybe it’s fixing stuff that I don’t know about. Maybe if I stopped doing it, I would just suddenly start having all these problems.

And I would have to start doing it. And I would feel like a real eggy-faced idiot because I stopped doing it and I introduced problems into my SQL Server workload. And then, you know, slowly kind of coming to the conclusion that even though I was doing these index rebuilds regularly, I was still having all sorts of regular performance problems.

There were things that would still go wrong. I would still have slow queries. I would still have all the normal stuff that goes terrible with a generic SQL Server workload. There was still lots of blocking.

There was still lots of deadlocks. There was still parameter sensitivity. There were all these things that would happen regardless of my index rebuilds. And, you know, eventually databases started getting to kind of a size where I no longer had time during my maintenance windows for important stuff, like running dbcc checkdb.

And so, you know, you would kind of reschedule things a little bit. Stop. Maybe stop rebuilding, like, indexes at such low thresholds, like 5%, 30%. Maybe crank those up a little bit and maybe do some other stuff.

And then the less and less that I re… Well, I’m going to stop saying rebuilt. Less and less I did index maintenance, the more I realized that no one was actually complaining about anything new.

I still had all the same problems, but I had more time to do important things. And that’s kind of when it started kicking in that maybe index fragmentation is not my problem. Now, the reason this is 32-bit mentality is because when SQL Server was a 32-bit piece of software, if you can remember back that far into the years, the problem that you had was that you had, like, two or three gigs of memory available for user space stuff.

Now, granted, maybe not so many databases were so much bigger than two or three gigs way back then, you know, or tables or whatever. But that just wasn’t a lot of space for the buffer pool and all the other memory stuff that SQL Server has to do.

And worse, the storage, you know, the often direct-attached storage that was backing a lot of SQL Servers had not yet reached the flash and SSD realm of things. And so you had these spinning disks that just went round and round in circles.

And then if you were doing sequential I.O., all was fine and dandy. But as soon as you had to do random I.O., it was like a record skipping all over the place. And you had to have a little disk head jump up and down and all around in order to go find that data.

And, of course, when there’s a physical movement of a drive, when things have to really shuffle around to do random I.O., well, I mean, there’s certainly a penalty on that.

You don’t have those sorts of penalties with flash and SSD, and you certainly don’t have that penalty when you have a good amount of RAM on your system. So I’m going to talk about something that is connected to all that.

And that’s something that is another 32-bit piece of mentality, in that I still run into a lot of people who set, like, just without any testing or without any, like, real forethought or, you know, really any sort of idea of what the hell they’re doing, will just set fill factor lower and lower.

The even funnier thing that I run into is a lot of people who get fill factor reversed in their head. And they’ll think, I want to leave 20% free space on a page, so they set fill factor to 20, thinking that that will allocate 20% free space, when really it allocates 80% free space.

And so let’s look at some of this stuff. So I created this index on the users table on the reputation column. And we’re going to save these queries for a minute, for a minute from now. And I’ve cut out some of the numbers in between here.

I used to do 5, 10, then count by 10s up to 100, but you just don’t need to stick around for all that stuff. I’ve already been talking for, like, eight minutes, and you haven’t seen any action yet.

And gosh darn it, you deserve some action. Deserve all the hot SQL action you can get. So what I’m going to do is I’m going to rebuild this index on the users table with a fill factor of 5. That’s going to take a second to run.

And if we look at what this query returned before, average fragmentation in percent was 0, and average space used in percent was 99.9, with a lot of numbers after it.

Right? So pretty densely packed pages, doing pretty good there. Pretty happy with that. Now, if we rerun this, if we rerun this query, after setting fill factor to 5, this is very interesting.

In fact, this is one of the most fascinating things that you might ever see in your entire life. Average fragmentation, logical fragmentation, pages being out of order, is incredibly low. But look at the average space used in percent.

It is no longer 99.9, a lot of other numbers. It is 5. So one of the things that I want you to take away from this is that, you know, a lot of people will say, like, oh, well, you know, I’m going to run my index maintenance scripts, you know, once a week or once a month, just to, you know, catch stuff that’s fragmented and rebuild it.

The problem is, you’re not guaranteed to fix the right kind of fragmentation with that mentality. The right kind of mentality is that you should be checking for data pages that are not very densely packed. And you should be looking at those instead.

Because average fragmentation of percent, that’s logical fragmentation. That’s pages being out of order. That has nothing to do with your queries being slow. Average space used in percent can sometimes have an impact on queries being slow if they’re scanning entire indexes.

And I’ll show you exactly what I mean by that. So let’s come back over to this window. And let’s look at these queries.

So we run this. And we look at the results. These queries are very carefully put together to hit 1%, 10%, and 100% of the table.

Right? And if we look at the query plans for this, something kind of funny happens. The first two queries that do seeks are very fast.

There is nothing slow going on here. Then we have this query down here. And there’s something different.

There’s something different about this query. Notice we have an index seek into a non-cl index up here. But here we have a clustered index. Yes.

SQL Server has looked at the size of our nonclustered index and said, that’s huge. That’s bigger than the clustered index. I’m not going to use that one.

I feel very silly using that one. But we can remedy that. We can remedy that. We can fix that, you and I. And we can set fill factor now to 50%, which will reduce the size of the index.

One thing I want to point out over here is the number of pages in this index is 47,277, because we have 95% free space on every single data page. Right?

But if we run this and we set fill factor to 50, we’re going to cut the size of our index by, well, let’s just say in half, because that’s close enough. But look how many pages we have now. We went from 44,000 to 4,800.

Right? Which is pretty good. But these numbers are still very funny to me, because average fragmentation in percent, we are logically very unfragmented, but we are still physically quite fragmented.

And where the physically quite fragmented thing might come into play for you is with your buffer pool. Right? Because your buffer pool is where all these data pages get cached. And if you have a bunch of half-full data pages in your buffer pool, you may not be making the best possible use of your buffer pool.

You may be overly polluting your buffer pool. Now, granted, this is only about 5,000 pages, which is not the end of the world. This is a small index, small table. But this is what we’re going to work with, because I can run the rebuilds to different fill factors faster, so we can get through this video in a reasonable amount of time.

So, now with fill factor at 50%, fill factor at 50%, let’s run these queries again. Let’s look at if they’re any faster or slower.

Well, I don’t know. This one’s still at zero seconds, so this seek to a single row is still fine. This one’s a couple milliseconds faster, so reading, you know, 5,000 versus 40,000 data pages from memory didn’t really mess us up, did it?

It didn’t really do anything. This one, a little bit faster. Few fewer milliseconds on that one. But the big thing here is that when you’re seeking to data, index fragmentation and even page, well, rather, let’s just say, when you’re seeking to data, page density makes a little bit less of a big deal, especially for, you know, when you’re seeking to, like, small chunks of data.

If I were doing an index seek that read the whole table or whole index, it’s a different matter, but, you know, we don’t need to talk about that here. We’ve talked about that before in other videos.

So, now, it’s fairly rare for indexes to become physically fragmented to this point. But I would say that if you do find that you have indexes becoming…

Did that actually show up on the screen? No, it didn’t. I had some weird pop-up to show up, and it was very, very strange to me. But let’s say that you were…

If you were to run a query like this to look for indexes that are physically fragmented to that degree, and you found some, and they were on big tables.

I don’t mean little tables, like 5,000 pages. I mean big tables, like 50,000. I don’t know. Maybe is 50,000 even that big of a deal? Probably not.

500,000, probably. 500,000. If you had big tables with… And the page… Average page space used in percent was at, like, 50% or lower, I would probably encourage you to do something about that.

They may end up at that point again because of the way that data sort of tends to get naturally distributed in indexes over time. But it’s a good idea to fix that every once in a while, especially just to see if it comes back.

If it comes back, you’re wasting time by rebuilding it. If it doesn’t come back, well, then something weird happened at some point. Maybe you did do a big delete. Now, so let’s do this again at 80%, right?

And we fill this up to 80%. We’re going to reduce the size of the table again. So from 50 to 80, we’re going to go from about 5,000 pages to about 3,000 pages. We are now 80% packed full, and we still have very, very low physical fragment…

Or low logical fragmentation, right? We still have a bit of this, right? So I can’t remember if I stated it explicitly before, but all your index maintenance scripts out there in the world, it doesn’t matter if it’s OLA or whatever, are going to look at this number.

And this number and this number do not necessarily track. You can have completely out-of-order data pages that are densely packed full. Likewise, you can have data pages that have, like we just saw, 95% free space on them that are completely logically ordered correctly, right?

So all your index maintenance scripts that go and measure that average fragmentation percent number aren’t going to tell you about the page density number.

They’re not going to tell you about that. You’re not going to be affecting the right stuff. You have to very specifically go and look for this stuff if you want to fix anything of any meaning. So now let’s come back over here, and let’s look at these queries again.

I don’t know why I have the set statistics time stuff. I’m not looking at it at all. Maybe I was just looking at it. Maybe I was looking at that while I was testing things. I forget.

So now this one is still at zero seconds. This one improved by a couple milliseconds. Not anything to write home about. This could be like Windows Update was checking for something at the same time as the last one.

That’s how few milliseconds this thing really improved by. This thing got better by, I don’t know, maybe 10 milliseconds. I don’t even remember at this point. Eight or nine somewhere.

There wasn’t a big difference. And then if we bump this stuff up to 100, and we go look at things, and then just wait for that 20% to disappear, and we run this again, we’re now down to 2,400 pages, right?

Which about makes sense, because when it was at 50%, it was like 4,800 some odd. Now we’re at 2,400 some odd. And now we have very, very good page space used.

And we always had no logical fragmentation, because our pages were already in very good order. They were all very orderly pages, but that didn’t tell us when we had a lot of free space on them.

All right, and if we come run these queries again, we go and we look, and we see what is happening here. This one is still at zero.

This one is still at about 16. And this one is still in the 160s per millisecond. So changing the index fill factor, making SQL Server read more data pages from memory, didn’t really improve much.

It didn’t really mess with anything all that big. Again, this is a smallish table, so we don’t expect to see big drastic shifts and stuff going from like 40,000 pages to 2,000 pages. It’s just not that difficult on a modern CPU to read those different things.

So if you have real big tables, real big indexes, and you want to find some meaningful metric to maybe take some action on those indexes with, you’re going to want to look at this number right here.

You don’t want to look at this number. This number is out 32-bit mentality, that number. You want this number in the rectangle. You don’t want that number with the, I don’t know, sort of weird text drawn on it there.

There. So, some other, another funny thing about fill factor, there’s lots of F’s in these fill factor, fill factor facts, is that SQL Server does not respect fill factor when things are just in their normal operating window.

Fill factor gets set when you create an index, fill factor gets applied when you rebuild or re-organ index. For the rebuild, you can change fill factor too, which is nice.

But otherwise, SQL Server is just going to jam those pages with whatever data it can. It’s going to split pages whenever it needs to, and all that other good stuff. So, like, SQL Server is not pausing the middle of your workload to say, wait, wait, wait, wait, wait.

Eric said this had to be 80% fill factor. Stop filling up that data page right here, right now. Leave 20% full, go on to the next one.

SQL Server just doesn’t do that. That would be a complete waste of time. So, the main metric that will go down if you rebuild indexes that have low page density is reads.

Like I’ve talked about in other videos, logical reads are not a great metric to judge query tuning by. Right. CPU is, right? CPU is what you can tune to bring your cloud bills down.

CPU is how you can get your company to spend less money on the cloud and maybe even give it to you in the form of a raise or a bonus. I don’t know.

Maybe an underling. That would be nice too, right? Maybe you’ll get a secretary out of the deal. But CPU doesn’t really change much at all when you do this stuff.

Changing CPU is more making sure that you have the right indexes and you write your queries in ways that can take advantage of those indexes.

Sometimes even getting batch mode involved is a good thing there. More efficient CPU use. So sometimes when it might make more sense to rebuild than others, if you are somehow on old spinning disks in the year 2024, perhaps logical fragmentation would be an issue to you.

But if you’re just rebuilding indexes to try and fix a problem, you are unlikely to find any joy in that outcome.

It’s also very unlikely that setting fill factor to a lower number is going to have any meaningful difference on your workload. Everyone has a story from 10, 15 years ago about, Oh, I solved this problem by lowering fill factor and preventing page splits and blah, blah, blah, blah, blah, blah, blah, blah, blah.

These are like old war stories. It’s like, I don’t know. It’s like, you said that, I don’t know, you rode a horse and you stuck a spear down the barrel of a tank’s cannon and the tank blew up.

Like, well, good for you. Nowadays, you would probably just have another tank and shoot the other tank. You wouldn’t, you would have a drone and drone strike the tank.

You wouldn’t, you wouldn’t be riding a horse up to a tank because that is not a very modern approach to warfare. Just being honest. It’s not. Kind of ridiculous. So, like, yeah, way before, you know, SSDs and stuff, maybe, maybe stuff like fill factor was a good idea and maybe stuff like logical fragmentation was a good thing to look at, but now it’s just, it’s just not where anyone should be focusing their time.

If you want to look at the big problems your workload is having, you have all sorts of better ways to do that. You have all sorts of better ways to solve those problems. Right?

There’s, there’s just, there’s, there’s no real replacement for good query and index tuning. It’s all, rebuilding indexes does not tune your indexes. It just wastes time and burns your SSDs out faster.

So, when you, when, when you’re looking at your servers or when you’re talking to various support outlets, maybe about a third party application and they start haranguing you about index fragmentation, I don’t know, maybe, maybe point them to this video.

I’m happy to work with software vendors on being less crappy to SQL Server. That’s, that’s, it’s a pretty cushy gig when you do that because you get to tell a whole lot of people that they’re wrong about everything at once and, um, it’s far more effective.

Alleviating the masses of their, of their, of their opiates is a noble endeavor. So anyway, uh, this video has gone on a bit longer than I anticipated, uh, and, and, I don’t know, there’s a lot of green text there that I, I sort of skimmed over and my head’s blocking some of it in here.

So we’re not gonna, we’re not gonna bore anyone any further with that. So, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Um, if you like this video, thumbs ups is nice.

Comments is nice. Nice comments are nice. Right? Mean comments are not nice. Uh, if you like this sort of SQL Server content about fill factor and fragmentation facts, uh, then you can subscribe to this channel and you can join nearly 3,000, hang on, I have to double check my numbers now just to make sure I don’t lie to you.

Make sure that we stay honest in these videos. Um, let’s see, where is my channel? Uh, you know what? It’s like, like 3,550 something at this point.

So, uh, you know, however, however many other people. You can, you can join that lovely, lovely queue to get notified when these videos get published. And, um, I don’t know.

I’m gonna, I’m gonna go enjoy the air conditioning now. Uh, I finally got marital approval to put the air conditioners in. So, I did that and, uh, you know, I’m working hard for the SQL Server community.

Putting in big, heavy air conditioners to, so I can record these videos without turning into a sweaty puddle in front of you. I’m sure everyone appreciates.

Anyway, uh, thank you for watching. Thank you for watching.

Going Further

A Little About Multi-Column Indexes In SQL Server

Posted on June 10, 2024January 22, 2026 by Erik Darling

A Little About Multi-Column Indexes In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the world of multi-column indexes in SQL Server, explaining their capabilities and limitations. Erik Darling from Darling Data shares his experience dealing with unexpected technical issues that delayed recording a planned video on parallel nested loops. Despite these challenges, he manages to deliver valuable insights by comparing query plans generated using different cardinality estimators and demonstrating how adding computed columns can significantly improve query performance. I hope you find this content as enlightening as I did while preparing it, especially if you’re dealing with complex queries involving multi-column comparisons.

Full Transcript

Erik Darling here with Darling Data. And today’s super ultra, probably the most important video you’ll watch over the next 10 minutes. We’re going to talk about multi-column, we’re going to talk a little bit about multi-column indexes in SQL Server and what they do and what they don’t do. So, last week, I promised a longer video on parallel nested loops and let me just tell you how many things have been just ganging up on me to prevent that from happening. One, Friday, when I went to actually record it, all of the lights in my office and actually apartment started getting weird and flickery and like things were like turning off like the like my computer monitors would start turning off off and on and then like randomly lights wouldn’t work. And I don’t know how many of you out there are like certified electricians. I know that there are a lot of forklift operators in the crowd, but I’m not sure about the electrician segment of Darling Data fans. But if you ever open up a fuse box, like behind the panel, there are, well, if you’re in America, there are usually two big wires coming in and they have screws that kind of hold the stuff in place to make a connection and provide electricity through the fuse box and the apartment at large. And apparently just over time, because of the natural vibrations of the city of New York, the screws had come loose. And so they had to be tightened so that electricity could once again flow freely and smoothly throughout everything. So that got done like late Friday night. And by that point, I was like, well, there’s just no way, no way in hell I’m trying to record a video about parallel nested loops. Now I’ve already had a few drinks and it’s a, it’s a dense enough set of, of, of explanations without the mind being a bit foggy. And so that didn’t happen. And then of course the weekend came along and you know what they say about weekends?

No one watches SQL Server videos on weekends. No one cares. So, um, you know, uh, Monday, uh, kicked my butt. Uh, a lot, a lot of, a lot of, a lot of query tuning work done on Monday. And, uh, here we are on Tuesday. And I kind of realized that the, the material that I have is a bit too dense for just one, one quick video. So I’m going to have to, I’m going to have to figure out a better way to present all that stuff. But it’s, it’s in the works. In the meantime, I hope you’ll accept this piece offering about multi-column indexes. So, uh, let’s do it. All right. So I’ve got, uh, four queries here. Um, two of them are using the, what Microsoft so, uh, I don’t know, hubristically refers to as the default cardinality estimator, which is stupid.

Um, it’s just new. Um, and, uh, two queries that are using the legacy cardinality estimator. And, uh, it’s not legacy. It’s just the, it’s the older one. Um, and what I want to show you here is, uh, well, it’s the first thing I want to show you here. This is first of many things that I’m going to show you here. I’m going to try to make this one on the shorter side because it’s hot. And part of the reason why it’s hot is there’s too much hair coating my head in some places anyway.

Uh, and it’s, it’s, it’s a sweaty one, sweaty one here in the city. And I have not been, um, meritally cleared to put the air conditioners in yet. So here we are getting a little sweaty on camera for you. Uh, but what I want to show you first is that, uh, neither one of these cardinality estimators does a tip, does a terribly good job of actually estimating cardinality. So, uh, here’s what all four of these queries return. Uh, the first, the, uh, first one, 269,420, I did not intentionally write a query, uh, that returned those numbers.

I’m just now realizing what those numbers are now that I read them out loud. Uh, and then we have two that return zero. So, uh, let’s recover smoothly from that. And let’s look at the query plans because what I want to show you in the query plans is how different cardinality estimation models handle these sort of predicates. Right? So we’re just looking for first where creation date is less than close date, which actually, you know, has rows that hit it because often, uh, you have to create a question before you can close it.

And then we have these, uh, that make no sense because you can’t close a question or you can’t create a question before you close, after you close it, something like that. Uh, so yeah, no, this just makes no sense. But SQL Server estimates the same number of rows, uh, for both of them. You get a same sort of stock guess of about 30% of the rows in the table. The post table has about 17 million rows in it. 5 million is apparently about 30% of 7 million.

So we have that going for us because five times three would be 15. And if we added 3%, then we would get like 99. That would bring us a little bit closer to like, you know, 5 point something million, a little higher. So trust me, math. We’ve got, we’re good there. And the legacy cardinality estimator, the calculation is not nearly so straightforward, but the end result is that SQL Server thinks that 97,000 rows will, uh, qualify for this predicate, uh, which is also wrong. It’s a little bit less wrong, right? Cause you know, 269,000, it’s a lot closer to 97,000 than it is to 5.1 million. But the legacy cardinality estimator does the same thing.

And, uh, that’s not what exactly what I wanted. Uh, and it also gives you the same stock guess. Now, like I said, this math is a whole lot less straightforward to figure out. There is a lot of funny symbols and letters that are actually numbers and things like that. Basically, uh, unless you are an advanced mathematician, uh, it would do no good to try to explain this formula to you. You would never remember it anyway. Uh, it would do no good. So what a lot of people, notice I don’t have any good indexes for these queries. We’re just working off the clustered index.

So, uh, let’s take a first stab at an index on, for this query, right? Let’s just say we want to create a query, uh, sorry, we want to create an index on creation date and close date. All right. And so we’ll get that index moving and let’s run these queries again. Now the last set of four queries took about seven seconds to run. Uh, they all generated parallel execution plans. We did some fancy fun work. We got results back. Everything was groovy.

And now with that index in place, well, I mean, do we really do any better? Not, not, not really. Everything finishes in just about the same amount of time. We get the same estimates for all of these 97,318 here, 5.1 million, blah, blah, blah over here. And we get all the same results back. Now, the thing to keep in mind here is that multi-column indexes like this don’t track any correlations between the columns in them, right?

You really only get a histogram on creation date. Uh, SQL Server may have in the background created a system statistic on close date. That’s totally possible, but this isn’t like a bad statistics thing. This is just SQL Server doesn’t like SQL Server indexes. Don’t keep track of this, right? It has no idea how, like how creation date, how many creation dates are less than or greater than close dates.

Like this is not something that statistics track. And it’s very, very hard to communicate some of these things to SQL Server. So like, unless we had a check constraint that told SQL Server that creation date always has to be greater than, uh, close date and that close date can never be greater or creation date can never be greater than close date. Creation date always has to be less than close date. Like, you know, we might get like giving a little bit more information might be kind of helpful, but SQL Server is going to have no idea how many creation dates, uh, like will be less than close date.

Cause remember not every question gets closed, right? So close is going to have a lot of null values in it. So only the questions that get closed have values here. And SQL Server doesn’t keep track of how many of those like, like might just show up, right? We just don’t have that kind of good information. So the only way to really give SQL Server any better info, like not the only way, but one, one way that you can give SQL Server a cleaner path to these sort of predicates is to use a computed column.

Now, uh, what I’m going to do is alter the post table and I’m going to add this column, which converts a bit, uh, converts these ones and zeros to bits. So when creation date is less than close date, it’s one when creation date is greater than close date at zero. And that’s going to be that, right? So we add our computed column and notice how quickly that added because I did not add the persisted keyword.

And I want you to pay attention to something else very closely here is that I don’t need to persist this column in order to create an index on it. Likewise, I also don’t need to persist that column for it to get statistics generated on it. Persisted is just a special thing that you do when you, you, I mean, it’s a tough choice to make because even persisting computed columns doesn’t guarantee you a whole lot in the way of, um, you know, things, things going well when, when you query them. Uh, that’s often, often quite a crapshoot.

So, uh, that index is in place and I’m just going to show, uh, a couple of versions here with the, uh, the, well, I guess the default cardinality estimator. I’m going to go along with Microsoft silly, silly naming scheme. And we’re going to just run these two queries. And these two queries finish a whole lot faster because we are able to very seek, very easily and quickly seek. And if this thing would just let me do the grabby thing and move the query up, we are able to very quickly, uh, jump to where, uh, the data that we care about, right?

Notice that we didn’t have to directly reference the computed column in the where clause. We just had to exactly write the form of the computed column in the where clause. Uh, so, but this is where we’re looking for one equals this. And this is where we’re looking for zero equals this. And notice that cardinality estimates do improve quite a bit here because SQL Server has an absolute, I mean, SQL Server always thinks that one row is going to exist. Even if no rows exist, you’ll never see a zero.

I mean, I’m not going to say never. You’re almost guaranteed to never see zero of zero for one of these things. Uh, at least for like an actual, like data acquisition operator. There are probably some like in memory operators where you could see zero of zero. But anyway, I, I, I, I digress. And in a minute, I’m going to undress cause holy God, it’s hot. Uh, but like what you see here is that we get, because we actually stabilized that expression, we gave SQL Server like an actual, like materialization of what we’re looking for and what we care about.

Uh, we are able to, uh, like generate good statistics for like exactly what we’re looking for. So we get the exact hundred, hundred percent spot on guess here. And even though we get zero of one here, that’s pretty gosh darn close to it. We get very nice index seeks exactly to the data that we care about. We don’t have to scan the whole index, figure out if one is greater or one is less.

And it just saves a lot of time generally. So if you’re dealing with query plans, uh, that even do something kind of simple, like, like you just want to figure out if one column is greater than the other. Um, this, you know, you might run into all sorts of issues with cardinality estimation. You might have a tough time indexing for those queries. And, you know, you might suffer all sorts of plan quality issues because you’re not getting good cardinality estimates from these things.

Remember with the default cardinality estimator, you get a stock guess of 30%. That could be way, way off with the legacy cardinality estimator. You get a very fancy math estimate, which in this case was closer to reality, but was still off by like a hundred percent or a little bit more because it was 97 versus two, two, six, nine, four, 20. Yes.

Not going to jail for that. So, uh, if you, if you find yourself having to do these sorts of calculations, uh, and you find yourself getting bad cardinality estimates and bad query plans, things slowing down, uh, one thing, one option that you might want to look at is creating a computed column that expresses this stuff for you. Where this gets a little tougher is that, uh, you know, I do run into a lot of people who have to make this comparison across tables.

So like, let’s just say for the context of the stack overflow database, let’s just say we were comparing a creation date column in the post table to a date column in like users or comments or votes or something or badges or any other column with a date in it. You know, it would, it would, it would be, you can’t really index that. You can’t index across tables, uh, directly.

You could create an index view and then create, you know, if you needed to create whatever other stuff you had to on top of that. But, uh, that would be one way of indexing across tables. Other than that, you would be looking at like dumping stuff into a temp table where you combine the results and then index that.

Or just, you know, some other, uh, some other data materialization layer, uh, where that you, that is indexable in that way. Which now that I think about it, like realistically, it’s going to be indexed views and temp tables, but all situational, isn’t it? Anyway, um, I got other stuff to do.

Um, and there is some sort of aircraft going by. Uh, I hope doesn’t pick up on the mic. But anyway, um, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. I hope you, um, are not hot and sweaty wherever you are. I hope that you are air conditioned and comfortable. Uh, and you’re, you’re having a good day.

Uh, if you like this video, uh, I, I do enjoy a nice thumbs up and I do enjoy a nice, uh, nice comment. Um, uh, you know, again, just, just be, go easy on me because I’m having, I’m having a sweaty one over here. Uh, if you like this sort of SQL Server content, please subscribe to the channel.

Uh, you can join. Now, let me, let me refresh this so I get the exact number. You can join nearly 3,545, nearly, other subscribers to get notified when, when I drop these precious gems. I drop these jewels on you.

And, um, I, I do, I do also promise that eventually we will get, we will, we will get to the parallel nested loops video. And, um, I’m looking forward to seeing the watch metrics on that. I’m looking forward to see exactly how long viewers stick around for on that one because, uh, like I said, it is some dense material.

And, uh, it is kind of, kind of mind numbing when we, when you get down to it. But, uh, anyway, uh, it’s time for me to go de-sweat myself. So, um, you can, I’ll leave you with that visual.

Uh, once again, thank you for watching. Goodbye. Uh, why won’t this thing stop recording?

Oh, it did stop recording. No, it didn’t stop recording.

Going Further

A Very Silly Performance Tuning Trick In SQL Server

Posted on June 7, 2024January 22, 2026 by Erik Darling

A Very Silly Performance Tuning Trick In SQL Server

Thanks for watching!

Video Summary

In this video, I share a humorous and somewhat unconventional query tuning trick that I had to use while working with a client recently. The scenario involved a simple query in the StackOverflow database that was taking an unusually long time due to a table spool in its execution plan. After trying various methods to eliminate the spool, including using trace flags and hints, none seemed to work as expected. That’s when I decided to take matters into my own hands by converting one of the columns to an Envarkar max type, which surprisingly got rid of the table spool and significantly improved query performance. While this solution is situational and not guaranteed to work in every case, it offers a creative workaround for those facing similar issues without resorting to expert-only hints that might be deemed inappropriate by some.

Full Transcript

Erik Darling here with Darling Data. And in this short, amusing video, I’m going to show you a very stupid query tuning trick that I had to pull while working with a client this week. Yes, you can hire me. I don’t just record YouTube videos and become a millionaire. So the deal was there was a very simple query that looked a bit like this. There was not a very obvious missing index in the one that I was dealing with, but the only way that I could get this to re-pearl locally in the StackOverflow database was to not have a useful index. So, you know, you can just, for now, just ignore the green text behind me, behind my head, this stuff. Because obviously this index would fix this situation. But the issue that I ran into was that SQL Server insisted on having a table spool. In the query plan. And when, with the table spool in the query plan. And I do have to just want to note, this, this is a short video early on Friday. I do have a longer video that I plan on recording later when I have more time. This is just a quick one because again, this was just funny to me. All right. Sometimes, sometimes you’re just going to have to deal with things that I find amusing enough to record videos about. So I’ve got this top query up here. Let me make sure I’m pushing the right buttons or I’ll get in trouble with the FCC. And this, this query runs for 40 and a half seconds. And if you look at, so, you know, just to kind of put things in perspective here. If you look at where time is spent, that’s about 39 minus 13 seconds there. So that’s another 29, 26 seconds is spent in this lazy table spool. And 13 seconds is spent in this clustered index scan.

And the thing that is very amusing about this is that I tried all sorts of things to get rid of the table spool. Now, of course, there’s a trace flag you can use. There’s also a very friendly option, option hint you can use called no performance spool to get this in there. But, you know, they were, you know, hinting queries. Apparently, someone said that it’s for experts only. And even though I got paid as an expert to tune queries, the query hint idea was out. So, you know, I had to, I had to, I had to resort to some extreme measures. And if you look at this bottom query, well, this thing runs for only 13 seconds total.

So, like, just the time that we spent scanning this clustered index in this query without the spool, this thing runs for, I mean, you know, like, I don’t know, like a fourth of the time or something, right? That’s about 10 seconds. That’s about 40 seconds. So, like, let’s just say it’s about a 4x improvement. I don’t feel like doing decimal math right now. Again, it’s Friday. Friday. Why would I want to do math on Friday? And so you might be wondering, what awful trick did I play to get SQL Server to not create a table spool in the query plan? Well, it looks a little bit like this.

This query was selecting some columns up here, right? Owner user ID, score, post type ID ID. Of course, the real query wasn’t selecting these columns because the real query wasn’t in the stack overflow database. That should be obvious to you by now. Stack overflow is not one of my clients, but if anyone from stack overflow is watching, you would like expert query tuning. My doors are open to you. In this query, all I do is I convert that last column to an Envarkar max.

And if I had to put some supposing shoes on, put on my supposing hat, it would be that the presence of a max data type made the idea of creating a table spool up in tempDB with a max column a bit overwhelming. And it costed that decision away. It just got rid of it. Threw it out, said, you’re too expensive. I don’t want you. There’s no coupons on Varkar max columns today. We’re just going to have to do this the old-fashioned way without a spool. And it ended up turning out quite well.

Now, this was a very situational thing. I’m not going to promise that every time you see a table spool, if you cast a column to an Envarkar max, that you’ll get rid of the spool and things will be better. There are all sorts of optimizer-costing things and logics that are going to have to go into that that may not work out in your favor. But if you do find yourself staring down a query with a table spool in it, and that table spool takes an excessive amount of time, and you have tricks to test out the query without the table spool, this is one way that you could get rid of it without having to put a hint in there that someone will say, I don’t think you’re expert enough to use that hint. I don’t think you quite have the credentials.

Can I see your certification list? Because, you know, those Microsoft certifications are so meaningful. I’m so happy that you got a DP. It’s great for you. We all dare to dream, don’t we? Anyway, I have a call starting soon that I’m going to get to.

So I’m going to wrap this up and I’m going to post it. And then a little bit later today, this Friday, lovely Friday, this 10 out of 10 Friday, weather-wise at least. The rest of it is, you know, up to some whims and fancies, but at least this Friday is pretty good weather-wise.

So I’m going to post this and then I’m going to post another thing later about different things that you might see in parallel nested loops joints, because interpreting those parallel nested loops joints plans is quite difficult. I’ve talked about them a few times, but never quite in this way.

So I hope that you’ll stick with me. So I hope you enjoyed yourselves. I hope you found this amusing. You may have learned something. You may have not.

I don’t know. But if you like this video, give it a thumbs up or a nice smiley face little comment. If you like this sort of SQL Server performance tuning advice, kind of as silly on the face of it as it may seem, you can subscribe to my channel and join…

Let me get the most up-to-date number here. Nearly 3,503 other people. Nearly. We’re one away. And you can also get…

You’ll be one of those 3,500 and whatever people to get notified when I post these things. They’re usually much higher quality than this. This one…

This honestly just made me jump into Giggle Bush. And when I’m into Giggle Bush, I want to record stuff. So I hope you jumped into Giggle Bush with me. Maybe… Maybe…

It’s nice to have company in the Giggle Bush. Right? Giggles last longer when you have someone to tickle. That’s the rumor anyway. So anyway. Thank you for watching. You can…

Let’s see. Like, subscribe, Giggle Bush. I think I covered everything. All right. Cool. Time to go do some actual work. And then we’ll have some more fun later talking about parallel nested loops. Thank you for watching.

Going Further

Some Questions I’ve Answered Recently On Database Administrators Stack Exchange

Posted on June 5, 2024May 30, 2024 by Erik Darling

Fun and No Profit

Normally I don’t have many questions, but here are a couple that I did ask:

Here’s the list of answers on dba.stackexchange.com:

Thanks for reading!

Going Further

In Which I Share A Piece Of Code That I’m Not Proud Of

Posted on June 4, 2024May 24, 2024 by Erik Darling

Requesting

I am sometimes asked to write special bits of code by people to solve a specific problem they’re having.

A recent one was for quite an unreasonable situation brought on by a shamelessly written vendor application, where:

Implicit transactions were in use
Bizarre locking hints abounds
Absolutely zero attention paid to transaction handling

Which lead to scenarios where select queries would run, finish, and never close out their connection. Of course, this was bad, because loads of other queries would get blocked by these things that should have just ended their sessions and released their locks and been on their way.

And so I wrote this thing. A thing that I’d always sort of made fun of the concept of, because I’d seen so many bad implementations of it throughout the years.

Most of them would just look for lead blockers and kill them without any consideration as to how much work they’d done, which would lead to even more blocking during rollback.

This one specifically looks for things that have used zero transaction log space.

Here it is. I don’t love it, but I wanted to share it, because it might make you feel better about some code that you weren’t proud to write, either.

Thanks for reading!

/*
EXEC dbo.sleeper_killer
    @debug = 'true';

SELECT
    sk.*
FROM dbo.killed_sleepers AS sk;

*/

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET STATISTICS TIME, IO OFF;
GO

CREATE OR ALTER PROCEDURE
    dbo.sleeper_killer
(
    @debug bit = 'false'
)
AS
BEGIN
    SET NOCOUNT ON;
    
    /*Make sure the logging table exists*/
    IF OBJECT_ID('dbo.killed_sleepers') IS NULL
    BEGIN
        CREATE TABLE
            dbo.killed_sleepers
        (
            run_id bigint IDENTITY PRIMARY KEY,
            run_date datetime NOT NULL DEFAULT SYSDATETIME(),
            session_id integer NULL,
            host_name sysname NULL,
            login_name sysname NULL,
            program_name sysname NULL,
            last_request_end_time datetime NULL,
            duration_seconds integer NULL,
            last_executed_query nvarchar(4000) NULL,
            error_number integer NULL,
            severity tinyint NULL,
            state tinyint NULL,
            error_message nvarchar(2048),
            procedure_name sysname NULL,
            error_line integer NULL
        );
    END;

    /*Check for any work to do*/
    IF EXISTS
    (
        SELECT
            1/0
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_tran_session_transactions AS tst
          ON tst.session_id = s.session_id
        JOIN sys.dm_tran_database_transactions AS tdt
          ON tdt.transaction_id = tst.transaction_id
        WHERE s.status = N'sleeping'
        AND   s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
        AND   tdt.database_transaction_log_bytes_used < 1
    )
    BEGIN   
        IF @debug = 'true' BEGIN RAISERROR('Declaring variables', 0, 1) WITH NOWAIT; END;
        /*Declare variables for the cursor loop*/
        DECLARE
            @session_id integer,
            @host_name sysname,
            @login_name sysname,
            @program_name sysname,
            @last_request_end_time datetime,
            @duration_seconds integer,
            @last_executed_query nvarchar(4000),
            @kill nvarchar(11);
        
        IF @debug = 'true' BEGIN RAISERROR('Declaring cursor', 0, 1) WITH NOWAIT; END;
        /*Declare a cursor that will work off live data*/
        DECLARE
            killer
        CURSOR
            LOCAL
            SCROLL
            READ_ONLY
        FOR
        SELECT
            s.session_id,
            s.host_name,
            s.login_name,
            s.program_name,
            s.last_request_end_time,
            duration_seconds = 
              DATEDIFF(SECOND, s.last_request_end_time, GETDATE()),
            last_executed_query = 
                SUBSTRING(ib.event_info, 1, 4000),
            kill_cmd =
                N'KILL ' + RTRIM(s.session_id) + N';'
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_tran_session_transactions AS tst
          ON tst.session_id = s.session_id
        JOIN sys.dm_tran_database_transactions AS tdt
          ON tdt.transaction_id = tst.transaction_id
        OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
        WHERE s.status = N'sleeping'
        AND   s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
        AND   tdt.database_transaction_log_bytes_used < 1
        ORDER BY
            duration_seconds DESC;                       
        
        IF @debug = 'true' BEGIN RAISERROR('Opening cursor', 0, 1) WITH NOWAIT; END;
        /*Open the cursor*/
        OPEN killer;
        
        IF @debug = 'true' BEGIN RAISERROR('Fetch first from cursor', 0, 1) WITH NOWAIT; END;
        /*Fetch the initial row*/
        FETCH FIRST
        FROM killer
        INTO
            @session_id,
            @host_name,
            @login_name,
            @program_name,
            @last_request_end_time,
            @duration_seconds,
            @last_executed_query,
            @kill;
        
        /*Enter the cursor loop*/
        WHILE @@FETCH_STATUS = 0
        BEGIN
        BEGIN TRY
            IF @debug = 'true' BEGIN RAISERROR('Insert', 0, 1) WITH NOWAIT; END;
            /*Insert session details to the logging table*/
            INSERT
                dbo.killed_sleepers
            (
                session_id,
                host_name,
                login_name,
                program_name,
                last_request_end_time,
                duration_seconds,
                last_executed_query
            )
            VALUES
            (
                @session_id,
                @host_name,
                @login_name,
                @program_name,
                @last_request_end_time,
                @duration_seconds,
                @last_executed_query
            );
        
            IF @debug = 'true' BEGIN RAISERROR('Killing...', 0, 1) WITH NOWAIT; END;
            IF @debug = 'true' BEGIN RAISERROR(@kill, 0, 1) WITH NOWAIT; END;
            
            /*Kill the session*/
            EXEC sys.sp_executesql
                @kill;
        END TRY
        BEGIN CATCH
            IF @debug = 'true' BEGIN RAISERROR('Catch block', 0, 1) WITH NOWAIT; END;
            
            /*Insert this in the event of an error*/
            INSERT
                dbo.killed_sleepers
            (
                session_id,
                host_name,
                login_name,
                program_name,
                last_request_end_time,
                duration_seconds,
                last_executed_query,
                error_number,
                severity,
                state,
                error_message,
                procedure_name,
                error_line
            )
            SELECT
                @session_id,
                @host_name,
                @login_name,
                @program_name,
                @last_request_end_time,
                @duration_seconds,
                @last_executed_query,
                ERROR_NUMBER(),
                ERROR_SEVERITY(),
                ERROR_STATE(),
                ERROR_MESSAGE(),
                ERROR_PROCEDURE(),
                ERROR_LINE();
        END CATCH;
    
        IF @debug = 'true' BEGIN RAISERROR('Fetching next', 0, 1) WITH NOWAIT; END;
        /*Grab the next session to kill*/
        FETCH NEXT
        FROM killer
        INTO
            @session_id,
            @host_name,
            @login_name,
            @program_name,
            @last_request_end_time,
            @duration_seconds,
            @last_executed_query,
            @kill;
        END;
    
    IF @debug = 'true' BEGIN RAISERROR('Closedown time again', 0, 1) WITH NOWAIT; END;
    /*Shut things down*/
    CLOSE killer;
    DEALLOCATE killer;
    END;
END; --Final END

Going Further

Why You Should Avoid People Who Tell You To Avoid Subqueries In SQL Server

Posted on June 3, 2024January 22, 2026 by Erik Darling

Why You Should Avoid People Who Tell You To Avoid Subqueries In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the world of SQL Server performance tuning and take a critical look at the uninformed masses posing as experts on platforms like LinkedIn. These individuals often share advice such as avoiding subqueries and using `SELECT *`, which is not always sound or necessary. I explain why these blanket statements are misguided, providing examples from my own query analysis to illustrate that context matters greatly. By examining a specific query with multiple subqueries, I demonstrate how the real issue lies in inadequate indexing rather than the use of subqueries themselves. The video concludes with practical advice on creating an index that supports all parts of the query efficiently, leading to significant performance improvements.

Full Transcript

Erik Darling here with Erik Darling Data. Make sure that my enunciation is working. My new AI enunciation chip is in place. And I’m recording this video early today because this is the situation that we are dealing with currently in my neck of the woods. And this is, I think that there’s probably a good line out of Ferris Bueller’s Day Off about, how can I be expected to care about SQL Server on a day like this? The only thing we don’t like about today is this little guy down here. We don’t like this one. Because of this, there is a very high chance that I am going to have to turn off my microphone at some point in order to not sneeze in your face and ears during the recording of this video. I’ve already had to use allergy eye drops four times today because my vision would let my eyes get dry. My vision would start to get a little blurry and my vision would start to get a little blurry and I’d be like, oh, the Lord’s taking me. Blurry vision is the first sign. But it turns out just the, we thoroughbred nerds here at Darling Data have all of the allergies and all of the symptoms that come along with those allergies and one of them being dry, itchy eyes. So today we’re going to talk about my least favorite kind of people in the world.

And that is the uninformed masses posing as performance tuning experts on the internet, primarily on places like LinkedIn where people are supposed to be like visibly professional and know what they’re talking about. And there are people who say things like hot SQL tuning tips, hot performance tips. There’s like little fire emojis next to all these great ideas like don’t use select star, like avoid distinct. And like, like don’t use sub queries. And all of that advice is dumb. Like unmitigated dumb. There are times when select star is not harmful. There are times when distinct is not only necessary, but entirely helpful. And there are times when sub queries are just fine. Most of the people who say these things and make these blanket statements have no idea what they’re talking about. They’re like fitness people who are just like, yeah, bro, just eat less and exercise more. You can look like Ronnie Coleman. You can’t, you don’t, let’s not just diet and exercise.

You know, people who are just like, oh, well, you need to, you need to front squat. You know, those, the entire crossfit model with, with the wads and the people just doing idiotic, saying idiotic things and doing idiotic things in order to confuse their muscles. It’s a very, very sad state of affairs. I only know this because I, I, most of my feed is either databases or people doing squats. So it’s, it’s about what my internet looks like. So yeah, um, we’ve got this query here and this query is, you know, got a bunch of sub queries in it. All, they all go to the badges table.

All right. Well, every single one of them goes to the badges table. Even this, even this little exists down here. What you may not know is that exists is also a sub query. All right. This is a sub query. You can, you can, you know that exists is a sub query because if you try to create an indexed view and there’s an exists in it, you will get an error when SQL Server will say sub queries aren’t allowed in indexed views.

And you leave it. What? But it’s, it just exists. It’s just a little joined. What are you worried about? What are you worried about SQL Server? What idiot designed indexed views to not be able to use exists? Who would do that? Well, someone at Microsoft, let’s call them Sam, someone at Microsoft.

Microsoft. So, uh, this query, uh, is it admittedly on the slow side? Is it executed for, if I write, lift up the correct arm, you can probably see under my armpit there about 31 seconds. Uh, that’s not great, but you know, let’s look, let’s look at why. Let’s see. We’re, we’re using sub queries. Why, why shouldn’t we use sub queries?

A lot of people who tell you not to use sub queries will say, do a join instead. I’ve got bad news for anyone who thinks that a sub query does not end up doing a join because I can guarantee you, there are many joins in this query plan to implement all of those sub queries. There are a bunch of them.

A lot of people will also say that sub queries can only use nested loops joins, but there are folks out there. I got to be honest with you. SQL Server lies and obfuscates and, uh, is, is, you know, uh, less than forthcoming about many things. But these are all hash joins. Every single one of them, all hash joins.

Okay. So what, what, what are the slow parts of our execution plan? We’ve got, we’ve got, I think like five or six sub queries in the select list. So are they all slow? Not really. Only a couple of them are slow. And a couple of them are slow because we don’t have a good index to support the query that we’re running. And that’s really the, the root cause of, of many performance problems is just, you don’t have a good index in place to, to answer the question that your query is asking.

Indexing data is really important. And a lot of people are probably the same types of people who tell you to avoid sub queries are probably the same types of people who have no idea what the hell an index does in a database. So if we go and look, there are really only two slow parts of this query plan. I didn’t need that tool tip. SQL Server management studio. There are really only two slow parts of this query. We have two eager index pools that get built in here.

And these are both admittedly, they, they, they, they, they are using nested loops joins. And we, we, and just based on this query pattern and based on my, my just, you know, wizard like knowledge of SQL servers, query optimizer, I know exactly which two sub queries are causing these eager index pools. It’s, it’s going to be these first two, right? If I, if I quote this out, rather if I quote these two sub queries out with the top ones in them, and I rerun this query with all the rest of the sub queries still intact, boy, oh boy, we get a pretty fast execution plan.

And boy, oh boy, look at, look at every single one of these hash joins. We have no nested loops joins. What happened? Did, did someone, did someone on the internet lie to us maybe about sub queries, not being able to use hash joins or only being able to use nested loops joins?

Is someone on the internet stupid or dishonest somewhere in between? Is it, is it malice or ignorance? We’ll never know until we capture them and torture them.

Get the truth by hook or by crook. So wise man once said, So this is really just a case of us having inadequate indexes to, to answer a couple of the questions that our queries have attempted to ask of our data. And if we go and create this index here on the badges table, what we’re going to do is we’re going to fully support not only the two sub queries that we had up, up in the top that were causing the eager index pools, we’re going to help all of the sub queries really.

And this, this index only took three seconds to create. So we’re doing pretty good there. And, and since every single sub query is correlated between user ID and ID, right? We have user ID as a leading column.

And since some of these queries are also ordering, or at least one of them is ordering by date, I’m going to have that as a second key column. And since some of them are selecting name and some of them are selecting date, we’re also going to have name as an included column.

Right now, if we rerun this with all of our sub queries in place, all of these terrible, awful, no good, very bad sub queries. Well, the thing finishes instantly, doesn’t it?

Right? And oh, oh, oh, oh, no, but we have a bunch of nested loops joins now. That’s that, that’s obviously a big performance problem. Right?

All our sub queries using nested loops joins. How will we recover? How will we ever recover from this query that runs in 67 milliseconds? How will we do it? What are we going to tell our, what are we going to tell our boss?

What are we going to tell our wife, our spouses, our children? Like, I mean, you know, if it were me at work, it would be like, what am I going to come home and tell my wife and kids that daddy’s a failure? Because he used sub queries.

And with that, I’m going to end my Friday. Because that’s enough. And like I said before, it’s far too nice a day for me to care about SQL Server.

So, I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, you can join nearly 3,461 other people who subscribe to this channel and watch every single video that I post the whole way through.

If you like this video, thumbs up is appreciated. So are nice comments. I’m going to go have a nice long weekend now.

I’ll miss you. I love you. It’s been lovely. It’s been a great video. But it’s time for me to go have margaritas. And whatever else comes along.

I’ve recently discovered a strong affection for vodka martinis. My wife steals all the olives out of them. That’s okay. Olives aren’t really my thing anyway. But those have been hitting the spot lately, too.

So maybe some margaritas, maybe some martinis, but not in the same glass. It’s a recipe for handcuffs.

Not the bedroom kind of handcuffs with the fuzzy stuff on them. The metal ones that really hurt your wrists. I mean, I guess that could go either way, depending on what you’re into.

But anyway, let’s end that here. Thank you for watching. I will see you, depending on how the weekend goes, if the margaritas and martinis maybe have their way with me.

I might not see you for a while. But assuming that all goes well, I will be back to my regular blogging and recording schedule just around Tuesday of next week.

So I do hope that everyone out there has a great weekend. Except the people who tell you to avoid subqueries. I hope that you suffer tremendously.

You deserve it. Thanks for watching.

Going Further

Cursors In Scalar UDFs, and Other Performance Pitfalls In SQL Server

Posted on May 31, 2024January 22, 2026 by Erik Darling

Cursors In Scalar UDFs, and Other Performance Pitfalls In SQL Server

Video Summary

In this video, I delve into some fascinating aspects of scalar User Defined Functions (UDFs) in SQL Server. After a busy week teaching at the New England SQL Server User Group and dealing with client work, it was great to finally record something for you all. This session focuses on how scalar UDFs can impact performance, especially when they are not optimized or used inefficiently. I walk through an example where a cursor-based UDF significantly slowed down query execution, demonstrating the importance of using set-based logic and avoiding cursors whenever possible. The video also explores how to convert non-inlineable scalar UDFs into inline table-valued functions (TVFs) for better performance, showing that sometimes just adding a `DISTINCT` keyword or making minor tweaks can drastically improve efficiency. By comparing different approaches with query plans turned on, I aim to provide practical insights and solutions for optimizing your SQL Server code.

Full Transcript

Ahem. Ahem. Ahem. Ahem. Erik Darling here with Darling Data. And it would be completely inappropriate for me to tell you just how much I’ve missed you. This is my first chance that I’ve had to record this week. Last week I was gone, the last couple days, because I had to teach a class up in Boston for the New England SQL Server User Group. Fine bunch of people who, let me show up and get paid to talk about SQL Server for a full day. I had about 40 people there. I had someone fly all the way from Nigeria just to hear me speak, which is wild, because Nigeria, at least as far as I can tell from most maps, is FAR. And, like, I’m only used to people showing up from far away, either around the world, or around the world, or around the world. When I speak at past Summit, because it’s expected, right? Like, people just show up there from everywhere, like Mars. And when I’ve, when I’ve, I’ve actually, there’s been a few times when I’ve, I’ve done talks at SQL Bits in, in the UK, and I’ve run into people there from America who I’ve never run into in America. So, like, just seeing random people in the UK is always funny, and that, that happens. But, uh, this has been very busy.

Since I got back, uh, a lot of client stuff, you know, um, the nice people who pay me so that I can record these videos for free, because I don’t, like, get paid for this. So, uh, if any, if you see anything in this video, and you’re like, hey, maybe we could pay Erik Darling to do that, uh, you can pay me to do that, even if it’s standing here babbling. Which I apparently excel at. Uh, so that’s fun. Uh, in today’s video, ahem, we’re gonna talk about some interesting stuff with scalar UDFs, because recently, as recently as this week, I had to help a client with some scalar UDF rewrites, because, you know, if you’ve been paying attention to me, or really anyone who talks about SQL Server over the last, I don’t know, 15, 20 years, you may have heard that scalar UDFs have some performance issues.

You can’t put all those rows on a single thread, which sucks. The other bad thing about them is, of course, that they don’t run once per query.

They run once per row that the scalar UDF has to process, and depending on where in the query you put the UDF, and how many rows have to pass through that UDF for query correctness, for the sake of query correctness, you could end up with a lot of rows going through that UDF and that UDF executing over and over and over again. So if you, like, have a select top thousand query with a scalar UDF in the select list, you could execute that UDF 1,000 times, maybe more, in order to produce a result from every row that passes through that UDF.

And if you do something extra stupid, like put a UDF in a where clause, and that where clause has to process rows from a table with, like, I don’t know, let’s just say a million rows in it, that UDF may execute a million times, or more, to produce a result which to compare against the predicate in your where clause. So you can imagine, my great amazement, relief, just really breathtaking-ness, when SQL Server 2019 introduced scalar UDF inlining, which is a cool feature. It’s a very neat thing. It’s a very novel idea.

I don’t think there’s, like, one other not quite, it’s like RocksDB, maybe, or DuckDB that can inline scale our UDFs, but it was very exciting when Microsoft SQL Server did it, because, you know, I work with Microsoft SQL Server. I’ve never gotten paid to work with, like, anything else.

Literally anything else. So, at least database-wise. I’ve gotten paid to do a lot of other stuff. But database-wise, SQL Server is it. So I was really, like, dumb, like, wow, they’re fixing it, finally.

But there are a lot of restrictions on it. I’m not going to go to the KB article about it, because it’s depressing a little bit. But anyway, so this is kind of what the UDF that I had to fix started as.

It’s sort of a gaps in island problem, and I’ve retrofitted it to the Stack Overflow database. And the idea that I’m using in the Stack Overflow database is to find the longest streak of consecutive days that a user has answered questions, right?

So if they answered questions from, like, 2008 to 2012, one every single day, that would be a lot of days. I don’t know who does that. You have no life. Sorry. It’s just the way it is.

But the idea is to find the longest streak of consecutive days that a question was answered. And so the form of the UDF that I came across was something like this. Again, this is me retrofitting things to the Stack Overflow database, because Stack Overflow is not one of my clients.

But if you work at Stack Overflow and you’re watching this video, my rates are reasonable. I have several references, both inside and formerly at the company, who would be happy to tell you that you should pay me to do things.

So there is that. Anyway, this is what the UDF does. It declared a cursor. It selected some rows into a thing. And then it had all this weird logic to figure out if the streak was consecutive or if we had to restart the streak and, like, hold on to the highest current streak and then return that streak.

I’m just going to take this one quick moment here to say that if you are the type of person who has to declare cursors to do things, please always make sure that you at least include local in your cursor definition.

There are a lot of issues with global cursors, especially if you have two people try to declare them simultaneously, because you can’t.

They will clash and they will error out. So please at least, if you’re going to use cursors, declare them as local. That’s my spiel there. So there are, like, a bunch of things that I want to show you today. But the thing that I want to start with is actually how big of an impact this distinct keyword has in this particular UDF.

Now, I just want to make sure that we have no other indexes in this index created. And I have three testing queries over in this window. The three testing queries are, well, really the same query over and over again.

So again, I’ve got lined up for you today to look at a cursor version of this scalar UDF, a non-cursor version of the scalar UDF, and then an inline version of the UDF. And I think it’s kind of important to see the progression there, because not every scalar UDF has to have a cursor in it in order for it to be horrible.

So without the distinct keyword, right? We have the index now. We have this thing ready to go.

So this thing takes a real, real long time. Like a disappointingly long time. When we look at how long, it’s about 15 seconds of time this thing takes to run.

And this is absolutely zero buenos, as the kids say. And this was sort of the first thing. Well, not the first thing.

The first thing that I did when I opened up the cursor was just, like, hang my head. Because, you know, who would put a cursor on a UDF and expect something good to happen? You’d have to be one of the more foolhardy individuals that I’ve ever met in my life.

So that takes 15, well, let’s see, I guess 14 seconds. But if I move the right way, you can see 14 way down here somewhere, maybe. Oh, it’s on the other way.

No, there it was. Hang on. Where did you go? Where’s the 14 seconds? Next to the six. There we go. 14 seconds, right? Obviously not good.

We don’t like not good things at Darling Data. We have a strong HR policy against good things, against not good things. So let’s just recreate this function with the distinct keyword in there.

And let’s rerun this. And it’ll be about three times faster with the distinct keyword in there. It should take about four or five seconds, right?

So we cut 10 seconds off this thing just by adding distinct into the cursor. So if you’re afraid of making big code changes, sometimes a little distinct goes a long way. Don’t tell anyone I said that.

Usually I make fun of people who are just like distinct, distinct, union, distinct, distinct, union, union, union, distinct. Because, you know, they’re crazy. And they don’t probably just screwed up joins or didn’t use not exist or not exist when they should have. But I digress.

So you really do. So that was that, right? And obviously, you know, we are SQL Server data professionals. And the first thing we do whenever we see a cursor is we stand on our hind legs and we ring a bell and we say, have you tried thinking in sets?

And then we get a little treat. We get to be in the in crowd when we say, have you tried thinking? Have you thought about writing a set-based solution? Okay.

The thing is, and this is a big thing, is that, like, from a, let’s just call it programming perspective, because I don’t know what else to call it. Writing a set-based thing is kind of hard sometimes.

Like, I don’t know a lot of people who can bang out, like, a true gaps in islands solution, like, first try flawlessly without, like, a half a day of tinkering, depending on, like, how many weird, like, edge cases and outliers you might have to deal with. So this is the set-based solution, right, where we have to use window functions like lead, and we have to, like, calculate date diffs, and we have to use sum with, like, a real windowing function sum, not just, like, hey, sum column, like, legit sum with, like, an over clause.

And we have to remember to use the rows between unbounded proceeding and current row, because if you use range in here, your life is going to be nothing but pain. Everyone who loves you will leave you alone, right? You don’t want to use range.

And then we have to do all this stuff, right? So this is the inline version of the function, right? And, of course, we can have, let’s just move that one over there where it makes a little bit more sense. This is the scalar UDF version written using set-based code, where we would declare a variable up here, and we do all this stuff.

Now, the funny thing about this set-based solution is that when we get to thinking in sets, when we think really hard about our sets, really focus our brains on sets, we mess up scalar UDF endlining. So the whole CTE crowd out there who’s like, yeah, CTE, readable, ha, got your readable query here.

They are, they are, we’re going to be disappointed. If we had used derived tables in there, we could have avoided this unpleasant scenario. But with all the CTE, if I just, let’s just get an estimated plan for this.

We look at the properties here. We are going to see that our T-SQL UDF function, not parallelizable, reason shows up because CTE make, break scalar UDF endlining, make scalar UDF endlining not work.

Okay. So the cool thing is, though, is that if you’ve got cursor code, like there might be something you can tweak in there to make it less awful.

Not great, but less awful. There are cursor options you might tinker with. You might throw a distinct down there. Might do all, try all sorts of tricks.

But if you’ve already got sort of set-based code like this, it’s very easy to strip away the things that make this function not inlineable, right, and write it as an inline function.

Now, just keep this in mind, like, in your head when we’re looking at this code. The only, like, this is the, this is a scalar UDF. The only different, the only real, like, actual code body differences are in the scalar UDF, we declare this variable, we write CTE, and then we return this variable.

The inline version of this function, well, it’s, obviously it skips over declaring a variable, and obviously it skips over returning a variable, but it does the same thing otherwise, right?

Like, we don’t declare a variable up here, and we don’t set that variable equal to the final result here, and then return that variable, but the code is exactly the same otherwise.

Like, all those CTE are exactly the same. I changed nothing except, like, just those things in the code, and of course I told SQL Server that I’m returning a table rather than an integer over here. All right, so that’s it.

That’s all that’s different. All right, so let’s come over to our, our answer street testing window. We’re going to give these things a real thrill ride, and let’s run all three, and we’ve got query plans turned on, so we can see what the query plans did.

All right, this and this and this. So all three run, and sort of as expected, as is wont to happen when one trifles with scalar UDFs, we have, up here, we have the cursor version, which runs for, again, about four seconds, and what’s, again, something really nice about modern, execution plan analysis, is that we get operator time, so we can see that the clustered index scan of the user’s table, which took 174 milliseconds, was pretty quick, even though SQL Server’s like, oh, we need an index to make this fast, 85%, or almost 86%, we’ll improve the query by.

The thing is that that index is on the user’s table, the user’s table has absolutely nothing to do with this compute scale R, the compute scale R, if we subtract the 174 milliseconds from here, just runs for about four seconds even.

Right? Close enough. There might be like a 50 millisecond difference or so. So, obviously, like, we did better in here without the cursor.

Right? That’s fairly obvious. The cursor UDF took about 4.2 seconds, and the non-cursor UDF took about nine seconds, even not being able to produce a parallel plan anywhere along the way, yada, yada, yada, yada, yada.

We still did a lot better. We spread this query up by 4x. In some cases, that might be good enough. All right?

So, let’s compare that with the inline scalar UDF. Sorry, the inline table valued function version of the query, which finishes in 164 milliseconds. And I know what you’re thinking.

It’s an unfair comparison, Eric. You should drop clean buffers and run it at max.1 so it’s fair with the other query. Whatever other nonsense people bark at me when they’re, like, mad that I did something faster than them. I don’t know.

It’s the thing people have. So, yeah, this does have the somewhat unfair advantage. This does get a parallel execution plan. This does finish, I don’t know, 800 milliseconds faster, which is, you know, another good, depending on what you care about.

It’s a good improvement. One other thing you might notice looking at the query plans is that these two have very small query plans over here. These just show us hitting the user’s table.

This one actually shows us going to the post table. If we follow this trail long enough, we’ll see that we actually do touch the post table way over here, which we don’t see when we look at the scalar UDF versions.

And, of course, if we were to get the estimated plans for the UDF versions, we would see the UDF query plans in here and in here. Right?

And this query plan for the, you know, the set-based UDF is very close to the query plan for the inline table valued function. It’s just hidden all the way inside, buried in the UDF. Now, if you’ve watched my videos before, you might have heard me explain why.

It’s because if you’ve actually listened to this video closely, you might understand why. It’s because every time we pass a row through a scalar UDF, we have to run that UDF.

So, for this query where, let’s see, let’s just run this one real quick since it’s easy to, since it’s pretty quick to run. This returns 613 rows. If we were to get an actual execution plan for every iteration of the function, we would have returned 613 execution plans back to SSMS.

SSMS would have fallen over and died in its 32-bit misery. So, we’re probably thankful that we don’t get that. We just get a compute scalar that says, don’t worry, I took care of it.

Right? Okay. So, fair enough. All good there. Now, to close this video out, to my grand finale here, what I want to tell you is that either scalar UDF inlining, the feature, assuming that you have right functions that are eligible to be inlined, if you’re on SQL Server 2019, Enterprise or Standard Edition, I don’t know about Web Edition, because who cares?

You’re not serious. It’s like if you use Hyper-V in production, you’re just not serious. He’s like, you must be joking. So, if we have scalar UDFs that can be inlined automatically, or if we go through the great mental strain, the tremendous difficulty, peril, of rewriting scalar UDFs as inline UDFs, sometimes we may find that our query slows down quite a bit.

And the reason why is because if we get the estimated plan for this one, what you might see in your query plans are eager index spools.

Now, I’m not going to sit here and make you wait for this to run, because an eager index spools coming off the post table is fairly disastrous. It usually runs somewhere around 45 seconds to a minute.

So, what the scalar UDF inlining or rewriting scalar UDFs as inline table-added functions can often expose as really bad indexing or just insufficient indexes to help your queries.

So, if you rewrite a query that has a scalar UDF in it, you rewrite the scalar UDF and you find that the rewritten query suddenly slows way, way, way down, make sure you get that actual execution plan.

Make sure that you are on the lookout for eager index spools and make sure that you create indexes that adequately satisfy the needs of your scalar UDF so that SQL Server doesn’t haul off and create a whole index for you on the fly every single time.

It’s not a good time. Not a good time at all. You don’t want that. And, of course, that won’t happen with this version of the UDF because this UDF takes one row and goes and executes it because this one takes all the rows and comes up with the query plan and inlines it.

SQL Server thinks, well, I don’t want to do that nested loops join that many times. That’s madness. Absolute madness. I’m not going to… I need an index. You want me to do that.

So just be careful there and be prepared to create indexes if you run into that scenario. Of course, I have lots of videos about eager index spools on this channel, so if you search my channel for eager index or spool, probably just search for spool.

That’d be good enough because you’ll learn about other types of spools too. Might as well increase the entire surface area of that smooth brain of yours, get some good wrinkles in there. Right?

Half the battle and all that. So anyway, this was a small portion of the client work that I did this week. got a real bad UDF, rewrote it, and just because I had something to sort of work off of, I figured, why not give you three examples over here and show you some of the downfalls of cursors in UDFs, UDFs in general, and something that you might run into, an eager index spool.

If you rewrite a UDF as an inline UDF, and performance, for some reason, gets worse. So you have all of, you are equipped with all of the knowledge you need to go from point A to whatever your end point is.

So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you are better prepared to do your SQL Server performance tuning job because I care about that.

If you like this video, I do adore a thumbs up. I do. I do. I sometimes like comments depending on, depending on what’s in them.

Sometimes they’re good. Sometimes they leave, leave a bit to be desired. Sometimes they hurt my feelings, my deep, deep feelings. And of course, if you like this sort of SQL Server content about performance tuning, you can subscribe to my channel so that every time I unleash these nuggets before you, you are notified promptly and you can see them before anyone else.

You can be the first person to see them. Wouldn’t that be great if you were the first person to ever see them? Like viewer number one every time? Surely you’d win some sort of prize if you could prove that sort of thing.

Anyway, thank you for watching. I’m going to turn off all these really hot lights now because I feel a patina beginning to form and I do not like feeling patined.

So, thank you. Thank you.

Going Further

A Query Writing And Tuning Exercise: Finding Duplicate Post Titles In Stack Overflow

Posted on May 30, 2024May 23, 2024 by Erik Darling

ErikGPT

I’m going to be totally open and honest with you, dear reader: I’ve been experimenting with… AI.

See, I’m just a lonely independent consultant, and sometimes it’s just nice to have someone to talk to. It’s also kind of fun to take a query idea you have, and ask “someone” else to write it to see what they’d come up with.

ChatGPT (for reference, 4 and 4o) does a rather okay job sometimes. In fact, when I ask it to write a query, it usually comes up with a query that looks a lot like the ones that I have to fix when I’m working with clients.

If I poke and prod it enough about the things that it has done wrongly, it will agree with me and do things the right way, eventually. That is an improvement over your average T-SQL developer.

Your average T-SQL developer will spend a terrible amount of time trying to figure out ways to write queries incorrectly, even when you show them the right way to do something, often under the assumption that they’ve found the one time it’s okay to do it wrong.

For this post, I came up with a query idea, wrote a query that did what I wanted, and then asked the AI to write its own version.

It came pretty close in general, and even added in a little touch that I liked and hadn’t thought of.

Duplicate Post Finder

Here’s the query I wrote, combined with the nice touch that ChatGPT added.

WITH 
    DuplicateTitles AS 
(
    SELECT 
        Title,
        EarliestPostId = MIN(p.Id),
        FirstPostDate = MIN(p.CreationDate),
        LastPostDate = MAX(p.CreationDate),
        DuplicatePostIds = 
            STRING_AGG
                (CONVERT(varchar(MAX), p.Id), ', ') 
            WITHIN GROUP 
                (ORDER BY p.Id),
        TotalDupeScore = SUM(p.Score),
        DuplicateCount = COUNT_BIG(*) - 1
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 1
    GROUP BY 
        p.Title
    HAVING 
        COUNT_BIG(*) > 1
)
SELECT 
    dt.Title,
    dt.FirstPostDate,
    dt.LastPostDate,
    dt.DuplicatePostIds,
    dt.DuplicateCount,
    TotalDupeScore = 
        dt.TotalDupeScore - p.Score
FROM DuplicateTitles dt
JOIN dbo.Posts p
  ON  dt.EarliestPostId = p.Id
  AND p.PostTypeId = 1
ORDER BY 
    dt.DuplicateCount DESC,
    TotalDupeScore DESC;

If you’re wondering what the nice touch is, it’s the - 1 in DuplicateCount = COUNT_BIG(*) - 1, and I totally didn’t think of doing that, even though it makes total sense.

So, good job there.

Let’s Talk About Tuning

To start, I added this index. Some of these columns could definitely be moved to the includes, but I wanted to see how having as many of the aggregation columns in the key of the index would help with sorting that data.

Those datums? These datas? I think one of those is right, probably.

CREATE INDEX 
    p 
ON dbo.Posts
    (PostTypeId, Title, CreationDate, Score) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

It leads with PostTypeId, since that’s the only column we’re filtering on to find questions, which are the only things that can have titles.

But SQL Server’s cost-based optimizer makes a very odd choice here. Let’s look at that there query plan.

sql server query plan — two filters? in my query plan?

There’s one expected Filter in the query plan, for the COUNT_BIG(*) > 1 predicate, which makes absolute sense. We don’t know what the count will be ahead of time, so we have to calculate and filter it on the fly.

The one that is entirely unexpected is for PostTypeId = 1, because WE HAVE AN INDEX THAT LEADS WITH POSTTYPEID.

¿Por que las hamburguesas, SQL Server?

Costing vs. Limitations

I’ve written in the past about, quite ~~literally~~ not figuratively, how Max Data Type Columns And Predicates Aren’t SARGable.

My first thought was that that, since we’re doing this: (CONVERT(varchar(MAX), p.Id), ', '), that the compute scalar right before the filter was preventing the predicate on PostTypeId from being pushed into an index seek.

Keep in mind that this is quite often necessary when using STRING_AGG, because the implementation is pretty half-assed even by Microsoft standards. And unfortunately, the summer intern who worked on it has since moved on to be a Senior Vice President elsewhere in the organization.

At first I experimented with using smaller byte lengths in the convert. And yeah, somewhere in the 500-600 range, the plan would change to an index seek. But this wasn’t reliable. Different stats samplings and compatibility levels would leave me with different plans (switching between a seek and a scan). The only thing that worked reliably is using a FORCESEEK hint to override the optimizer’s mishandling.

This changes the plan to something quite agreeable, that no longer takes 12 seconds.

So why the decision to use the first plan, au naturale, instead of the plan that took me forcing things to seek?

12 second plan: 706 query bucks
4 second plan: 8,549 query bucks

The faster plan was estimated to cost nearly 10x the query bucks to execute. Go figure.

For anyone who needed a reminder:

High cost doesn’t mean slow
Low cost doesn’t mean fast
All costs are estimates, with no bearing on the reality of query execution

Thanks for reading!