Tag: DBA

For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable

---

Thanks for watching!

Video Summary

In this video, I delve into table variables in SQL Server and why Microsoft’s advice about their usage is often oversimplified. I start by challenging the notion that a table variable containing fewer than 150 rows is safe to use, drawing parallels between this advice and other dubious recommendations from various sources. The core of my discussion revolves around the limitations of table variables when it comes to cardinality estimation and plan shaping. By walking through an example with a table variable and comparing its performance against a temporary table, I illustrate how SQL Server’s lack of statistical information on table variables can lead to suboptimal query plans, even for small datasets. This video aims to provide a more nuanced understanding of when it’s appropriate—or not—to use table variables in your T-SQL code, emphasizing the importance of considering how these variables will be used later in queries and their potential impact on performance.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are, well, I had my sights set on something completely different. I had my gaze set upon far grander shores, but then there was a YouTube comment earlier saying, Microsoft’s advice about table variables is that as long as there’s fewer than 150 rows in them, they’re okay. Everything’s hunky-dory, just go about your business. And given the quality of Microsoft’s advice on a lot of other things, I would treat it with the same level of suspicion that I would treat, I don’t know, politicians, alligators offering to help you cross rivers, I don’t know, homeless people handing you soiled bags. There’s a lot of things. There’s a lot of things that, like, I would just be like, mmm, no thanks. Not what you’re selling today. Not interested.

So, the thing with table variables is that the number of rows that you put in them does not matter. What matters is what you do with them later. Right? So, if you’re going to use table variables, this is when you probably shouldn’t. This would be your mental checklist for times when you probably shouldn’t use table variables, regardless of how many rows you’re going to put in them. So, if you’re going to put in them. So, if you’re going to put in them. So, if you’re going to put any amount of data into a table variable, and you’re going to correlate that table variable to larger, to one or more larger tables in any way, you probably shouldn’t be using a table variable.

And that goes for anything in this list and more. Joins, where’s, subqueries, ins, not ins, exists, not exists. Anything that requires a relational activity between a table variable and another table of any meaningful size means you shouldn’t be using a table variable. So, when table variables are sometimes okay is if you want to pass data between procedures. Now, I talked how, another video where I talked about how if you use temp tables for that, you can get a rather polluted plan cache, which may not be the end of the world. Passing in lists, right? Instead of passing in a list of comma separated values and parsing it out. Often, using a table value parameter is a better choice. But even then, I might even suggest dumping the contents of your table value parameter into a temp table and then using that instead.

Where table variables usually win out in a typical speed test, unless you have in-memory tempd enabled, which if you’re on stupid managed instance, you can’t do, is if you have very high frequency code calls, like hundreds or thousands of calls a minute or second or millisecond or microsecond or nanosecond or whatever unit of time you want to deal with. I don’t know what, I don’t know what, I don’t know what the difference between metric and metric units of time is. Do they have kilominutes or kiloseconds? I don’t know. Weird thing to think about. So, and you’re only ever like putting a little bit of data into a table variable and then retrieving a little bit of data just from that table variable.

Right? So, when you shouldn’t use them, most of the time. When you should use them, very, very small controlled portion of the time. So, I’m going to talk a little bit about why the number of rows, and hopefully this is the last time I have to talk about this, because I feel like I’ve recorded this video 17 times. So, hopefully this is the last time I have to say it.

The number of rows that you put into a table variable is not what makes using a table variable okay. So, here’s a simple example of why table variables can screw you up regardless of how many rows you put in them. You ready for this? Alright.

I’ve got a table variable called at t. It’s got one column in it. It’s an integer. It’s got a clustered index on the integer. Actually, let’s fix that. Let’s make sure everyone knows that’s an integer.

Let’s not minimize it by calling it an int. I don’t like that. And I’m going to put two groups of data in there.

I’m going to put one group of data that represents a larger number of rows across tables in the Stack Overflow database. So, user ID 22656 belongs to a gentleman named John Skeet. His name has come up a number of times across my videos.

He is prolific in the Stack Overflow community. I don’t know if he still is, but at one point he was quite prolific. And then I’m going to put 99 other rows in there. And the 99 other rows that I’m going to put into my table variable are very low reputation users.

And that’s ordered by the most recent creation date. So, these are the people who joined the site and haven’t been around a long time. The reason why this matters is because table variables, even with indexes created on the columns, do not get column level statistical histograms created for them.

And SQL Server has no idea how to produce cardinality estimates for them. Now, that stuff has changed a little over the years. There’s trace flags and recompile hints and compat level 150 plus, I believe, enterprise only.

We’ll get you something called table variable deferred cardinality estimation, which tells you how many rows in total are in the table variable. But it does not tell SQL Server what the values in those rows are. Very specific about this.

Say that very, very slowly. SQL Server has no idea what’s in your table variable. It just knows how many things are in your table variable. Alright.

And then what I’m going to do is I’m going to just get a count from my table variable joined off to two other tables. And I’ve run this ahead of time. Because if I were to sit here and make you watch me run this now, I don’t think we’d be friends anymore.

So, here’s our initial insert into the table variable. Alright. Let me get rid of that tool tip and all sorts of goofy things popping up on me. And this is fast enough.

300 milliseconds. Very few people would complain too much about that. Now, we still get the inability to perform a parallel insert here. But for putting 100 rows into anything, that doesn’t matter.

Right. What matters is down here. Now, let’s look at this query plan a little bit. And we can see where SQL Server, because we’re using compat level 150 here. SQL Server correctly estimates that 100 rows are going to leave the table variable.

But again, it has no idea what the contents of those 100 rows are. And so, when it starts attempting to do joint cardinality to the other tables, everything goes right to hell. So, we mess up here by almost 2,900%.

We mess up here by a really, really big number. I don’t even know what percent that’s going to end up being. But that’s a big number with a very small estimate here.

And this all drags on for nearly two minutes in total. All right. You look at the final operator here. We have a minute and 54 seconds.

And then, for some reason, this batch, this hash mode, this batch aggregate, sorry, this hash aggregate runs in batch mode. There’s a lot of hab-hab-hab-habas in there. And so, we have to add 500 milliseconds to the 150, sorry, to the 1 minute and 54 seconds here.

So, nearly 1 minute and 55 seconds in total. All right. Tally that up.

It’s a long time. All right. It’s a bloody long time. That was a bad accent. I apologize. Never going to do that again. And the thing is that this kind of stuff does not happen with temp tables where SQL Server can generate histograms and do proper cardinality estimation with data in temp tables.

So, a couple things to do in here. All right. Let’s run this real quick.

And what I want to show you is that under different circumstances, under sort of normal circumstances, the temp table variables are backed up by sort of hidden temporary objects up in tempDB. Here we have AB575B71. All right.

That is our secret temporary object backing up the temp table. And if I run this query a couple more times, we will just get different sort of values in here. They’ll be a little bit different every single time I run this.

There’s a B11, blah, blah, blah. Now, the reason why this query is quick is because we’re just, like I said, one of the times when table variables are generally okay is if we’re just selecting a relatively small amount of data out of them. But what’s interesting here is that, you know, the data that we put into this table variable, which is ID 22656, and then a bunch of pretty small numbers.

SQL Server knows how many records are going in total are going to be in the table variable, but has no idea how to make a guess for this. If you notice, go back to the query plan, or sorry, go back to the results, we get zero records back from that. No single row in that table variable qualifies for that where clause.

But SQL Server still thinks that 30 of the 100 will. So we get a 30% guess there. We had 1,000 rows in there, we would guess 300.

We just get a stock 30% guess. If we had an equality predicate, it would guess 10%. And if we had a unique index on there, we might guess 1. But, I’m sorry, unique index and an equality predicate, it would probably guess 1.

But even that would be wrong. And all sorts of just wrong things in there. So temp tables don’t have that issue. Right?

If we run this code, now this code matches the exact same query that I ran the first time with the table variable that took a full two minutes. If we run this, notice this number might look familiar if you remember the really bad estimate from the initial plan. But now SQL Server, well, a couple of things happen.

One, we actually get parallelism in this plan. So this improves by a little bit, right? This is like 300 milliseconds, now it’s 55 milliseconds. And this query down here, that used to take a full two minutes, finishes in 15 milliseconds. Why?

Because SQL Server can make good guesses. Right? SQL Server is now, oh, you know what? I forgot to change that. Let’s make this equivalent. That’s supposed to be 99.

Let’s do that again. There we go. SQL Server can make just fine guesses now. Right?

They’re not perfectly spot on, but it was a lot closer. Right? That was only off by 1,400%. This was only off by 2,400%. Not off by like 483 million percent, whatever that was. So, again, not perfect join cardinality, but SQL Server was able to do some stuff differently, and we got a much faster execution plan because it at least had some reasonable idea of what was inside that temp table.

Right? The reason for that is that SQL Server, like I said, generates statistics on temp tables that it does not create on table variables.

Now, what I’m going to do is I’m going to use a couple trace flags in here to print out cardinality estimation information for this query. And if you look at the messages tab, what you’re going to see down a little bit is text that looks like this.

Right? And what do we see in here? Well, we see a cardinality estimate on the base table posts where we get this number, 1.7e plus 07.

And then this, CST call black box card equals 1. Cardinality of 1. Right?

And that’s going to be repeated throughout all of the cardinality estimation attempts for this query all throughout here. This black box thing shows up over and over again. And that black box thing is indeed the table variable because it has no statistics.

Right? There is no statistical information about… Oops.

Sorry. I got a little carried away with the highlight there. There is no statistical information about what is in our table variable. Right? That just doesn’t exist there. Now, that will exist is with the temp table.

Right? So keep in mind, the only thing I’m sticking inside my temp table is the value 22656. And if we run that same thing for that, well, what do we get back?

Well, we get back a bunch of junk that we probably don’t need that, you know, other stuff that came through there because I had a very expansive query to show all these things. But the thing that’s important is this first line where we have…

Well, let me squeeze that over a little bit. There we go. The thing that matters is this top line here where our pound sign temp table gets a single row histogram with 22656 in it. And so SQL Server can do accurate cardinality estimation.

SQL Server can figure out exactly how many rows are going to qualify in there. Right? Pretty good.

Pretty good stuff. Remember, SQL Server wasn’t able to do that for the table variable. So, let’s go back to this plan. Look what we got.

That was 27,901 of 12. Right? That one of one here, but no idea how to match that one to that one down there. Temp tables just didn’t have that problem.

So, just to reiterate, does not matter how many rows you’re going to put into your table variable. It matters how you’re going to use your table variable later. Table variables do not get statistical information about the data that populates them.

So, if you’re going to join those table variables off to other tables, where cardinality estimation and plan shape and a whole slew of other things might matter for performance, you probably shouldn’t be using them.

All right? Now, this kind of stuff might not matter at first. What I’m going to relate it to is, since I obviously just came back from the gem, right? If you’re the type of person who does not have a very challenging database size-wise, or does not have a very challenging workload, or does not have a very challenging performance criteria for their queries, you can almost use whatever you want and get away with it.

All right? But if you’re the type of person who wants to get better at this stuff, and who wants to someday maybe work on challenging databases and challenging workloads, these are the kind of small technique things that you’re going to have to get used to fixing.

Now, relating it to the gym, if you’re the kind of person who walks in, maybe puts a couple of 45-pound weights on a bar and does some squats, your form is almost never going to matter.

135 pounds is not going to be enough to destroy your life. But if you’re the type of person who wants to get a 400, 500-plus-pound squat, these little technique things are what’s going to make a big difference.

All right? Breathing techniques, where you place the bar, foot position, hand position, right? How you descend, how you rise, all these things.

All these technique things make a far bigger difference when you’re dealing with far bigger weights. In the same way, all these little technique things with T-SQL make a far bigger difference when you’re dealing with far bigger databases, data sets, far more challenging workloads, and all sorts of other stuff that make your job hard.

So please, stop telling people that the number of rows that you’re going to use is what makes a table variable okay or not. It’s all about how you’re going to use that table variable after it’s populated.

Okay? Because every time I hear someone say that, the smackin’ hand comes up. Just, you’ve got to fight it.

You’ve got to keep the smackin’ hand down. It’s like that movie Idle Hand, where it’s just like, all of a sudden, I have a knife. I don’t actually have a knife. It’s just the smackin’ hand.

The big smackin’ hand. Anyway. Thank you for watching. I promise tomorrow’s video will not be about table variables. I hope.

God, I pray it won’t be about table variables. I hope you enjoyed yourselves. I hope you finally learned that the number of rows you put into a table variable is not what makes using a table variable okay.

If you liked this video, please, you can do the thumbs up thing.

You can leave a nice comment. You can tell me how good I look after the gym. If you like this sort of SQL Server content, you can join the, let’s see, let’s get a fully updated number here.

All right. Here we go. The nearly 3,632 other people who have subscribed to this channel, so you can get a little ding every time I post one of these web gems for you.

All right. I’m going to go not think about table variables for a while. Might think about the bottom of a bottle of wine for a little bit. That’s about where I’m at.

Anyway, thank you for watching. Please stop telling people that the number of rows that you’re going to use is what makes using table variables okay. For the last damn time, it’s not okay.

Thank you.

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.

Erik Being Allergic To Slow SQL Server Queries

---

Thanks for watching!

Video Summary

In this video, I dive into the world of SQL Server performance troubleshooting by walking through a practical example using stored procedures and table variables. I explore how different compatibility levels can affect query plans and performance, demonstrating both the benefits and drawbacks of using table variables versus temporary tables. By experimenting with index creation and reordering queries, I show you how to optimize your code step-by-step to achieve better performance. This is a day in the life of what I do for a living—analyzing and improving SQL Server performance issues. Whether you’re just starting out or looking to refine your skills as a performance tuner, there are plenty of takeaways here that can help you tackle real-world challenges. So, if you enjoyed this video, don’t forget to give it a thumbs up and leave a comment with any questions or insights!

Full Transcript

Erik Darling here with Darling Data. I wasn’t actually sure that the record button worked on that. Things are a little touch and go for a minute. And I have finally gotten my awful nerd allergies subdued to the point where I think I can make it through an entire video without anything terrible happening. We’re going to see if that actually works. We’re going to see if that happens. If it doesn’t, this is going to happen. I don’t mind. I don’t get too attached to these things. I upload them. I delete them from my hard drive. They’re on YouTube. If anything ever happens to YouTube, it just wasn’t meant to be, I suppose. So in today’s video, we’re going to talk about all the different angles of performance troubleshooting stuff, right? Because that’s what I do for a living. I trouble shooting. I trouble shooting SQL Server performance issues. And, you know, I walk into a lot of situations where maybe not a lot of things were done in a great way. And it’s my job to unravel them. It’s my job to figure out what, what, you know, not every single thing that is unaligned to best practices needs to be fixed. But the ones that are causing the problems certainly do.

So let’s just pretend that we have a store procedure that we’re troubleshooting. We’re not actually going to run this as a store procedure. We’re just going to run this as some ad hoc code because it’s quite frankly just good enough. All right. So we have two queries here. We have one that inserts some rows into a table variable called way pops, which if you’re not if you’re not hipping with it, like old E. Darls over here, that’s what the kids say for way populars. Way pops. They don’t. It’s not. It’s not my kids saying go away, pops or random kids on the street thing.

Go away, pops. You’re weird and covered in tattoos and kids of our generation don’t care for tattoos. Way popular. And then what we do is we do some work with that table variable. In this case, I’ve chosen to cross apply to it with the select top one query as our sort of villain.

And, you know, you can you can call this sort of stuff unrealistic or unreasonable and you can say, I know all this stuff. But not everyone does. So and you notice down here that I have for the moment. I have the optimizer compatibility level hinted to 140.

Of course, because I’m using SQL Server Developer Edition because this Microsoft promises is not production data. You know, don’t audit me and start trying to charge me seven grand a core for this. It’s not going to be worth your time. I’ll fight you.

Because under Compat Level 150 in Developer Edition, which is equivalent to Enterprise Edition, table variables do get treated a little bit differently. We’re going to look at both ways. Don’t worry. So I’m going to take advantage of kind of a neat thing that SSMS added where I can execute the current batch.

Let’s shift alt and F5 for anyone playing along at home. And watch what this does. It’s going to turn the two queries under this booger green.

That’s not even like go Celtics green. That’s just like you’re sick green. And this is going to run for almost to say about 10 seconds.

And here’s what the query plans look like. Let’s bring that up a little bit so we can see both things in full detail. We have an insert into the table variable that all in all, all told, takes about 1.6 seconds. And then we have a query down here that takes about 8 seconds.

And we can see there’s not a lot of time spent in this part, 51 milliseconds. But this is where SQL Server chooses to say, Ah, this is where we need an index on the users table.

That’ll solve all our problems. All right, we can reduce this query’s impact by 58%. If only we had this index on the users table.

We can see looking at this thing that we spent, you know, 8 seconds in here. A lot of it’s scanning our table variable. That’s not a good time, is it?

Why would we spend 8 seconds doing that? SQL Server, come on now. Come on now, SQL Server. What’s wrong with you? So let’s just look at that real quick under compat level 150.

So under compat level 150, one thing is going to change. Exactly one thing. If I execute the current batch, it’s going to go a little bit faster.

Not like, you know, saves the day faster, but about twice as fast. So like 4 seconds instead of like 8 seconds. And this query in here did get better, right? Like this query is still about the same at 1.6 seconds.

But this query all of a sudden has signs that, well, perhaps this missing index request is not where we should be focusing our time. Perhaps an index is missing on our table variable because we have an index spool coming off our table variable. SQL Server has taken its just, you know, magnificent big brain and said, I’m going to take this temporary object and I’m going to create a temporary index in tempDB after I’ve scanned all the rows from our temporary object.

And then we’re going to use that index instead, which is not a terrible strategy because we’re down from 8 seconds to 2.5 seconds, right? So, you know, in this case, index spool kind of worked out in our favor. But that’s not good enough, right?

We still have this thing that runs for like, I don’t know, let’s see, 1.6 plus 2.3. I don’t know, let’s put 4 seconds. We don’t, it’s not good enough for us.

We are professional performance tuners. I don’t know who you think you’re messing with here. So the eager index pool is certainly a sign that we should have an index around somewhere. So let’s try indexing our table variable, right?

So we’re going to, since we can’t create an index after the fact, we’re going to create one in line. So let’s just say index C clustered user ID and then date. And then since we’re ordering by stuff by date descending down here, let’s give it a shot with date descending, the clustered index.

And we’re going to switch this back to 140 because, you know, not a lot of people I know are operating under 150. But we’re going to look at both because we are, if we are one thing here at Darling Data, we are fair. We are fair to everybody.

Mostly. Except scale our UDFs. Kind of unfair to them. So now let’s execute this and see what happens.

And, you know, that’s two seconds. So something got better, right? So down in this query, I mean, SQL Server is still like, we need an index on users, which is, you know, stupid at this point. 183 milliseconds.

And we spend about one second in here. But now this got a little bit worse. This got about 300 milliseconds slower. And the query plan for the insert changed, didn’t it? Right?

So let’s, what happened? We now have this sort in here. And this sort spills a little. We can tell by the little, little bangy thing here that this sort, this sort spilled off to disk. Maybe that’s why it’s slow.

Maybe it’s not. Maybe that’s just, you know, maybe that’s just how much time it took. The spill wasn’t too devastating, right? If we look at what happened in here. We spilled 3,900 pages.

That’s not so much. You know, I don’t think, I don’t think that’s what’s, I don’t think that’s what’s slowing us down tremendously. So I think we’re going to need to rethink how this insert is structured.

Right? Like, what are we doing in here? Right? Because now all of a sudden, so we have this index on the badges table.

Should probably, probably tell you about that. We have this index on the badges table. Right? Name, user ID, and date. And since we’re seeking to the name in the badges table here.

Right? Name and yada, yada, yada. Yeah. Well, that index should present everything in a reasonably orderly fashion. So let’s take a look at why we are now sorting things to get, put data into the clustered indexes.

Remember, indexes sort data, and it’s the kind of thing that, the kind of thing they do that makes other stuff fast. Remember, the query we have down here now with all our data nice and indexed and sorted is pretty good. Like, we still have a crappy estimate, but in this case, like, it’s, you know, such a small number of rows that we just don’t, we just don’t need to care too much about that one.

So, all right. How can we rewrite this insert in a way that will maybe be a little bit less painful? So, let’s take this, and let’s actually do this twice.

Actually, let’s see. What’s the best way to do this? I’m going to type in a demo and screw with all your heads. So now I just need to figure out how I want to do this.

So, let’s take this part. Let’s get rid of this. I’m going to need this right now. We’re going to say equals, and then we’re going to put that there. We’re going to say equals, and we’re going to put that there and say equals, and then we’re going to get crazy.

No, not that crazy. Union all. There we go.

Let’s put a couple union alls in between these things. And now let’s dandy up our where clauses. I’m going to put one there, and then we’re going to put one here, and we’re going to forget an end quote, and SQL prompt is going to put an extra one in, and then we’re going to take this one and put that there.

Now we can finally delete this, this empty shell of a query. All right. Let’s just see what happens.

Let’s see. Oh, no. We should do. There we go. Terminate your queries properly, everyone. So let’s do this.

And we’re still at like two seconds. And now instead of one sort, we have three sorts. There’s one.

There’s two. And this one in the middle still spills. So maybe our index definition is working against us. So let’s actually take the descending out of here.

Let’s see how far this gets us. So rerun this. And now, well, things got a little snappier.

All right. So the index order that we have for the badges table, right, we are now fully preserving that index order. All right.

We seek to the name in all three of these things. And because we seek separately to the name in all three, we now have the data in order by user ID and date descending. All right.

So 1.6 seconds. It’s still not great. But, you know, like this query is still going to be fast. You know, 180-something milliseconds. So let’s think about this a little bit differently.

All right. Let’s maybe think about our good old friend the temp table. All right. So let’s leave this where it is.

But I’m going to steal this part. And what I’m going to do is just down here, rather than declaring anything here, let’s create a table here. And let’s replace that with the pound or hash sign, whatever you want to call it.

And now let’s do an insert into that. Oh, I hit the wrong button. I don’t know.

Got all ahead of myself. Now let’s try that out. All right. So now we have two inserts, right? We’re just testing the inserts right now.

We’re going to test the insert into the table variable versus the insert into the temp table. And now let’s see how these two. Let’s drag race these two.

Oh, man. What did I do wrong? I forgot to drop table if exists. What an amateur. Drop table if exists.

Way pops. Go away, pops. All right. Let’s try that again. Error free this time.

It’s running. It did its thing. And let’s look at the difference between these two. So the first query, the insert into the table variable, takes about twice as long as the insert into the temp table.

Now, if you’re like me and this sort of thing interests you greatly, you might go on, you know, your SQL Server and you might fire up PerfView or Windows Performance Recorder and you might grab CPU stacks for when this stuff is happening. And you might look at flame graphs and all sorts of other things to figure out why one thing is so much different than the other. But the short answer is that the insert into an empty clustered index on a temp table gets all of like the fast loading minimal logging stuff that if you are a big fan of wine distributors from New Zealand, you may have read various blog posts about.

And that’s something that the table variable doesn’t get. It has never gotten. So in this case, something that I’ve talked about many times in the past is that table variables make queries ineligible for parallel execution plans.

That’s true all across all of everything, unless you play weird tricks on your table variable like inserting from like executing dynamic SQL or inserting from executing the store procedure, which I don’t recommend doing the second one because that has some weird locking stuff that happens. I recorded a video about that that you should probably watch and catch up on if you haven’t done that yet. But there’s no parallel plan difference here.

These are both single threaded plans. The only difference is that the data load into the temp table is far more optimized than the data load into the table variable. So let’s just get rid of the table variable.

We’re just going to quote this thing out now because we have once again determined that temp tables reign supreme when it comes to performance. Don’t worry. They don’t always.

Certainly times and places for table variables. It’s up to you to figure that out. I can’t tell you every time you should use something.

But so let’s see here. We’ve got our drop table. We’ve got our create table. We’ve got our insert. And then we need to fix this query a little bit, don’t we? We need to make that point to the temp table that we created.

And now let’s start this over a little bit. And let’s do our fancy trick here where we execute the current batch. And now we have everything rocking and rolling pretty okay, don’t we?

I think we did a pretty good job here. We got the initial insert query down from a couple seconds down to just under a second. And we got the final select down from eight seconds to just under 200 milliseconds.

And we didn’t need to create this index on the user’s table to get there, did we? So this is sort of a stock and standard approach to query tuning.

You fix little bits until you’ve got a whole thing that performs pretty well. All right. And this is what I do all day, every day for money.

Weird, right? Get a piece of code, make it go faster. It’s a darling date away.

So anyway, I hope you enjoyed yourselves. I hope you learned something. I’m moderately amazed. It’s actually sort of a miracle that I have not had one single allergic symptom during this entire, so far, 17 minutes.

I have been holding back a little bit because I’m afraid that if I do anything too sudden, I might just break into a sneezing fit. And you wouldn’t enjoy that. You wouldn’t like that at all.

So this is just a couple things that you can look at and do. Hopefully some lessons learned in here. For all you aspiring performance tuners out there on YouTube. So yeah, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, thumbs ups are appreciated as are nice comments. Boy, that Eric darling sure is spiffy.

Wish he was my dad. I would never tell him to go away pops. Something like that. If you like this sort of SQL Server content about performance tuning, which apparently some people do, you can join nearly 3,609 other satisfied customers of a free product and subscribe to the channel and get notified every single time I post something.

And normally when I post things, my voice doesn’t sound like scratchy box and I’m not awaiting some terrible allergic disaster befalling me in the middle of recording. So I promise the other videos are a little bit more lively.

So yeah, so we covered the like, the subscribe, the thanks. I think that’s about it. I do appreciate you spending your time choosing to learn from me.

And I will see you in the next video. All right. 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.

Performance Issues With Denormalized Data In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of database normalization and its impact on SQL Server performance. With a bit of a groggy start due to an early morning battle with allergies, I explore how a lack of proper normalization can lead to unexpected query performance issues. Specifically, we look at the Stack Overflow POST table, where questions and answers have different types of relationships and constraints that are challenging for the SQL Server optimizer to understand without explicit guidance. Through practical examples and queries, I demonstrate how these issues manifest and provide insights on optimizing queries in such scenarios.

Full Transcript

Erik Darling here with Darling Data, and boy do I have a big smile on my face today for no good reason. This Monday morning hit like an absolute ton of bricks. I woke up at 3.30 in the morning with every allergy in my head conspiring against me, and it took a very long time to get things calmed to a point where I could fall back to sleep. So if this video is a little weird or low energy, well, you have a pretty good reason why there.

My head is swimming with strange, my eyes have just all these weird floaty things, whatever those things are called. So in today’s video, we’re going to talk a little bit more about how a lack of database normalization can lead to some very weird performance issues. We’ve talked about it a little bit before in the Stack Overflow database.

Again, coming back to the POST table, and why the POST table is so important to this is because within the POST table, we have questions and answers and some other things, but only like certain things can apply to questions or answers, right? Like only answers can have a parent ID because the question is the parent of the answer.

And likewise, only answers in the POST table can be accepted as an answer to a question. Questions can get a different type of voting on them. They can get up votes and down votes just like answers can, but only answers have an additional attribute where they can be marked as the answer.

Get a little green check mark and 15 extra life points to use at the Stack Overflow company store to buy, I don’t know, windbreakers and kayaks and zippos and stuff. It’s like the Marlboro Miles of yore. So let’s look at voting in questions and answers because this is an election year, I hear.

So someone’s going to have to vote, unfortunately. And if we look at what we’ve got in the POST table, there are eight different kinds of POST that you can have in there. The ones that we care about are the ones that make up the majority of the table, which are questions and answers.

Now, like I said before, questions and answers can all be voted on in different ways, but only answers can get voted on with a vote type ID of one, right? So only anything with a POST type ID of two can get a vote type ID of one, right?

Ups and downs can apply to either one. So that’s fine, but it can make life a little weird when you’re querying data in there. Now, like we’re just running straight queries here. So I can show you, I can show you like an easy example of this. But imagine that you had a front end that called the store procedure and passed some parameters in.

You could end up in an even tougher spot than what I’m going to show you with parameter sniffing also involved in this situation. But even without parameter sniffing, you can end up with some weird query plan stuff. So this first query where we’re looking for, you know, anything, any POST type ID of one, which remember again, that’s a question, that has a vote type ID of two.

Right? So the top 2,500 POST ordered by creation date, this finishes nice and quick, right? We get a rather easy, simple looking query plan here. We seek into the votes table. We find some rows. We do a nested loops join to the POST table.

We find some rows and we return the top 2,500 ordered by creation date descending out to our fabulous, wonderful end users. Now, imagine a scenario where, again, if you had a store procedure and like, you know, you know your data, and you love your data, and you give your data big hugs and kisses, and ruffle its hair before it goes to school every morning, gets on the school bus, right, and lucky pencil behind its ear.

Like, you could like, put some safeguards in here. And even like, you know, if you were a reasonably gifted front end designer, you could put some safeguards in here, so that this didn’t happen to you.

But a lot of folks have no idea that, A, this can be a problem, B, that this is a problem that they should sort of guard themselves against. So now we’re going to look at where data doesn’t exist. So now we’re going to look for a vote type ID of 1, like there, and a POST type ID of 1.

And remember, that combination cannot exist across these two tables. Alright, so if we run this, this is going to be significantly slower than the previous query, because SQL Server is just looking and looking and looking for nothing, rows that can’t possibly exist.

They cannot possibly occur in that way, unless we had terribly wrong, erroneous data hanging out in our tables. I mean, God, I think we’re too smart for that, aren’t we? Right?

So if someone, you know, just like either entered the wrong thing, or, you know, like maybe just has no idea, again, most end users are not walking data dictionaries that understand all of these things. Like, like what the numbers are, and what they’re actually looking for.

Especially when it comes to, you know, the vote types, post types, I mean, it’s all weird. So, the query plan ends up looking like this, where we have to, our top spins up, and it starts asking for rows over here, and we have to keep going to the votes table.

We spend almost two full seconds going to the votes table repeatedly saying, okay, give me rows, give me rows, give me rows, give me rows, give me rows, give me rows, I need to look for stuff.

And then we spend almost, well, actually, this is a little over five and a half seconds, seeking into the post table, looking for rows and rows and rows and rows and rows, only to have nothing return. No rows met our criteria.

Right? And like the optimizer, I mean, you know, God bless it, it made some reasonable guesses about, like how many rows might exist and might come together, but it was wrong. Right?

There was zero of 7500. It’s crazy. Crazy. Silly little optimizer. How dare you? But like I said, this sort of, this sort of like domain logic is really difficult to express to the optimizer. Like, I guess you could create an index view, maybe, and use the index view instead.

But that might mess you up even still with all this stuff. But like, there’s no like cross table constraint that does a good job of this stuff. Because the only way to do that is to use a scalar UDF.

And if you put a scalar UDF into a check constraint, you’re an awful person. Your heart is just rotten garbage. It’s, get away from databases.

I’m going to send you the rock collecting book. Don’t do that. Scalar UDFs in computed columns and check constraints have this nasty habit of making, if not every, then nearly every query that touches the table ineligible for a parallel execution plan.

And that can have some really, really serious detrimental effects on the speed and efficiency of your workload. Alright? So don’t, don’t do that.

Don’t be awful in that way. Now, this is partially due to the fact that we are asking for a top. We have requested a top. We, the same thing would happen if we used offset and fetch, because under the covers offset and fetch is just, just a little topperoo.

Uh, but this is all because using top and, you know, a number of other things internally sets a little role goal, for the optimizer. And the optimizer, uh, sort of uses that role, row goal to hedge its bets against how hard it’s going to be to find this limited number of rows.

Remember, like a row goal means I don’t need to find all the matching rows. I just need to find the top X number of matching rows. Right?

Which, if we run this query with the optimizer row goal disabled, this comes back very quickly finding no data. But we choose a much different query plan. Right?

I mean, granted, it doesn’t run for like eight seconds, which is good, but it’s a much, much different query plan. Right? We still have some nice little index seeks here and here, and the whole thing takes around 470 milliseconds. And that’s not bad, but that row goal internally just kind of made things get, get wonky weird.

Right? All right. SQL Server chose a silly little navigational nested loopy easy. Well, you know, just, uh, I think this is going to be super easy to find this data.

All this data exists. I’m just going to find it real quick type bet. But that, that query plan was not a very good bet for the table. Now, uh, I, I’ve, I’ve, I’ve demonstrated this little trick before, but you know, it might be worth, you know, going through again.

If you’re, you know, the type of person who’s not allowed to use hints in queries, like disable, optimize, or row goal, you could do something like this, where you nest some tops, and you get a very similar execution plan. Right?

So this one again, about 400, 500 milliseconds. Same bigger query plan, parallel, lots of seeking and hashing and stuff. But, uh, it finds the lack of matching rows quicker. And if we were to, uh, use the original sort of argument of vote type ID two down here for post type ID one, we would still be able to find all the rows that we cared about relatively quickly.

Doing that. So, uh, this one goes back to using sort of the original plan with just the really easy nested loopy deal in here. But, you know, uh, either way we get, we get the results very quickly.

And the same thing would happen, uh, with the query above with this thing, uh, the disable optimizer row goal hint applied to it. This would also finish rather quickly because SQL Server is able to find this data very fast based on the indexes that we have. So, if there’s a message in here, it’s largely that well normalized data causes fewer problems.

Um, uh, in situations where I see any sort of, like, parent-child relationship or hierarchical relationship, uh, all designed into a single table, uh, the, the, the, the, like, the actual, like, stuff that you know can only happen for a parent or a child or a question or an answer, or the top of the food chain versus the middle and bottom of the food chain, uh, gets very difficult to, uh, get into a table. Get across to the optimizer as far as just like, you know, what data lives and can, is eligible for stuff in other tables, right? Because that’s what kind of happened here is SQL Server just doesn’t know that only these type of, uh, only answers can be accepted as the answer, uh, to a question.

Questions can’t do that. And the vote type ID of one and the post type ID of one, the vote type ID of two and the post type ID of one means absolutely nothing to the optimizer. It’s just making guesses based on what data lives in there.

So, um, if you need help with that sort of thing, I am, I am a SQL Server consultant. I can, I can fix these sorts of issues, apparently, that people pay me for, usually. Uh, they do not pay me for YouTube videos.

These are, these are free for you to, uh, you know, help you understand what’s going on with your SQL Server a little bit better. Uh, in that, uh, in that vein, I did, I do, I do hope you learned something. I do hope you enjoyed yourselves.

I do hope that my, my energy level was acceptable for, uh, the limited amount of highly damaged sleep that I got last night. Uh, and I hope that my, my, my, my, my wording and thoughts were clear to you. So, we have all that stuff going for us.

Uh, if you like this video, nice comments, especially about my new haircut, uh, are appreciated. Uh, if you like this sort of SQL Server content, well, golly and gosh, you can join. Look, let me get the, let me get the most up-to-date number here.

You can join nearly 3,600. We are at 3,599 subscribers to this channel who are blessed with magnificent, angelic notifications from YouTube. Every single time I post one of these free little gems for you.

Ain’t that nice? Anyway, uh, I’m going to go back to my natural state of being slumped in a chair. Um, maybe I’m going to drink some more coffee.

That might be a good idea. Espresso? I’m sorry. Not, not just coffee. Espresso. I go, I, I’m, I go for the hard stuff. All right? I’m a, I’m a hard caffeine user when it comes to that. So, uh, I’m going to go do that now.

And probably, I don’t know, do all sorts of other dorky stuff like take antihistamines and use nasal sprays. Maybe, maybe I’ll do a neti pot. But I’m, I’m not going to film me doing a neti pot because, uh, I know, I know, I know how many, um, sort of sensory issues exist in my, my audience.

So there’ll be no live neti potting. Don’t worry. Anyway, um, 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.

The Unpredictability Of SQL Server Performance

---

Thanks for watching!

Video Summary

In this video, I delve into the unpredictable nature of SQL Server performance issues, sharing a real-life client emergency that occurred on a Saturday afternoon. This experience highlights how slight changes in data or workload can significantly impact query plans and execution, making it challenging to maintain consistent performance. I illustrate these points with an example where two nearly identical queries exhibit vastly different performance due to subtle differences in literal values and statistics sampling. By discussing the nuances of index usage, modification queries, and locking strategies, I aim to provide insights into why SQL Server can behave unpredictably and how understanding these factors can help in troubleshooting and optimizing your database environment.

Full Transcript

Erik Darling here with Darling Data, and in today’s video I want to talk a little bit about the unpredictableness of SQL Server performance issues. And this came up because I actually dealt with a bit of a client emergency a little bit earlier today, and if you’re looking at your calendar, if you’re looking at the published date of this video, you’ll see that it is indeed Saturday at 3.10pm, which means that it’s a little bit more than a client. It means Erik Darling did some work on a weekend, which is a rarity, but you know, nice people pay me money and I try to be responsive. So there’s a couple different angles to this, and a lot of it has to do with making sure that your environment is sort of set up and maintained so that SQL Server has, is future-served, chances to screw-up, and chances to screw-up as possible. SQL Server is a big, gigantic, complicated piece of software, and slight changes here and there can have weird effects.

And even if you’re not making changes, your data most likely is changing. And as data changes, well, SQL Server makes adjustments. Things like auto-update stats, compile new query plans, and all sorts of other sort of things that, you know, seem like a good, you know, dynamic, like adjustment to whatever is happening with your data and your workload, but can really kind of make things hard to keep stable. And, you know, not to plug away too much at, you know, your humble YouTuber here, but this is the kind of thing that I do like helping people with. So let’s, let’s just take a look at these two queries right here. There is a one minute difference in one of these literal values. And if we run both of these queries, and we look at the query plans, one of them is going to do a clustered index scan, which takes 175, well, 177 milliseconds. And the other one’s going to do an index seek with a key lookup that takes 18 milliseconds.

Right? That’s a one minute difference there. And there’s a one row difference. And one SQL Server decided that it was going to tip that point. The one that does the clustered index scan estimated 13,496 rows. And the one that does a key lookup estimated, well, that was an improperly aligned blob, but the one that does a key lookup asked for 13,000, estimated 13,495 rows. Right? And I know you’re looking at this, and you see, you see this green text here. And this green text is asking for an index that would fully cover the query. Right? So it wants to include display name, display name is not in the index up here that I created. So asking for display name is really the thing that tips SQL Server over. If we had that green text index, SQL Server wouldn’t really have much of a choice. Like that key lookup just wouldn’t be an option.

Or rather, it would, I mean, I guess it would be an option if there were another index. But SQL Server would narrow down its choices to that one index that has everything in it a lot easier. Right? So it would just say, well, there’s no lookup to be done. So this is clearly the cheapest way. There’s not going to be that costing choice. So that’s kind of like one part of it. And the other part of it is that when modification queries are on, modification queries need love and indexes too.

They need to have good ways to find data. And one thing that’s terribly frustrating, before we move on, one thing that’s terribly frustrating is like I’ve, I’ve, I’ve, I’ve gone over this demo a few times and every time that I have dropped and redone this index, that the time and date that I need to put in here to get that one row plan switch over has been different.

So like different stats, stats, samplings and stuff can really make a big difference here. Like if I, if I rerun all this stuff and I rerun these two queries, there is a very good chance that either they’ll both get this, that they’ll both get the same plan and we won’t see that tipping point difference like we did before. And we, we don’t with that, with that, with that index, right? Where we resampled statistics, look what happened.

Now for both of them, we get a clustered index scan. And now these numbers are still have a one row difference, but they weren’t quite low enough for a SQL Server to, to, to go for the key lookup plan. Right? Neither one of them met whatever internal tipping point threshold SQL Server had for itself.

And that, that’s terribly frustrating. Stabilizing stuff like this is hard. Right? Figuring out that this was the problem is hard. Figuring out how to solve the problem is hard. Right? Because there’s not all, it’s not always as obvious as an index when a plan completely and drastically changes.

This is just a simple example. There are, you know, the queries that I deal with are much bigger, much more complex. And there are much bigger changes that happen just because of random set standpoint. But for instance, like a query that used to have a fairly, that used to have a cost that made it eligible for a parallel plan.

Something happened. And all of a sudden SQL Server is like, well, this, this, I think this thing’s going to cost like two query bucks. And you can’t just go drop cost threshold for parallelism down to one so that this query plan, this new query plan is like parallel all of a sudden. That would drastically change the entire workload.

Another thing that gets weird is with locking. So with just this index in place, if I run this and I do this update, SQL Server on the first run chooses to lock the entire object. Right? SQL Server said, nope, don’t want to manage a bunch of little locks.

That’s not fun. We’re going to lock the whole object. Right? And if I run this again, SQL Server chooses a different strategy. I ran the same update twice in a row and now SQL Server said, oh, I changed my mind.

Now we’re just going to take a bunch of exclusive key locks. Right? And then if I, if I come down, if I come down here a little bit and I’m going to lose a little bit of how the sausage was made going out there. But if I, if I come and rebuild the, that users table and I run this again, we are back to locking the whole object.

Right? And then if I run that update again, we’re back to using the key lock. So it’s like just weird stuff happens constantly all the time. Weird, like weird little things change.

Weird little bids and bobs just line up differently. And plans change and locking changes and all sorts of other things just kind of get really weird. And you can just have oddball problems.

Of course, if we add in a good index for this modification to query, this modification query is, we don’t even necessarily need creation date in here. That’s just sort of an artifact of when I copied and pasted this index down lower. Then we’ll get pretty consistent results from this where we’ll always take the key locks.

Right? Because SQL Server will have a good index that it can seek to, to find the, the, the reputation rows that it wants to update. So if you’re out there and you’re, you’re having trouble with these sorts of tiny little things, having profound cascading effects on SQL Server performance, if this is really gumming up your workload, well, I’m, I’m, I’m here for you.

This is the, this is the kind of stuff that I like doing. You know, it’s like a, like, like a coach likes telling you what’s wrong with your squat form. I like what’s telling, I like telling you what’s wrong with your SQL form.

So if you, if you do need this kind of help with stuff, um, you know, I’m, I’m here. I’m ready to work. I’ve got my, got my hammers and shovels, uh, a couple of flamethrowers, you know, some grenades.

We got some, we got some stuff that, uh, we got some, we got some good hardware. So, uh, anyway, uh, I’m going to go not work on a Saturday now. And, uh, we’re going to, yeah, I think, I think, I do believe it’s martini time.

It’s close enough. So, anyway, uh, thank you for watching. I hope you learned something.

I hope that, I hope that you, I genuinely hope that you don’t have these kind of SQL Server problems. But if you do, uh, young and handsome consultants are standing by. And, um, yeah, uh, if you like this video, uh, appropriately placed thumbs are, are appreciated.

Uh, as are nice comments, especially if you like my new haircut. I got one. My head’s not big and fuzzy anymore.

My head’s nice and sleek and streamlined and sort of aerodynamic. I look like I’m, I look like I’m ready to, like, be a, be a general or something. I could lead an army with this haircut.

Uh, and if you like this kind of SQL Server content, or if you just need, if you, if you see this kind of SQL Server content, and you’re like, wow, I, I, I, I, I identify with that. Well, um, I guess you, you could subscribe to the channel, which is free, but, um, that, that, that might not solve all your problems unless, unless you, unless you have a lot of time on your hands.

So, anyway, uh, thank you for watching. And, and please, please do go enjoy, uh, your Saturday. Hopefully you didn’t have any weird emergencies today.

And, um, I, I do hope that if, if you are the type of person who, who partakes in, uh, alcoholic beverages, that, uh, your martinis are cold and stiff. Way, way God intended. All right.

Uh, 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.

Why Some Indexes Create Slower Than Others In SQL Server

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

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

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

A Little About Fill Factor And Fragmentation In SQL Server

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

A Little About Multi-Column Indexes In SQL Server

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

A Very Silly Performance Tuning Trick In SQL Server

---

Thanks for watching!

Video Summary

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

---

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.

Fun and No Profit

---

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

• Improve The Performance Of Multiple Date Range Predicates
• XML Query keeps converting < to &lt; and > to &gt;

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

• Comparing an aggregate function’s result against a column
• Do I need to run add UNCOMMITTED hints to IndexOptimize?
• Constraint design for maintaining a unique “most recent’ row
• When should I use Query Store over the plan cache?
• Query performance when updating 50 million rows
• Trouble with the EVENTDATA function
• Efficient queue table design and querying
• Plan cache problems with multi-tenant database design
• Optimizing Recursive Common Table Expressions
• How to figure out how many tables cascading deletes touch
• Low selectivity indexes
• What happened to my backups?
• When should I look at SQL Server’s error log?
• The ten millionth question about deleting duplicate/system statistics
• Why is sp_who2 so hard to understand?
• Does indexing a table variable make using one okay?
• Just how stupid is it to set min server memory equal to max server memory?
• Why oh why doesn’t Query Store tell me about tempdb spills?
• Partitioning and Primary Keys
• Advice for switching to using Read Committed Snapshot Isolation
• What do “Parallelism” waits in Query Store translate to?
• How can I tell if the Version Store is causing issues?
• The 10 millions question about default ordering of query results (there isn’t one)
• Help my dynamic SQL is broken
• Another question about queue table design and querying
• Why is SPID -5 and why is it causing blocking?
• Slow In The Application, Fast In SSMS 😭

Thanks for reading!

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.