sql temp table Archives | Page 40 of 138

A Little About DOP and Bitmaps In SQL Server

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

A Little About DOP and Bitmaps In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the effectiveness of bitmap operators in parallel queries and how they can impact query performance. By analyzing a specific example from StackOverflow’s database, I explore the relationship between the degree of parallelism (DOP) and the efficiency of these operators. Through various DOP tests, I demonstrate that while higher DOPs may not always double execution speed, they can significantly reduce processing time, especially in larger datasets where row counts are more substantial. I also highlight how adjusting DOP to optimize bitmap performance can lead to better overall query efficiency by reducing the amount of data processed and joined, making SQL Server’s workload management more effective.

Full Transcript

Erik Darling here with Darling Data, the one and only, as far as I know. I did recently get an email that someone was trying to file a patent on Darling Data, and that if I paid this random lawyer a bunch of money, he would prevent it from happening. The crap you get from LinkedIn, you sign up for LinkedIn, and you put anything other than, like, I’m just an employee, man, leave me alone, in your title or experience, or like, you put a business on there. This is the kind of garbage that you have to look forward to. I also get lots of spam emails about VOIP phone lines from my office. I’m like, you’re gonna be real disappointed. I don’t know, some guy keeps trying to sell me wires, but mostly it’s just a bunch of emails like, hey Erik, call tomorrow. And I’m like, no, call never at any time. So, yeah, life on the internet sucks. We all knew that. Cool. In today’s video, we’re gonna talk about DOP, degree of parallelism, in bitmaps. I don’t think bitmaps is an acronym for anything. If it is, I’m way out of line. So, oops, I hit the wrong button. That was escape button. Hey, there we go. Pretend that didn’t happen.

But before we talk about the exciting world of DOP and bitmaps, the rich tapestry of DOP and bitmaps, we’re gonna talk about how you can keep me alive. You can sign up for a low-cost membership for the channel. It’s like four bucks a month. It’s a nice way to say thank you for getting, like, five free videos a week. I mean, I realize that at four dollars a month, they are no longer free, but the average cost per video is still really low. If you do that math. If you are unable to fork over four dollars a month and you would like to say thank you or show some appreciation in a slightly different way, you may subscribe to the channel exactly once, unless you have a, like, a bot farm or something, but, which would be cool, but wouldn’t really help me. Wouldn’t really help me with this part. You can also like the videos and comment on the videos in case you weren’t aware that on the internet there’s thumbs up buttons and ways to type into things and say words and have them there permanently.

If you need help with SQL Server, these things, they’re fun for me. Doing health checks, performance analysis, hands-on tuning. I mean, the emergencies are less fun. They’re more stressful, but mostly for you, because, I mean, I enjoy myself, even during an emergency. It puts me in a calm and soothed state. Or if you want developer training so that you have fewer emergencies and you freak out less, that would probably be great for you. I can do all of those things at a reasonable rate. If you would like some low cost, high quality, the highest quality, I mean, golly, golly, I can’t, I can’t even begin to stress how high quality this training is. You can get 24 hours of it for about $150 for life with that, that coupon code. There is also a link for that in the, in the show description.

If you, there’s a link for the membership, in case I forgot to say it, and a link for the training in there. So there’s just links galore in those descriptions, in case you’re unaware that on the internet you can link web pages from another place. So there’s that. If you want to see me live and in person, you have two options right now. You can come to Seattle November 4th and 5th and see me and Kendra do two days of performance wondering on, not wondering, wondrous performance tuning on SQL Server. You can, you can do that. Or you can tell me about an event near you that you might actually go to. And I can say, hey, event, do you need a pre-con speaker? And would you like it to be me? And they might say yes.

And then I might show up and do it. So that’s what you got right now. With that out of the way, let’s go do some SQL. Let’s do a query, my friends. You know, query real hard here. So the purpose of today’s video is to tell you, tell you a little bit about, uh, DOP and bitmaps. So the first thing you should know, uh, and this is, this is outlined, um, in a, in a fairly old Paul White article, uh, where even in a serial plan, uh, that gets a hash join, you, there’s like some invisible bitmap. Uh, I can’t imagine that it does much of anything because, um, well, you’re, you’re going to see why I imagined that here, but, uh, he’s way better at explaining it than I am.

Uh, what I’m going to try to explain to you is how the degree of parallelism of your query can make bitmaps more or less effective. And basically the, the tall and short of it is that when you have a tall DOP like eight or 16, bitmaps are more effective than if you have a short DOP like two or four. Uh, I skipped six on this one because six didn’t really change much of anything.

So I’ve got the same query four times. I got max DOP two. Count them off. Do pushups. I want you to do DOP pushups right there. All right. So you owe me two pushups and here we have max DOP four. And this is where you owe me four sit-ups. All right. And here’s DOP eight. And this is where you owe me eight jumping jacks.

All right. And then we have DOP 16 and this is where you owe me $16. Remember those numbers because we’re going to look at them again when we look at the query plans too. All right. So way up at the top here, we have a parallel execution plan.

And, uh, there is a bitmap in this plan. And remember, if you’ve watched other videos of mine about bitmaps, you know that the bitmap, well, it gets created here. Where it gets used is typically down somewhere in here. Some bitmaps can get stuck at the repartition streams. Some, some bitmaps get pushed right down to the, uh, the table that we’re hitting.

Which is the case for this one. We have this predicate. We have an in-row bitmap, which means that SQL Server can start filtering out rows way down when it starts reading pages that don’t, that, like, obviously don’t match the bitmap. Even with, you know, bitmap in place under certain conditions, you still have to have a residual thing here. We don’t though.

Because, uh, the ID column in users, uh, is a, uh, not nullable integer. And the, uh, user ID column in the badges table is also a not nullable integer. So the not null integerness of those two columns, it would also work for not nullable big ints, uh, and some few other data types.

It’s not strings, uh, but numbers generally. Yeah. Uh, you could get similar behavior where you don’t need the residual predicate there because SQL Server’s like, I got it. So, with that out of the way, uh, let’s look at how effective that bitmap was.

And the answer is, not very. Uh, that bitmap was not able to filter out most of these rows. We go from 2465710 to 2465701.

So that bitmap at DOP2, where we, where we built exactly, like, two hash buckets, uh, well, uh, that got rid of nine rows. So, not very good there. Um, I don’t know, maybe SQL Server had, like, more, maybe SQL Server thought that would do better.

I don’t know. But that, that clearly stinks, right? Not, not a good time there. If we go look at the DOP4 query, well, we got a few more out.

At least we got, I don’t know, about four, 39 rows that time, right? 2, 4, 6, 7, 5, 1, 0 to 2, 4, 6, 2, 4, 6, 5, 6, 7, 1. So, we, we got down by about 39 rows there, I think.

If I’m, if I got, if I got my finger maths right, um, that, that sounds good. 39. Maybe I’m off by 10, one way or the other. And the high, high school dropout thing makes on-the-fly math a little tough.

So, DOP2, right here, this one, right? Remember, you owe me two push-ups once again. Uh, DOP4, again, not terribly effective.

You owe me four sit-ups here. And then if we scroll down a little bit, we have this query at DOP8, where you owe me eight jumping jacks. This one does a little bit better.

Not a lot better. Slightly better. Uh, this one at least changes the second number, right? We went from 2, 4 to 2, 3. So, that, that one actually got us down a little bit.

So, the DOP8 bitmap so far has done the most work. Good job, DOP8. Now, in real life, most queries, uh, I would be happy to stop at DOP8 and be like, well, you know what? Uh, you know, there’s usually kind of diminishing returns after DOP8.

Sometimes it’s just, it’s just not as good, right? Like, you just, like, you’re not, you don’t keep scaling linearly as you add threads after DOP8. The stuff like that totally happens all the time.

I know, because I, I, I test stuff like this all the time. Uh, it’s part of my job, figuring out what the best set of query stuff to do is so that they go as fast as they can. Sometimes part of that is testing higher DOPs to see if anything remarkable in the plan changes.

Now, for this plan, and I, look, for this plan, right? And I, I’m, I’m totally with you on this. Uh, it, it, it finishes and, well, I mean, so let’s start back up at the top a little bit.

But this one at DOP2, right, because it only uses two threads, this takes about three seconds. So DOP2, obviously not a very effective use of parallelism or bitmap. This one down here at DOP4, that, that did get quite a bit better than the one at DOP2.

Just still not a very effective bitmap. This one’s 1.18 seconds, 1.181 seconds. At DOP8, we do still just about twice as good.

We go from 1.1 to 673. That’s close enough to twice as fast for me. But that’s still without even getting rid of twice as many rows with the bitmap, right? So usually when I’m dealing with queries that do this sort of thing, the differences are far more profound.

There’s a lot more rows flowing around. This is just a StackOver 2013 database with a little bit of stuff in it. The data that I deal with is typically much, much larger, which makes things like testing higher DOPs, like, a lot more attractive in a lot of scenarios.

Because I want more, I want to take those, like, you know, it’s like from, you know, if you’re at DOP8 and you have an 800 million row table, you’re still looking at, like, 100 million rows per thread. At DOP16, you’re at 50 million rows per thread. And that you can do a lot more work across that, you know, it’s a lot more effective spreading those rows out further.

So, like, that was, again, that was high school math, right? That was straight division, baby. Mmm.

Felt good. Felt real good. This one down here, it gets faster, but not twice as fast. We go from about 700 to about 4.5, which, you know, give or take 100 milliseconds, that’s still a pretty good reduction. And, you know, if in a much, much bigger query, you would find, like, that that difference might be more profound.

It might be, like, you know, 4.4 and a half minutes versus almost 7 minutes, right? So, like, that would be noticeable if you were dealing with, like, a big process that did a lot of ETL and moved a lot of stuff around. You’d be, like, you know, even bigger time span.

Let’s say that was almost 7 hours versus 4 and a half hours, right? There’s, like, a lot of timescales where it would make a difference that just doesn’t make a difference in milliseconds. But look at this one, right?

I don’t know how much the bitmap made a difference here. It’s hard to tell. But if you look at this part, oh, gosh darn it, tooltip, why do you do that to me? Why do you have to bury it to the hilt?

Ugh. Look at this. Look how much more effective that bitmap got. That’s a little over 700,000 rows, I think.

Right? A little over 700,000 rows. So, going, like, from here where we got, like, I don’t know, maybe, oh, that’s the wrong button. There we go.

Control key. Now we got it. From here where we go from, like, 2.4 million to 2.3 million, it’s not that great. But this one down here where we go from 2.4 million to 1.75 million, or 2.4 to 1.7, that’s probably a much more fair comparison. Because that is 2.46 and that’s 1.75.

So, you know, decent baseball player money, I guess. If you look at that, then, like, that higher dot contributed to a much more effective bitmap operator. So, what is the takeaway here?

If you have parallel queries like this, and you’re able to, you know, sort of fit this basic plan shape where, you know, you probably scan and index because you’re not going to do a lot of seeking with hash joins usually. You can if there’s other indexing stuff involved and there’s other where clause stuff involved. But just, you know, let’s just say you have a big parallel seeker scan over here, and you have a big parallel seeker scan down here, and somewhere in between them, you have a bitmap operator, which is going to look a little bit different if your queries are running in batch mode.

If you’re running in batch mode, you’re not going to see a bitmap operator in the query plan. You’re going to have to, like, right-click on the hash join operator and go to the properties, and it’ll say, like, is bitmap creator equals true if it did create a bitmap. So, this is like a row mode plan.

If you’re running stuff in batch mode, it’s going to be a little bit different. But let’s say that you have a seeker scan and then a bitmap and then another seeker scan, and there’s a hash join involved. It’s something to look at how effective that bitmap is.

Usually, the lower the number of rows that come out compared to – so, like, bitmaps don’t always do cardinality estimation. Or, rather, bitmaps can make cardinality estimation look like it’s just really wrong here. But really, it’s the bitmap being really effective that makes it look really wrong.

It’s just, like, the better a bitmap gets, the worse this estimate looks, right? Because this thing is basically just, like, table cardinality, right? Because there’s, like – we’re not, like, a seeker scan or a predicate down here.

We’re not – there’s no, like, where clause that’s saying, like, you know, where users’ reputation is greater than 100,000. There’s nothing to, like, you know, say it might be less than table cardinality, but we’re reading out of there. The only thing that you get is the bitmap, and Seawil server may not know how effective that bitmap’s going to be, obviously, because it uses it at DOP2 where it sucks.

And down here – so, like, the worse this estimate looks, the better the bitmap was usually, right? As long as there’s no other predicates involved that might have been right or wrong or somewhere in between. So, when you’re messing with queries that have this particular pattern in them, sometimes it is worth trying to adjust the DOP up to a higher number to see if, A, the query gets meaningfully faster, right?

Because adding more CPU threads in, spreading the rows out on those CPU threads generally can get more efficient up to a certain point. The other thing is that sometimes it can make that bitmap way more effective, which means you’re reading far less out of this table, and you’re putting far less into the join up there. So, you know, there’s a lot you can do that, you know, makes SQL Server’s job a lot easier as bitmaps become more effective.

And again, going back to the example of, like, you know, bigger ETL processes, larger overnight batch stuff, even, like, big reporting queries that may run for a long time, being able to get this sort of impact on both the overall time and the effectiveness of the bitmap, which probably does contribute to it a bit, at least a little bit, can really help with performance.

So, keep all that stuff in mind as your tuning queries, because, gosh, if I have to do it, you have to do it, too. It’s not fair if only I have to do all this stuff, remember all these things. I have to write stuff down and record videos, and I have to, God, I have to read my own writing sometimes, and I have to watch my own videos sometimes, and the one thing I hate doing is hearing my own voice.

Not fun. So, anyway, that’s about enough for this one. Thank you for watching.

I do so very much hope you enjoyed yourselves, and I pray to whatever might be out there that you learned something. If you didn’t, I don’t know what you did for the last 16 minutes. You don’t have to tell me.

Personal choice. Just sit here and watch this if you’re not learning anything. And so, enjoy yourselves. Learn something. Hire me to do stuff. My rates are reasonable.

I am going to trademark that phrase, because I feel like it is now synonymous with the Darling Data, Erik Darling brand. And I will see you soon in another video where I will talk about something with equal effervescence. So, there we go.

Thank you 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.

Inconsistent Error Handling By SQL Server

Posted on October 9, 2024January 22, 2026 by Erik Darling

Inconsistent Error Handling By SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the fascinating and often perplexing world of inconsistent error handling in SQL Server. Specifically, I explore how certain errors can cause entire batches to roll back while others only affect individual statements within a transaction—demonstrating this through a series of examples with a table called `transaction_test`. By walking you through these scenarios, I aim to highlight the importance of setting `XACT_ABORT` on for stored procedures and scripts that handle multiple modification queries. This practice ensures more predictable behavior and helps avoid unexpected rollbacks due to conversion errors or other inconsistencies.

Full Transcript

Erik Darling here with Darling Data. And, boy, we’re having a great day here at Darling Data Studios, getting all sorts of important work done. And I’m going to showcase some of that very important work to you today in this video about inconsistent error handling in SQL Server. I don’t mean your inconsistent error handling. I know you. I know you handle errors inconsistently anyway. We’re not talking about that. We’re talking about how SQL Server handles certain errors inconsistently and how that can affect what rolls back in a batch of queries. It’s going to be fun, I promise. You’re not going to lose your mind anyway. It’s going to be a great time. So, before we get into all that, let’s do the normal spiel and routine here because this is, gosh, this is just my favorite part of every video. Getting to sell myself a little bit, one piece at a time.

You can subscribe to a very low-cost membership to support my continuing to record these videos. I’ve got to pay for the electricity somehow, I guess, right? There’s a link to sign up for the memberships down in the video description. You can get one for like $4 a month. In the future, there will be more stuff for subscribers. But right now, I’m dealing with a lot of pre-con material and writing and all this other stuff and I just haven’t had time to build that up yet.

But, if you get in early, you’ll get more stuff because that’s how much I care about you. All of this content is, of course, free. So, if you don’t have the $4 a month or maybe, I don’t know, you just hate me that much and you won’t like spite watch these videos and you just want to like, I don’t know, keep making me waste electricity, You can do other things to show me how much you hate me, like subscribe to the channel so that you can get spite notifications every time I publish a video.

You can give me a spite thumbs down if you want. I mean, go ahead. You’ll be like, you know, the only one. And you can leave me spiteful comments if you also are feeling particularly outrageous on that day. If you need help with SQL Server, and spite or not, you realize that I’m pretty good at it, I can do any one of these things and more.

And as always, my rates are reasonable. So, you should hire me for these things because I’m better than everyone else at them. If you need low cost, very high quality training for SQL Server, I have things from beginner to intermediate to expert. There’s 24 hours of it and you can get it for 75% off for life, which means about $150.

And you can, you know, depending on how long you live, that could be a great deal. If you want to see me live and in person, if you want to see my, my, my human presence, my corporeal form, appear on a stage in front of a laptop and talk about all things having to do with SQL Server performance, you can catch both Kendra Little and I for two days of performance tuning magic and miracles and witchcraft and all sorts of other.

We’re not going to kill a goat, don’t worry. But that’s November 4th and 5th at Pass Data Summit in Seattle. If there’s an event near you that is in need of a pre-con speaker and you think, hey, I bet Erik Darling would like to come here and pre-con this, this, this thing.

Let me know what that is, because there’s a pretty good chance that it’ll show up. Who knows? Right? That’s the worst that could happen.

And with that out of the way, let us continue our party extravaganza with SQL Server and its inconsistencies. All right. So here’s the setup. I have a table called transaction test.

And I’m going to start just by clearing the whole thing out. And I’m going to put in three rows with just default values. There’s a check, there are a couple of things on this table. There’s a clustered primary key on the table, which wonderful.

We should, we should have those for our, you know, transactional tables. Look at this great cluster of index. There’s also a check constraint on this table. It just checks to make sure whatever data goes in there is greater than or equal to get date.

Okay. So that’s, that’s the only thing that we care about in this one. So if we look at what’s in that table now, we will see that there are three rows with this dead giveaway date to let you know that I’m indeed a dork recording on a Saturday. God help.

So, and that’s about it there, right? Nothing too weird, wild, crazy out of the ordinary. Here’s what I’m going to do. I’m going to set no count. Well, actually, I’m going to set no count on and I’m going to set exact abort off, which is a worst practice.

Usually for anything like this, you would want to set exact abort on to avoid exactly this kind of strange oddity, this insane confusion. But I’m going to, I’m turning it off to show you what will happen if you do things in the worst possible way. You want to do things in the best possible way, which is to turn exact abort on if you’re going to do stuff like this.

If you’re not going to do stuff like this, I don’t care what you do. You can, I don’t know, do whatever you want. So we’re going to begin a transaction and we are going to make sure that set exact abort is off or not on, right?

We’re going to go like the is not trusted foreign key, right? Set exact abort is not on. And then we’re going to update this table, which is totally fine.

That’s going to work. It’s in the transaction. We’re going to select from the table and that’s going to look right because we have incremented this to the first when rent is due and why you should subscribe and hire me and buy training because rent is due people. Rent is due.

And now we’re going to delete row number two from the table and that all goes fine. And this is going to look right. And that’s also going to look right now too, right? That row number two is gone.

We have gotten, we have deleted row number two. And now we’re going to do this. And this is going to fail because we are violating that check constraint that we put on the table, right? We can see all of this stuff happened here.

And the important thing that I want you to pay attention to is at the end of all that red text, there’s a little line here that says the statement has been terminated. Right? That statement was terminated because of the check constraint and keep that in mind for later.

So now what the table looks like is exactly what it looked like before. Row number three is, well, the row number two is gone. Row number one is a day ahead, but nothing happened to row number three because that update failed.

All right. We’re going to commit that. And then before we go back up to the top of the script, we’re going to do a little switcheroo here. And we’re going to instead on the second time through, rather than just rather than violate that check constraint, we’re going to have a conversion error.

Right? So we’re going to try to set the date column to something that is absolutely not a date. Right?

Okay. So let’s start over again. Let’s rebuild our table. Right? And actually, before we do that, just to make sure that we don’t have anything open that’s going to cause anything weird, we’re going to make sure that transaction is extra committed. Because, Lord help, if I mess up on the second time through because I didn’t make sure that that was done.

Bush League. You’ll probably see that in someone else’s training, not in mine, though. So I’ve basically recreated the table.

I put all the same stuff in there. And we’re going to follow the script in the same way. We’re going to set no countdown and we’re going to keep XactiBort off. Again, a worst practice.

An absolute worst practice. You don’t do this in your queries. You’ll be mad later if you don’t. And then we’re going to begin a transaction. And then we’re going to just double check to make sure XactiBort definitely not on.

Definitely a bad idea. Definitely not a good idea to have XactiBort off. XactiBort not on.

Right? So we’re going to go through. We’re going to step through. We’re going to update that. And we’re going to say, hey, what’s in there? And that’s correct now, right? So that’s 901. So that got moved forward.

Great, great, great. We’re going to delete row number two. And that’s going to be absolutely fantastic. And we’re going to select now and look at this. And we’re going to say, yep, row number two is gone.

We have passed all these tests. But now we’re going to do this. And what we have here is SQL Server just says, conversion failed when converting date and or time from character string. Now remember, when we violated the check constraint, there was a little piece of blackish, grayish, not red text under there that said, statement has been terminated.

We don’t have that on this one, do we? SQL Server doesn’t say anything additional here. It just says there was an error.

Sorry. Yeah, that’s crazy. But now look at what happens when we run this. All three rows are there and all of them are back to their original data.

So this one never changed because the conversion failed. This one got deleted, but is now back. And this one no longer has the date of 0901.

This one has a date of 831. So the update that pushed that forward a day also got rolled back. Now, to me, before we do that, the reason why this error message is in here is because if I try to run either one of these, this one will say, the commit transaction request has no corresponding begin transaction.

So that means our transaction got killed and completely rolled back. So like, of course, I can’t roll it back either, right? So this one here says the rollback transaction request has no corresponding begin transaction, which is why I have this sort of slash thing in here.

In real life, you would never see the commit slash rollback. It would just say one or the other like I just showed you. But that’s why that’s there in shorthand because that’s what you get from either one of those.

So just to sort of reiterate, all the rows got rolled back to their original state because we hit a conversion error instead of just like some other kind of error. Now, the full list of errors that will cause a batch to completely roll back if you hit them is not really documented anywhere. The closest that I’ve found is in Erland’s article.

So, where was that hiding? There we go. There’s not, it’s kind of hard to find your way around in these things sometimes. So, there are some things that Erland has brought up here.

And one of them is most conversion errors, which is this line right here. I don’t know what most means. I don’t know if there’s a conversion error that would not cause that to happen.

But I do see that most conversion errors would cause that to happen. Which is pretty wild. I also think that this one is pretty wild.

Arithmetic domain error is like squirt on a negative number. Why would you do that? It’s mean. So, this is sort of like the closest I can find to like an actual list.

There might be a longer list out there somewhere, probably in like SQL Server source code, that isn’t available to human beings. But we can get a partial list of that stuff here.

But anyway, the reason why this came up is because I was trying to do a simple demo that showed, you know, exactly kind of what I was trying to show when the check constraint got violated. Except I, you know, without, sort of without thinking much about it, I was just like, oh, I’ll just do the, this is not a date excel.

It’ll be funny. Everyone will laugh. But it turns out, I outfoxed myself on that one. And I thought I was crazy. And I was like, why is this thing doing this?

And of course, I had to revisit Good Sir Erland’s article. And that section was in there. And that at least partially explained my problem. It’s just something that I totally kind of forgot about after, you know, looking at that article a billion times.

So it can happen to anyone. But the important thing here is not that I’m forgetful or not that I did something silly and foolish and now I decided to maybe pass something along to you that will help you down the line in the future if you’re ever doing this stuff.

My, my, the, the really, the, the big takeaway from this video is that if you are going to begin a transaction and you are going to run multiple modification queries in that transaction and you are going to do that in some facility like a script or a store procedure or really anything, like anywhere you’re going to run this sort of thing.

Please set exact abort on. Do not use exact abort off. If you set exact abort off, you’re at this sort of undocumented women fancy of stuff like that conversion error causing the whole thing to roll back, but other errors causing only the one thing to not roll, not roll, not commit, right?

So the other things stayed, right? The other changes were there. Just that one thing failed when it was the check constraint violation.

When it was a conversion error, whoo, it was awful. It was, it rolled the whole batch back and killed the transaction. So different errors will, are handled differently in SQL Server with a lot of inconsistency.

And the only way you can protect yourself from that is to say set exact abort on, on, on, not on, on. There we go. I found the right key. So if you want to protect yourself from foolishness, idiocy, inconsistencies, and all other slings and arrows, barbs and, I don’t know, spears and nunchucks and other things that would, that would really hurt in SQL Server, you should, you should always set exact abort on for stored procedures that you care about.

So, I don’t know. That’s that. And since it’s Saturday, and so are we, I’m going to finish this recording and I’m going to go do something with my family, because, um, I, I, I, I suppose, I suppose they, they might want to see me today.

I suppose you might not be the only people who want to see me today. Or you might, you might just be spitefully watching this and mashing your teeth and clobbering your fists together and… I don’t know.

I really, I don’t, I don’t understand your motivations, to be honest. Anyway, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that, uh, you will do all the things that give me money.

Uh, and, uh, what’s the other one? Uh, well, yeah, I, I suppose thank you is in order since you did, you did make it to this point. So, uh, if you, if you are still watching, thank you for watching.

And I hope, I hope you’re having a great weekend. Or you had a great weekend when this weekend was. Next weekend, I’m going to be in Dallas. But this video is not going to get published until way after that.

Because I, I, I make a lot of content. So, anyway. Off to the mines. Goodbye.

Going Further

A Little About Index Intersection Query Plans In SQL Server

Posted on October 8, 2024January 22, 2026 by Erik Darling

A Little About Index Intersection Query Plans In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the fascinating world of index intersection plans with Erik Darling from Da Darling Da Data. We explore how these plans come to life when your table has multiple indexes and SQL Server decides to use a combination of them in a unique way. Unlike simple index unions where data streams are concatenated, index intersections involve joining those indexes together to produce the result set. I also share some practical advice on managing indexes effectively—avoiding unnecessary single key column indexes and opting for compound keys with included columns for better performance. Plus, if you’re curious about upcoming events or need personalized SQL Server support, I discuss how my services can help. Don’t forget to join our community by subscribing, liking, and commenting!

Full Transcript

Erik Darling here with Da Darling Da Data. All those Ds. Deepen the Ds there. In today’s video, we’re going to talk about, drumroll please, index intersection plans. No jokes about me not having a driver’s license, please. These are a type of query plan that you will see when your, you know, table has multiple indexes on it and SQL Server chooses to use some combination of those indexes. But it’s a little bit different from the index union thing because rather than just concatenating the two streams of data, we do something a little bit different and we’re going to talk about that in a couple of seconds as soon as we talk about how you can buy things from me. If you would like a membership to this channel, four bucks a month, not bad. That’s like, I don’t know, with inflation, that’s like quarter of a box of mac and cheese. Right? If you would rather have a quarter of a box of mac and cheese, I totally understand. So would my kids. Then you can do all sorts of other fun things that help you, that help me, that help you help me, like like and comment and subscribe because that’s nice things to do. If you need help with SQL Server problems, because I am a SQL Server problem fixer, I am available to do all of these things and more and my rates are reasonable.

If you would like some reasonable rates on high quality SQL Server performance tuning that will last you a literal lifetime, you’ll never have to subscribe to this chunk of training. You can get it for about 150 US dollars with this discount code. This link is also in the video description. Just like the membership thing and just like Intel timing out being able to find driver updates constantly in the background. That thing shows up like every 45 seconds. If you would like to see me live and in person without Intel driver updates timing out in the background, you can actually they might still show up because I have to use this laptop for those things too. You can catch me at past data summit with Kendra little doing not one but two days of SQL Server performance tuning magic, November 4th and 5th in Seattle, Washington.

And that’s that’s the last event that I have so far for 2024. 2024. Gosh 2025 sure did sneak up quick didn’t it. Golly feel like I just paid taxes. You can tell me if there’s an event near you that you think I might be a good addition to and if they are looking for pre con speakers, there’s a chance I’ll even show up looking pretty.

You never know. Crazy things, crazy things happen in this wild data world of ours. But with that out of the way, let’s talk a little bit about index intersection. Now, just like in the index union video, I have two single key column indexes.

And just like in the index union video, two single column key two single key column indexes are not strictly necessary for this to happen. And it’s just easier for me to do this way. I have to type less and I have to work less hard on the demo part of it, which means that I can record it and show you stuff better.

So that’s great for everyone. So in this first query right here, we are going to run a query that says where creation date is greater than this and last activity date is less than this. And the resulting query plan will miraculously use index intersection.

You can see that right here where we seek into this index and we seek into this index. But then instead, like in the index union plan, there was this concatenation operator. There was a regular concatenation and there was merge concatenation.

But now instead of concatenating, we actually join those two indexes together to get our results set. So we take all the rows from this one and we join all the rows from this one. How do we join those rows, though?

Well, it’s just like with a key lookup. SQL Server has the clustered primary key for the post table stored away as a hidden key column in both of those nonclustered indexes. Because they are not unique indexes, the clustered index key column, in this case singular, is an additional key column hidden away in there.

If this index were unique, the clustered index column would be treated like an included column. So SQL Server uses the ID column to join those two indexes together here. Just like with the key lookup, it would use the clustered index key column or columns to get data out of the nonclustered index and then seek into the clustered index to locate the additional rows that we need to get the additional columns that we need out.

So it’s almost the same principle, just with a slightly different join setup. Rather than a nested loops join, we have a hash join. And I don’t know, I guess SQL Server just felt strongly about that.

You might see other join types in there. Like you could see a merge join in there because I have put a merge join hint on this plan. And golly and gosh, doesn’t that just work out in our favor? But you can see why SQL Server did not choose the merge join plan naturally for the other query, which other than the merge join hint was identical.

Because it would have to fully sort both of those result sets in order to use the merge join. Now, you totally could see this in real life if SQL Server costed the hash join out of proportion to the two sorts in a merge join. Or if you had equality predicates on the two date columns, but two date-time columns rather.

But come on, who uses equality predicates on date-time columns? It’s obsessively difficult. Like, bleh!

I don’t know what would be wrong with you to do that. You would have to so very specifically be looking for something. Or a date column, maybe, but holy cow. Now, you might see SQL Server choose different join types.

So here, because I said it when I had to do it, we are going to use two equality predicates here. We’re not going to find any rows, and that’s okay. But here’s an example of where a merge join plan happens somewhat more naturally, because we don’t have to sort that input.

The reason why we don’t have to sort the input is because we are using two equality predicates. And if you’ve watched some of my other videos on indexes and how indexes work, you’ll already know that when you have equality predicates, like an equality… So in this case, we have an equality predicate on creation date, which means that the order of the ID column will be preserved.

And the query up above that, where we had a range predicate or an inequality predicate, greater than, equal to, less than, less than, equal to, all that stuff… That ordering is not preserved in the index. So that’s why it would have required sorting up there, but not down here with the equality predicates.

And just like with the index union plan, SQL Server is able to do index intersection plus a key lookup. So to imitate the index union demos a little bit, we are going to select the post type ID column and group by the post type ID column. And of course, since the post type ID column is not a clustered index key column, it is not going to be present hidden anywhere in either of these nonclustered indexes.

There is no invisible post type ID. So when we run this query and we get the resulting query plan, even though nothing comes back, SQL Server still faithfully executes the query. Does a seek into this index?

Does a seek into this index? Uses a hash join once again to join those two indexes together on the ID column, which is the clustered primary key. And then we have a key lookup back to the post table to get that post type ID column.

And just to sort of bring the point home that I was making before, we are outputting post type ID from the lookup. And our seek predicate is the ID column because that is the clustered primary key of the post table. That’s the column that we use in the lookup to locate the correct row for the columns that we need.

Pretty cool, right? So again, single key column indexes, generally not the first thing that I recommend for SQL Server. You can end up with some tremendously weird, complicated, probably ill-performing query plans.

If you are the type of person who creates a single key column index on every single column in the table, not usually a good idea. Compound index keys with included columns are usually the best strategy for either OLTP or, you know, reporting type workloads or rather mixed workloads. You know, there’s always the columnstore question, but, you know, that one’s a little bit too much to answer.

And a video that’s not about the columnstore index question. So we’re not going to get into that. But we are going to say thank you for watching. I hope you enjoyed yourselves.

And I hope you learned something. And I hope you will stick around and watch more videos because I have many, many more to come. Many such videos. Who knows what the next one will be about? It might even be about something crazy like doping bitmaps.

You might even put money on such a thing. If there is like a betting channel for me and what my next video is going to be about, I would heavily suggest voting on .bitmaps. I would not suggest voting on join or clause plan patterns.

I’m not saying that because I’m going to switch the order of those and make you lose all your money. I would much rather have you spend your money on me in other ways. Anyway, thank you for watching and I will see you over in the next video.

Going Further

Join Me And @Kendra_Little At @PASSDataSummit For 2 Days Of SQL Server Performance Tuning Precons!

Posted on October 7, 2024August 26, 2024 by Erik Darling

Last Year

Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper with you, and decided to submit two precons this year that we’d co-present.

Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.

You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.

Here are the details!

Day One: A Practical Guide to Performance Tuning Internals

Whether you’re aiming to be the next great query tuning wizard or you simply need to tackle tough business problems at work, you need to understand what makes a workload run fast– and especially what makes it run slowly.

Erik Darling and Kendra Little will show you the practical way forward, and will introduce you to the internal subsystems of SQL Server with a practical guide to their capabilities, weaknesses, and most importantly what you need to know to troubleshoot them as a developer or DBA.

They’ll teach you how to use your understanding of the database engine, the storage engine, and the query optimizer to analyze problems and identify what is a nothingburger best practice and what changes will pay off with measurable improvements.

With a blend of bad jokes, expertise, and proven strategies, Erik and Kendra will set you up with practical skills and a clear understanding of how to apply these lessons to see immediate improvements in your own environments.

Day Two: Query Quest: Conquer SQL Server Performance Monsters

Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.

This isn’t your typical training day; this session follows the mantra of “learning by doing,” with a good dose of the unexpected. Think of this as a SQL Server video game, where Erik Darling and Kendra Little guide you through levels of weird query monsters and performance tuning obstacles.

By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.

It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.

Going Further

We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.

Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?

twitter — pretty, pretty, pretty, pretty good

You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.

See you there!

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. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

A Little About Index Union Query Plans In SQL Server

Posted on October 4, 2024January 22, 2026 by Erik Darling

A Little About Index Union Query Plans In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the fascinating world of index union plans, a somewhat rare but intriguing query plan pattern that can sometimes appear in SQL Server. I explore how these plans work by demonstrating examples where SQL Server uses two nonclustered indexes to satisfy a query with multiple equality predicates or an `ORDER BY` clause. I also discuss the differences between simple concatenation and merged concatenation, explaining when each is used and what you might see in the execution plan. Additionally, I delve into scenarios where additional columns are required, illustrating how SQL Server can use key lookups to retrieve necessary data post-index union. This video aims to demystify these complex plans, helping you understand why they occur and how to design indexes more effectively to avoid or optimize them.

Full Transcript

Erik Darling here with Darling Data. Today’s video, we’ve got another one of our query plan pattern videos. And this one is about the wonderful, the fabulous, the somewhat exceedingly rare, index union plans. All right, I know you’re excited for this one. Before we talk about that, the usual things apply here. If you would like to get a membership to this channel for as low as four American dollars a month, there’s a link to do so in the video description. Otherwise, liking, commenting, subscribing, all completely fine activities by me. If you would like help with your SQL Server, I can do all this and more at a reasonable rate. Ding! If you would like some affordable training on SQL Server Performance points tuning, it’s tuning. I’ve got all that and then some for about a hundred and fifty USD for about twenty four hours of content. You can, you can buy that and you can watch it for the rest of your life. How long it’s good for. There is no expiration date. Aside from like, I don’t know, maybe if I die and the bills stop getting paid. That might be it. Likewise, if I die, I won’t be at this event. Otherwise, I’ll see you there on my birthday, November fourth and fifth in Seattle, Washington at Past Data Summit where Kendra and I will be talking and just doing wonderful things with SQL Server and showing you how to solve all sorts of performance mysteries and become the data whatever you are. I don’t know, there are too many titles. Everyone wants to say DBA developer, but there are like 50 million data titles now and I can’t keep track of them all. So whatever data you do, get better at it with me and Kendra at Past Data Summit. And with that out of the way, let’s go do something fun with query plans because that’s what we love doing here at Darling Data. All right, so the first type of index union plan that you might see would look something a little bit like this. And what you’ll have are two index accesses to different nonclustered indexes on your table and then a concatenation of the results of those indexes. All right, now what I’ve got up here for indexes is I’ve got one on owner user ID and I’ve got one on score. And so when I run this query and I say, hey, I want to know where owner user ID equals zero or score is greater than 10.

SQL Server says, well, I’ve got a couple indexes here that are pretty good. I can use both of these. Now, I’m not often one that recommends single key column indexes. They work out well to show you the query plan that I care about in this demo. And this isn’t necessarily solely the domain of single key column indexes. You might see this if you had two indexes that led with those same columns and had maybe different key columns afterwards or in different included columns or all sorts of other different arrangements. You might also see SQL Server sometimes choose to do two other things.

You know, when SQL Server is picking which indexes to use and it needs to fuse indexes together from a table to satisfy all the columns from a query, it essentially has three options. Well, I guess, I guess technically four options. It can scan the clustered index. It can hit a nonclustered index and do a lookup back to the clustered index. It can do index union, which is what we’re looking at, and it can do index intersection, which is what we’re going to look at in the next video. So this is index union, where it takes results from two indexes, munges them together, and then does some, well, in this case, aggregation to get the records out. So the first example that we saw just uses regular old concatenation.

So it’s just taking all the results, plopping them together, and, you know, just producing the result that we need to count things over here, right? The other option that you might see, when you have sometimes two equality predicates, or sometimes if you have an order by, you might see things change a little bit, is if we run this, and what’s different in this query is that we are asking not for where up here we said score is greater than 10, here we’re saying score equals 10. So we have two equality predicates here, and in this plan, rather than just a regular old concatenation, we have a merged concatenation. So SQL Server is essentially taking two sorted inputs that it can merge together, rather than taking two inputs where either one or both is unsorted, and just cramming them all together into one. You’ll notice that this plan is a little bit different, because rather than, and I should have maybe showed these back to back, but it really only makes sense when I talk about this one thing, and it could get confusing otherwise, and confusing sort of like using a mouse to grab tiny little lines in SQL Server Management Studio. This plan changes a little bit after the concatenation, where the the unordered version, just the straight concatenation, immediately does a hash match aggregate, and the first operator after the merge join is a stream aggregate, which indicates you have sorted input already.

Otherwise, we would have seen an explicit sort operator here, where SQL Server would have put the data in order for us. Now, we’re going to jump into the crazy world of needing additional columns, right? Because like I said, when SQL Server is choosing which index or indexes to use on a table, it has options available to it.

Right? And one of those options is to do an index that we can use a union plan with a key lookup. So if you’ll notice in this query, we are selecting the post type ID column, and we’re grouping by the post type ID column. So we need that column from somewhere.

However, that column is not a clustered index key column. It is just a regular old column in the table. So it is not automatically going to be part of our nonclustered indexes. And when we run this query, we’ll see that SQL Server has chosen a somewhat different approach, where we still do the index union, right? We still hit our indexes here, and we still concatenate them here.

But now we have a key lookup back to the clustered index to get that post ID column. If we hover over the lookup and we see what was output there, we can see post type ID. So SQL Server can use an awful lot of indexes on your tables to satisfy different parts of predicates, depending on selectivity and other costing options. And sometimes that’s great, and sometimes it’s not. Where I see it being not great is when folks have added a whole bunch of single key column indexes to, say, every column on the table. And SQL Server chooses to use a whole bunch of that and have to put all those together. And then sometimes even additionally doing a key lookup to do that. If you see query plans doing that, and they look very weird and confusing to you, and you start wondering, is this a view? Why is it hitting this? Why is this table getting hit so many times? It might, the answer just might be in the index design being used for this stuff. Now, I’m going to say not single key column indexes, but like very nearby indexes can be incredibly useful for stuff like interval queries. But we’re going to talk a little bit more about stuff a little bit closer to that in the next video on index intersection, which is when SQL Server, again, chooses two nonclustered indexes, but in a slightly different way.

All right. So, with all that being said, this tiny little chunk of, this tiny little nugget of SQL Server knowledge, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you find this sort of content useful, or at least amusing. Pick a somewhat positive adjective from the potpourri of positive adjectives you have in your head, and just give me that one. I’ll take it.

It doesn’t really matter too much to me. And I will see you over in the next video on index intersection. Again, thank you for watching. My rates are reasonable.

Going Further

A Little About Nested Loops Prefetching In SQL Server

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

A Little About Nested Loops Prefetching In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into a fascinating and somewhat humorous artifact that I believe might be the funniest SQL Server artifact I’ve encountered: a little red gate showing up in PowerPoint presenter mode. It’s unexpected and certainly adds a unique twist to what could have been a straightforward discussion on nested loops prefetching. This SQL Server feature can lead to some interesting behavior, especially when dealing with parameter sniffing issues in stored procedures. I walk through how the prefetching mechanism works, demonstrating its impact on query performance with both ordered and unordered scenarios. The video also touches on the nuances of parallel execution plans and how they exacerbate these issues. For those interested in diving deeper, I provide links to relevant blog posts by Craig Friedman and Paul White. Additionally, I share information about affordable SQL Server training and upcoming events where you can catch me live. So, buckle up as we explore this quirky aspect of SQL Server together!

Full Transcript

Erik Darling here with Darling Data, feeling absolutely magnificent today, positively capital, if you will, because in today’s video, I don’t know if you’re ready for this, you might need to strap yourself in to something, because we’re going to be entering the funniest artifact that I think I’ve ever encountered in my life, is this. little red gate thing showing up over PowerPoint in presenter mode. That’s cute.

We’ll just… I don’t know, I can pick one again there. Since I already clicked and you already kind of saw it, we’re going to be entering the sexy world of nested loops prefetching. Huh? Yeah, alright.

Hope you don’t have your tight pants on. Before we get into the incredibly lewd body world of nested loops prefetching, if you happen to like me enough to spend $4 a month, if you’re not currently supporting… As long as that $4 a month wouldn’t like come from like the $4 a month it takes to support a starving child somewhere, you can subscribe to my channel via membership.

There’s a link to do that in the video description. If that would take money away from a starving child, which I would never want to do. You can do other things to show your undying support, your unmitigated devotion to being a dated darling by liking my videos, commenting on my videos, and doing the one time maximal effort dance by subscribing to my channel.

If you are in need of SQL Server help, many of you are. I see you out there struggling, having problems, and can’t tell which end of the pants go on first. If my rates are reasonable. I can do this stuff. I can do more stuff at a reasonable rate. Whatever you need, let me know. If you are in the market for some affordable SQL Server training that will last you for the rest of your life.

I have over 24 hours of it available streaming. You can just play it and learn. Like, put your head… fall asleep with it on. Whatever you want to do. And you can get that for about $150 with the discount code SPRINGCLEANING there down below.

If you would like to see me live and in person. If you would like to see me up and talking about database-y things. In the flesh, as it were. But again, this video is going to be so sexy that I don’t think we need to add the word flesh to it. It might just be overkill.

You can catch Kendra Little and I performing two days of SQL Server miracles, November 4th and 5th in Seattle at Past Data Summit. And as always, if there is an event nearby you that you think I would be a good addition to. And they are in need of a pre-con speaker.

You just let me know what that event is. And perhaps I can work out some way of showing up. Because that’s what I do best is just show up. Like I do here. Every day.

So with that out of the way, let us continue on to the nested loops prefetch party. Alright. So I believe I’ve already created this index because it says query executed successfully.

And I did. We are large and in charge here. So first off, no video about something so just titillating would be complete without blog posts from some of my favorite bloggers of all time. Craig Friedman, Microsoft fella, used to write a whole bunch of really great stuff about SQL Server.

From what I understand now, he’s busy trying to figure out how to use an iPhone. And of course, Paul White has a good article about this stuff as well. So if you are interested in diving further into this, these links will be available in the video description.

You might have to do something so bold as scrolling to find them. Some people will ask me where to find things. And I will say, you did not scroll far enough.

I might start charging for butt wiping lessons for some people who refuse to read things. So what I’m going to do is I’m going to first drop clean buffers. And I’m doing this because what I want to show you is that when data that you are prefetching is not already in the buffer pool, you will do additional reads from disk.

Because SQL Server reads ahead to get data that it will need for other iterations of things. It will go and do that ahead of time so that it doesn’t have to issue synchronous IOs all the time. Because, well, I guess at the time, you know, the spinning disks that were available when all this prefetching stuff went in there were not very good at supplying data quickly to CPUs.

Granted, that story has gotten better except in the cloud. So if you are in the cloud, this might still be relevant to you. I don’t know.

So we are going to do this. And I want you to note that I am selecting the top 1,000 here. That’s one and three zeros. All right.

There we go. One, triple zero, thousand. And if we look at the query plan for this, though, we will see that SQL Server went ahead and read almost 1,400 rows or almost 1,400 or did rather not wrote SQL Server. Yeah, did almost 1,400 additional, almost 400 additional rows over the thousand that we have selected there.

And that was because SQL Server was like, oh, I might need those later. And if we go and we hover and we hover over the nested loops to get the tooltip up because we don’t want the tooltip interfering with the right click, which is one of the worst user interface things that happens in SSMS. If we right now right click on this so that we can get the properties overlaid over the tooltip and we go over here, you will see that we have an optimized nested loop that does not show up in the tooltip.

And that the optimized nested loop used, used, used, used, used, used, used. I’m just going to mangle everything today. The unordered prefetch.

So it went out and just sort of scatter gathered stuff from disk. It went, you, you, you, you, you, you, you, you, you, you, you, you, you. It was like one of those, like grocery shopping challenge shows where it’s like you need to put as much expensive stuff in the cart as possible. And someone just runs down the aisle almost shoveling everything in.

We can, of course, change that attribute by asking for stuff in order. And if we run this and we look at the nested loops here, we will now see that we did an ordered prefetch of things.

Right. But this nested loops is now not optimized, but it is ordered from the prefetch, from a prefetching scenario. And this one also did also read an additional 399 rows here.

Okay. So we got, we got that. We got that going for us. Now this will be exacerbated in parallel execution plans because you’ll have multiple threads rushing out and doing prefetchy things.

Like on this one where we read 1700 instead of 1000. That’s up from the 1399. We did some, did some extra work in there.

And this one down below where we will read an amazing, a whopping 1410. Right. So 410 additional rows.

So that, that will, that will change with a parallel plan. But that’s not really the point of this video. The point of this, the point of that stuff was just kind of show you where you see the nested loopy stuff in there. And also to, um, I guess kill a little bit of time.

I don’t know. I did want to show you like the additional read stuff. So, uh, where this can get really nasty is in store procedures where parameter sniffing is a thing. So I’ve got this store procedure ginned up here.

And it’s going to select the top 50,000 rows from the post table. And just to make things easy to demo without having to worry, without having to fiddle too much with things. Um, I’ve got it hinted to use the specific index that we want.

Uh, and I’ve also, uh, got this set to max.1 to, um, sort of highlight where these things can be particularly problematic. Uh, also up at the top, we are doing the same thing that we did above with the checkpoint, the drop clean buffers and a slight wait for delay to let the buffers clean out. So, uh, we’ve got this thing created.

At least I think we do. Let’s double check that there. And, uh, what I’m going to show you is what happens when this. So this is just like another weird parameter sniffing thing. Because some very low numbers of rows, SQL Server will say, oh, we don’t need an optimized nested loops.

We don’t need to prefetch anything. We’re just, it’s very, it’s a very small amount of data. We don’t have to worry about doing any of the like more involved stuff to optimize IO retrieval. So what I’m going to do is I’m going to grab the top.

Well, I mean, it’s still doing the top 50,000, but this person only gets one row. Most of the one second that we just waited for that was the drop clean buffers thing. If we go in the query plan and look what happened, this thing actually finished in about 13 milliseconds.

Right there. So if we, uh, get the properties of this, we will see that this is not optimized and that the, uh, the prefetch attribute is just straight up missing. So SQL Server doesn’t tell you, uh, SQL Server only, rather SQL Server only tells you if, uh, uh, ordered or unordered prefetch is are used.

If neither one is present, that thing is just missing. It doesn’t say like ordered pre like prefetch false or anything like that. It’s just straight up not in there.

So if we go and we look at how this runs. So this, this user, this is John Skeet. John Skeet has about 28,000 rows in the post table. Uh, this will get pretty slow.

All right. We were just waiting and waiting and waiting. And since John Skeet, since we’re getting the top 50,000, but we don’t quite have 50,000, we only have about 30,000 rows in there. I did want to grab one user ID.

That’s the community user ID that has like 220,000 something rows in there that, uh, will take a little bit longer than this even. But if we go look at the query plan now, you’ll see that we spent a lot, not much time in here. Right.

Notice we’re still harboring under the estimate from, uh, the, the, the last compilation. So this is like a parameter snippy thing, but now this nested loop section takes about six seconds or sorry, uh, this key lookup takes about 5.9 seconds. A nested loop takes an additional a hundred milliseconds.

And then we finish out the top there. So not doing that, that read ahead IO really hurt us in here. Now, if we go and we get for user ID equals zero, which has the, again, the 220,000, some odd rows in there. And we’ll actually fill out the 50,000, uh, uh, 50,000 row row goal from the top.

Things will look, um, slightly worse, right? That was, that was about six seconds. And now we are up to about 10 seconds, right?

So this key lookup situation took 10 seconds, one, zero tough time. And where that changes though, is if we recompile this and we go back and we run this the first time for John Skeet, right? So the 22656 user that didn’t take five something seconds.

Did it that took about half a second, right? There’s 531 milliseconds. And if we go look at the nested loops join now, uh, even though the optimized part is false, the ordered prefetch thing is true. So SQL Server did go out and do a bunch of prefetching to optimize the IO from the lookup, right?

And now if we get out of here and we go look for user zero, this will also be pretty quick, right? It’s not going to be quite as quick as John Skeet, but we’re only, we’re only about 300 milliseconds, uh, different rather than like three seconds different, right? Cause remember these numbers almost scale in the same way where, when we ran for user ID six, whatever first that had one row and we didn’t get the prefetching, uh, John Skeet then took about 5 point something seconds.

And this one took about like about 10 seconds. So these go almost up in the same amount, but just in milliseconds rather than, uh, rather than full seconds. So if you are ever dealing with a weird situation with a query plan or even a, you know, something in a store procedure or parameterized, uh, parameterized query where you have a nested loops join.

And, and like the plan doesn’t plan itself doesn’t change. And you, you suspect parameter sniffing and you’re like, maybe you go to query store, maybe you use SP quickie store to look at quickie store. And you’re like, well, this thing generates different execution plans, but they all look the same.

They all use nested loops. What’s going on? Uh, doing something like this and looking at the properties and figuring out if sometimes the, um, the nested loop join uses prefetching and sometimes doesn’t is probably going to be your answer. Now there are various ways of fixing that.

Um, you know, if you have, if you know kind of what you’re doing, uh, you may be able to add an optimized forehand. Uh, to your query to continuously get the prefetching, right? Cause the prefetching is based on cardinality estimation for low numbers of rows.

SQL servers like, no, we don’t need to go out of our way for that. Um, I, if, if I’m remembering correctly, there’s like a, it’s like 25 rows that you, at minimum that you need to get the, the prefetching stuff. But, uh, those details may have changed and may actually be subject to change.

I don’t, I don’t, I don’t recall, uh, exactly how long ago I had, I had figured that one out, but, uh, it may, it may be different now. And, um, it’s kind of a pain in the butt to find all the different test scenarios for these videos and still have them take a reasonable amount of time where my camera will stay on and won’t overheat and all that other good stuff. So anyway, uh, the takeaway here is if you’re dealing with a situation where a query continuously produces a nested loops join, either for a key lookup or even a table to table join, and you, uh, produce the same nested loops plan.

But one of them is much slower than the other sometimes. The answer is probably going to be at the nested loops operator where you are going to be missing the prefetch for some and not missing the prefetch for others. The prefetch can make a very big difference, especially on crappy hardware.

So with that out of the way, with that brilliant recap out of the way, everyone, I hope everyone’s doing okay now. I hope everyone is sufficiently covered from, from this, this just charged video. Uh, uh, I hope you enjoyed yourselves.

I hope you learned something. Uh, I hope that you will continue to watch these wonderful videos and, uh, support this channel in whatever way you, you are able to. Um, cause I, I do appreciate you.

Anyway, uh, that’s about it for this one. Uh, thank you very much for watching. Um, I believe it’s, it’s nearly time to go to the gym and, um, you know, make, make, get a, get a physical workout, break a, break a physical sweat on top of the mental sweat that I spent talking about nested loops prefetching. Ah, all right.

That’s good here. Thank you for watching.

Going Further

The Curse Of Cursor Options In SQL Server

Posted on October 2, 2024January 22, 2026 by Erik Darling

The Curse Of Cursor Options In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the often-overlooked world of cursor options in SQL Server, sharing insights from my experience and the wisdom of Paul White. I explore how different cursor options can significantly impact query performance, especially when dealing with parallel execution plans and memory grants. By examining practical examples, I demonstrate why it’s crucial to carefully consider cursor options before implementing them, as their default behavior might not always be optimal. Whether you’re a seasoned SQL Server professional or just starting out, this video offers valuable lessons on how to fine-tune cursors for better performance without rewriting complex logic from scratch.

Full Transcript

Erik Darling, right here with you. With Darling Data, too. The force of Darling Data behind me. The strength and might of Darling Data. That’s where we’re at. That’s why we have a barbell for a logo. Anyway, in this video, we are going to talk about the curse of cursor options. Because this is, I mean, you know, you can talk till you’re blue in the face about, you know, not using cursors. But you end up seeing them in a lot of code. And, you know, depending on how much time and effort and, you know, how much someone has paid you, you might find it easier to adjust the cursor options that you’re using, rather than try to rewrite this whole, you know, rewrite this whole, you know, this whole big, complex, undoubtedly nested cursor scenario with, like, you know, decades of business logic built into if statements and checking things and whatnot. Because I’ve certainly been in that position. And I want to say right off the bat that everything I’ve learned about cursor options, I’ve learned from Paul White. I have a tremendous amount of respect for his ability to know what I’ve learned about cursor options.

what kind of cursor to use immediately. I don’t. I always have to, like, refer to notes and think about things and try different stuff. I do not have the mental capability to know exactly which cursor options to use immediately. So I’m going to put that way out front there. You know, there is stuff that I’ve, like, read about cursors from other sources, but I’ve only ever understood it when Paul told me. Which, I don’t know. Maybe it’s the accent. I don’t know. I don’t know. He doesn’t type with an accent, so it’s kind of weird. God, I’m messing this whole thing up. So embarrassed. Anyway, before we talk about this, let’s talk about you and me. Because I have this here YouTube channel, and you might enjoy it so much that you think, you know what, I can part with four bucks a month to make Erik Darling feel good about himself. You might also think, I’m not giving this twit four bucks a month. You might want to like or subscribe or comment, one of those things. If you subscribe, you will be joining the legion of over 4,300 other data darlings in the known universe who get lovely flowers thrown at their faces and feet and then you will be able to find out the way out front there. And I’ll see you next time.

If you’re into that sort of thing, it might be something you’d consider. If you need SQL Server help, maybe you have a lot of cursors in SQL Server and you need some help tuning those. Apparently, apparently I can help with that. I ain’t too proud to bring that up. But in general, if you need a health check, performance analysis, hands-on query and index tuning, or I don’t know, anything else really. I’ll do all the knobs and buttons on that thing. If you’re having a SQL Server crisis or if you want developers to get trained so that you have fewer SQL Server crises to call me about, all good ways to spend money on me. If you want to get some low-cost training for a SQL Server, whether you’re at the beginner, intermediate, or expert level, you can get all of mine for life for 75% off. There are links to do all of these things in the video description.

So you should go look at the video description where the links are and click on them. It’d be the smart thing to do. If you want to see me live and in person, there are no links in the video description for this. That would be overbearing and I would have to change stuff too often. So this just gets a slide. Friday, September the 6th, I will be…

Actually, this video might be coming out on the Monday after I do this, so it’s going to be too late for that. Forget I said anything about Dallas. November 4th and 5th, I’m going to edit this slide apparently. November 4th and 5th, I will be at Past Data Summit with Kendra Little co-hosting two raucous…

rockin’… It’s going to be great. Two days of performance tuning content all about… well, I mean, obviously SQL Server. I don’t know. What else am I going to do with my life? So you can catch me there.

And with that out of the way, let us begin the festivities. Let us join the dance, my friends. All right. Let’s get over to SQL Server Management Studio. Let’s make sure that everything is in good working order here. And what I’m going to do is I’m going to run this query because this is important.

We’ve done exercises like this before where, you know, you come up with a query that runs reasonably fast, but then you put it in some other context, like maybe inserting into a table variable or something, and all of a sudden performance bites. And you’re like, what happened? I don’t understand it.

The answer, my friends, is always going to be in the execution plan. Always. It will be there. It will tell you a good general selection of things.

Let’s put it that way. So let’s run this query. And this query will finish quite reasonably in a bit under a second, right? We get a parallel execution plan.

Everything looks pretty okay here. If you were to write this query in real life, you would probably say, cool, it’s under a second. I’m done. I’m out. Don’t need to call Erik Darling about this one.

But you might if you do some other stuff. So some cursor options, like fast forward, will prevent a parallel execution plan. Other cursor options cause SQL Server to generate weird checksums on data when cursor tables are populated.

Again, these are all things that will be in the query plan that I’ll show you. This can happen. A lot of the times people in SQL Server will just declare a cursor and let SQL Server do the rest.

That’s a big mistake. Because SQL Server can make all sorts of weird choices about your cursor. They can have all sorts of performance side effects.

So if you use scroll, keyset, dynamic, or optimistic cursors without the read-only option also included there, you can end up with really awkward query plan stuff. Because SQL Server adds this checksum to see if rows changed between cursor.

So what I’m doing here is I’m using some syntax that I wish were more popular. Because I see a lot of people try to close and deallocate cursors in the wrong place in code. Myself included.

I’ve gotten caught with my pants. I mean, I’d say around my ankles, but it was like one leg is around an ankle and the other leg is pulled up over my face with that. So I’m using a cursor variable here.

So I don’t have to worry about closing or deallocating. SQL Server will just do that when the time is right. It does complicate the syntax a little bit because you have to, like, declare this and then set the options for it later. It’d be a lot nicer if SQL Server just let you declare the cursor variable with the options you care about.

But what can we do? T-SQL does not see too many improvements, does it? It’d be nice if the summer interns would do some of that stuff.

You ever see some of the syntax available in other databases? It’s real nice. Makes SQL development nice and easy. A lot less crap to do.

T-SQL, you are looking at doing maximum work in everything. So let’s run this just as a fast-forward cursor, right? And this is what I mean to show you about the parallel execution plan thing.

You may notice that this is no longer finishing in a little under a second. This took about five seconds to finish. Here we have our no longer parallel execution plan.

And this is not a side effect of the cursor variable. If we declared a regular cursor with all the options, it would do the exact same thing. There’s no difference there.

But if we, let’s see. Oh, good. We get a warning about this. The query memory grant detected excessive grant, which may impact the reliability. The reliability of what?

I do not know. Just the reliability. The grant size initially was 1,024 KB. Massive, right? Huge.

And used was 16 KB. This is what we are warned about in SSMS. This is what we got instead of commas. Or let’s just say number separators.

It doesn’t have to be commas. I learned recently from a bug in SP Quickie Store that the French use spaces for number separators. So glad that got fixed.

I do want to keep friends with the French. They do have my favorite food and they do have my favorite place to smoke cigarettes. Thanks, France.

Anyway, that’s a bummer. And if we go to the properties over here, we don’t get a loud warning about this. This we have to go digging for. And we zoom in way over here. Let’s put that somewhere nice and near my head.

We have this non-parallel plan reason. No parallel fast forward cursor. Yes, indeedy-doody. No parallelism for you there. Be very careful where you use fast forward cursors.

Sometimes fast forward. In some scenarios, fast forward can be the best option. In other cases, fast forward can really bungle up performance. So be careful there.

My favorite cursor options are local static. But, you know, depending on what you’re doing, that might not make sense either. Cursor options are crazy like that.

So anyway, let’s look at the second example of cursor weirdness. Now, we have to quote out fast forward because we cannot combine these two things. And we have to quote in dynamic.

And what’s going to happen here is performance is going to get even worse than it was before. Not only, well, we actually do get a parallel plan on this one for a bit. But that parallel plan has to do some stuff.

That took about 10 seconds. And if we look at the plan for this, so you have this whole thing over here. So this is where SQL Server populates this sort of hidden worker table that the cursor is going to work off of later. So you have the population query, which grabs all the data you care about and puts it in here.

And then you cursor over the populated table rather than cursoring over the actual table over and over again. The trouble is, even though this is in a parallel zone, right? You can see all the parallelism stuff in here.

You can see the little zoomy buttons across these operators. Notice that this sort now takes nine seconds. It takes nine seconds because, oh, we don’t need the properties. We just need the tooltip.

Calm down. Calm down. Calm down. We have this output list. And that output list contains CHK1002, which is a checksum that SQL Server generates on the entire row that ends up in the cursor table to see if there are any changes between cursor executions, which you need to do some handling for if you’re writing that type of cursor.

It’s almost the equivalent of doing something like this, where you’re selecting the top one owner user ID with this checksum across everything in there. And you won’t see the CHK1002 in here, but you will see this EXPR1001. If I were to, there’s a reason why there’s a misspelled note over this query.

I’ll pretend that didn’t happen. Here I go making fun of the reliability, and I’m saying, don’t run this, PAA. PAA, don’t run this.

Like a mel. And this would take about a full minute to run. So the checksum that happens in here is somehow worse than what happens in the cursor. But if we give this cursor the read-only attribute, right?

If we switch from dynamic-only to dynamic-read-only, we give SQL Server that extra information. Somehow, this select top one query was not enough for it to go off of. Don’t ask me why.

And we rerun this. This will finish just about instantly because we no longer have the CHK in here. And we actually have the population query is just about the exact same as it was when we ran our top one query just by itself. So, like I said, sometimes if you’re tuning code that involves cursors, you may be a full-time employee.

It may be straightforward enough, or maybe you got paid enough to turn the whole cursor thing into set-based code. If that’s true, I’m happy for you. Usually in SQL Server, getting rid of cursors is a pretty darn good idea.

If you’re in a situation where getting rid of the cursor is not an option, sometimes fine-tuning the cursor options might be an option. And you might be able to get much, much better performance if you’re able to do that rather than rewrite the entire cursor thing. Of course, that really depends on what you do with the cursor later.

You know, well, cursors do have, you know, their own sort of overhead and issues. And, you know, I generally don’t recommend them. There are times when they can solve a problem pretty quickly and easily that writing set-based code for would not be very, very easy at all to do.

So, I understand why they’re there. And I also do my best to try to understand the options available to me when using cursors because they can have a profound effect on how fast your cursor runs. So, with that out of the way, as always, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that, uh…

God, I’m talking about curses. I hope that you’ll like and subscribe and member and training, consulting, all that good stuff. Uh…

Because, you know, that’s… That’s how I drink enough to deal with cursors. Anyway, we’re gonna call that one here. Uh…

Once again, thank you for watching. You… Uh… You have now made it to the end of SQL Server. You can shut it down now. There’s nothing left to talk about, right?

That’s good. That’s good. Uh… Alright. Bye.

Going Further

Mostly Some Updates To sp_QuickieStore, But Some Other Stuff Too!

Posted on October 1, 2024August 27, 2024 by Erik Darling

It Wasn’t Me

Duplicate join condition and missing database_id conditions by @markkeyworth in #443
Dev by @erikdarlingdata in #445
Closes #450 by @erikdarlingdata in #452
sorted the list of ignorable wait types by @HannahVernon in #453
Dev by @erikdarlingdata in #454
Update sp_PressureDetector.sql by @erikdarlingdata in #455
sp_Quickiestore: Truncate #query_text_search_not if @Get_All_Databases = 1 by @ReeceGoding in #460
sp_QuickieStore: Correct UTC offsets by @ReeceGoding in #458
sp_QuickieStore: Add the ability to sort by arbitrary orders, but particularly wait stats and “plan count by hashes”. by @ReeceGoding in #459
Dev by @erikdarlingdata in #463

A very special thanks to Reese Goding here, for working off several butts on getting some really cool bug fixes and improvements into sp_QuickieStore.

You can download all of the main SQL Server troubleshooting procedures I use in one convenient file.

Thanks for reading!

Going Further

Join Me And @Kendra_Little At @PASSDataSummit For 2 Days Of SQL Server Performance Tuning Precons!

Posted on September 30, 2024August 25, 2024 by Erik Darling

Last Year

Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.

You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.

Here are the details!

Day One: A Practical Guide to Performance Tuning Internals

Day Two: Query Quest: Conquer SQL Server Performance Monsters

Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.

By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.

It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.

Going Further

We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.

Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?

You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.

See you there!

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. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

A Bug With Inline Index Creation Syntax In SQL Server

Posted on September 27, 2024January 22, 2026 by Erik Darling

A Bug With Inline Index Creation Syntax In SQL Server

Thanks for watching!

Video Summary

In this video, I share a lighthearted look at inline index creation bugs in SQL Server, which have persisted even through updates to SQL Server 2022. This quirky issue, which I reported long ago and still exists today, adds an amusing twist to my usual technical content. As always, if you enjoy the channel and are looking for more in-depth SQL Server performance tuning, health checks, or training, there’s a special offer available: 24 hours of expert-level content for just $150 when purchased at 75% off. Plus, I’ll be teaching a full-day pre-con on Friday, September 6th, and hosting two days of SQL Server performance tuning content at Data Saturday Dallas and the Past Data Summit in Seattle. So, whether you’re looking to improve your skills or just want to see me in person, there are plenty of opportunities coming up!

Full Transcript

Erik Darling here with Darling Data. And, um, well, this video is gonna be, this is an easier one, cuz I’ve scheduled this for a Friday. I’m not recording this on a Friday. But, uh, in the order that I’m recording videos today, this is also my Friday of video recording for the day. So, I would also like an easy one, where I don’t know if I’m recording videos, I don’t have to talk a lot about anything particularly in-depth. Um, much like on a real Friday, even though I believe, even though, even though today is Monday, and this is going out on a Friday, I am exhausted. I’m not gonna say anything too goofy about Mondays, or coffee, or alarm clocks, or any of that stuff, because, uh, I don’t know.

Don’t don’t wear shorts, as they say. So, let’s talk about inline index creation bugs in SQL Server, cuz this one’s kind of funny, because I reported this a long time ago, and no one has done anything about it. It exists to this day in SQL Server 2022. Uh, I don’t know. Apparently, the summer interns were busy with other things, so. Whew. Let’s have ourselves a time. Uh, if you like my channel, and you have gotten sick of just the mere act of liking and commenting, and you’ve, you’ve, you’ve, like, subscribed all of your, you know, primary and alt YouTube accounts, uh, one, one way that you can support the channel is to sign up for a membership. There is a link to do that in the description of the video, along with links to other things, uh, that you might find useful. I don’t know. Maybe you will, maybe you won’t. Uh, it all depends on how you click them, I guess.

Uh, if you need help with your SQL Server, if it is in bad shape, uh, if performance is not good, or things are going awful for some other reason, I’m pretty good at fixing it. Uh, health checks, uh, health checks, performance analysis, hands-on tuning, dealing with emergencies, uh, training your developers so I don’t have to deal with emergencies anymore. Uh, uh, these are all things that I excel at. Um, I already made an Excel joke in the video, so I’m not gonna redo it here.

I mean, you, you deserve better than that. I’m not gonna go there. Don don’t wear shorts. Uh, so, um, yeah, my rates are reasonable. As always. Uh, if you want some very reasonably rated, uh, performance tuning content at the beginner, intermediate, or advanced, slash expert, slash crazy cuckoo brains, uh, I can’t believe you’re gonna do that with SQL Server level of training.

You can get all mine. It’s 24 hours of content for life for 75% off, which brings it to about $150 US dollars. If you’d like to see me in person, this is where I’ll be, uh, on a Friday just like this one. September the 6th, I will be at Data Saturday Dallas doing all sorts of Data Saturday things.

Uh, but on a Friday too, because, uh, Friday I have to teach a full-day pre-con. And then Saturday will be the 7th, and that’ll be Data Saturday Dallas, but it’s all, it’s, it’s all combined in there. And of course, November 4th and 5th, my, my birthdays, I will be at Past Data Summit in Seattle co-hosting two.

Bang em up, smash em up, hit em up. Days of SQL Server performance tuning content with the lovely and brilliant Kendra Little. So, um, you should come see me at both of those.

Now, without further ado, let’s repartee. Alright. Now I gotta get my head under the party hat. Just in case this is the shot that YouTube picks for like the, the splash image for my video.

This, I gotta get my head under the party hat. Make sure that’s lined up. I really should find a party hat that maybe looks like it’s on a head already.

I don’t know. I’m probably too lazy for that. We’re just gonna have fun now. Alright. So, here’s the bug as it exists. And has existed ever since inline index creation syntax was created.

Uh, now I’ve, this is sort of fitting because I did recently write a video about complaining that you can’t create a filtered index on a, or can’t create a filtered index on a computed column, something like that. One of those videos wild time, obviously very memorable experience for all.

Uh, where if you create a video, uh, create a video, if you create a table. Oh, dear me. If you create a table that has a computed column in it like this one, and it doesn’t matter if you persist it or not.

Uh, if you try to do this, uh, SQL Server will say, you cannot do that. You cannot do that with that column. You are wrong to even try.

This red text signifies your idiocy in the matter. Your, your, your idiocy in the matter and your illiteracy in reading the documentation. Uh, we can, we can prove that doubly by adding the persisted keyword and starting this whole tragic experiment over again. And trying to do this and being met with the same level of resistance from SQL Server where we cannot create that thing.

But this is where things get real wild. If we create our table with this definition, right? We’re gonna, we’re gonna drop the table if it exists.

We’re going to create the same columns right here. Uh, and it doesn’t matter if this one’s persisted or not yet again. But we’re gonna inline the index creation syntax right here like this.

Right? See what we’re doing? Very sneaky, very tricky. And if we create this table, this, this, this completes successfully.

Qua? How dare you? What, what’s gonna happen next? You know what’s gonna happen next? Errors.

Errors happen next. If I run this query, and I don’t even call, I don’t call the crap column. Uh, I don’t do anything where the definition of the, the crap column would be expanded. Uh, and I run this, I get these errors.

Invalid column name crap. Over and over again. Even though I’m not selecting crap, I’m just selecting ID. And then down here, he says, cannot retrieve table data for the query operation because the table dbo.ohyeah schema is being altered too frequently.

Because the table dbo.ohyeah contains a filtered index or filtered statistic, changes to this table… Oh, I’m sorry. This is a long error message. Bring that down a little bit.

There we go. Get in there, baby. Hey, yeah. Contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data, retry the query operation, not retrying. And if the problem persists, use SQL Server Profiler.

Profiler? Hmm. Use SQL Server Profiler, you say, to identify what schema altering operations are occurring. Even if you just try to get a simple count of what’s in the table, SQL Server will spittoon that same error message at you, and you will get all of that same awfulness in red text on the screen.

Isn’t that wild? Isn’t that a weird bug? And just when you thought you were a bad developer…

Someone got paid a lot of money for that. A lot of money. Someone is getting paid for that to this day.

Someone is on the payroll. For that. Someone… Someone got a generous grant of Microsoft Stock. For that.

Someone’s probably on a boat somewhere. Named Microsoft Stock because of that. Now, well. What can you do? Anyway.

It’s Friday. So am I. So are you. So are we. So let’s… Let’s get out of here. Let’s go do something better with our lives, right? Even though I’m just pretending it’s Friday and it’s Monday, this is…

This is the end of my recording days. So… You have no idea how relieved I am. Anyway.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something like you’re maybe not that bad of a developer. I hope that you will like and subscribe and comment and membership and training and consulting me. So I don’t get lonely.

Anyway. There we go. I think… That Microsoft should give me a generous grant for finding all the bugs that I do in SQL Server. I get…

There’s no glory in reporting them. Aside from making… Getting… Sometimes minor improvements made to their product. But sometimes they linger on for years and no one cares to do anything. So…

You know. You gotta take it as it comes, I guess. I don’t know. I’m gonna go take a margarita as it comes. Which is without triple sec or coin trope. Because those are gross.

They shouldn’t be in your margarita. They make your teeth feel like they’re wearing sweaters. To know better. You’re a grown up.

Anyway. That’s good for now. Thank you for watching.