Tag: how to create temp table in sql

A Foreign Key Join Elimination Limitation In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into a peculiar limitation involving foreign keys and join elimination in SQL Server, which can be quite frustrating when it occurs. I share my thoughts on why foreign keys are generally useful for ensuring referential integrity but caution against using cascading foreign keys due to the underlying serializable isolation level that can slow down operations. The video also touches on how SQL Server behaves differently depending on whether you’re checking if data exists or non-existent data, leading to unnecessary joins even when a foreign key should guarantee otherwise. If you found this topic interesting or have any questions, feel free to like, comment, and subscribe for more content.

Full Transcript

Erik Darling here with Darling Data. You know, it’s cool when you and your logo are both handsome. See, I see a lot of consulting companies out there where neither the logo nor the consultant are handsome. So really getting the total package from me is worth every single penny. In this video, we’re going to be talking about a really annoying, I guess, maybe the plural, maybe that S shouldn’t be there. It’s a sort of an annoying limitation with foreign keys and joint elimination. Now, the first thing I want to say about foreign key joint elimination is that it happens under such limited circumstances anyway that you should stop trying to meme people into using foreign keys by saying it’s a thing. Like, it’s… it happens so rarely and so rarely in useful circumstances. I don’t understand the point of even bringing it up. It’s almost like when people talk about other facilities in SQL Server with this, like, like, like, meme white knighting of what makes… why they’re okay when they’re… they kind of suck.

Things like, you know, like, you know, like, you know, like, you know, CTE being more readable, table variables being only in memory, things like that. Things like that. Things like that. Okay, sure. Yeah, you read a LinkedIn post where someone said that and now you’re just gonna ride and die with that. Okay. All right. Fine. Fine. Foreign key joint elimination is right up there with that. Now, foreign keys and SQL Server do work as long as they’re not, you know, disabled.

And as long as they’ve been re-enabled correctly, they will totally, you know, make sure that you have referential integrity between two tables. They will make sure that rows exist or, you know, are present in one table that have to be present in another table. It’s fine. They do that. They also, you know, you know, if you’re… I mean, God, don’t put them in a data warehouse. One thing I gotta say up front is data warehouses are not the place for these things.

Your OLTP data is the place for these things. And your data warehouse should just follow whatever, you know, ETL processes are necessary to take, to, you know, honor whatever uniqueness or referential integrity or other constraints exist in your relational data, your OLTP data. Your data warehouse should just be a big, beautiful, massive columnstore indexes where you don’t. Mess with things that slow down data loads.

All right. You put those things in your data warehouse, you’re asking for things to be slow. Take care of them somewhere else. Do not, do not put these things in your data warehouse. It’s stupid. Stupid, stupid, stupid, stupid.

Anyway, if you like this channel and you would like to support this channel with money, you can do that. I have very, very low cost member sponsorship options. I have a hair on my neck somewhere. It feels very strange. If you are unable to participate in the money thing, other things that make my heart go pitter patter are likes and comments and subscribes.

You can hear it from here. That might be arrhythmia. I don’t know. It’s hard to tell. It’s a reason I take a lot of blood pressure medication. All right. If you need SQL Server consulting help, these are the things that I usually help my clients with.

If you need something else, we can negotiate. But if you need any of this stuff, I can pretty much do this right out of the box for you. If you need some SQL Server performance tuning training, I have a very low cost option where you can get about 24 hours of it for just about $150 US if you use that discount code over there.

I’m not sure where my finger, ah, there goes the finger. But there’s a link to get that coupon code applied directly for you down in the video description. I will be out in the world. I will be out in the world speaking to real live people on two dates in the near future.

Friday, September 6th, I will be at Data Saturday Dallas doing a full day pre-con. I also have a couple of regular sessions on September 7th. Then November 4th and 5th, I will be at Past Data Summit doing a big old double team high five with Kendra Little on two days of pre-cons there.

So all great things to show up to and all great events to support with your very presence, your mere presence. You can support me and countless other speakers who show up and organizers who put these things together. And it’s a nice thing to do for the data community because you are part of the data community, aren’t you?

You’re one of us. You’ve been bored in, right? So let’s get on and talk about something that really irks me with far and keys in SQL Server. And I don’t know why this thing gets so tiny when I close out.

It doesn’t look that small normally, so it’s a little strange. Alright, so let’s start by setting things up. Alright, we are going to drop some tables if they exist, and they did up until a minute ago.

They were there. They were live and present. And then we are going to create two tables. One called, I forget why I named it this.

Maybe I was just mad at Connecticut that day. Because, you know, that kind of speaks for itself. And then both of these tables have clustered primary keys on this column called ID, obviously.

And they have two date columns that we are not going to really do much with. And then, I think I already did this. I think this was part of the initial highlight. Yep, I already did that. Good for me.

SQL Server is working correctly. It said that foreign key already exists. So we’ve created a foreign key on CT that references the ID column. Or, sorry, on the ID column in the CT table that references the ID column in the CT underscore FK table.

Alright, so we have that there. And if we do a little background check on our foreign key, we will see that it is present in the database. It is not disabled.

And it is not not trusted. It’s the old double negative here. Whatever Microsoft person decided to phrase it this way, I wish that I could spend some time with you where there are no video cameras and no other recording devices. Because why the hell would you do this?

Why would you just not call it is trusted? Why is it is not not trusted? Now we have to say is it not not trusted or is it is trust trusted? You know?

Is you is or is you ain’t? We don’t know. But that’s a zero. So it is trusted or not not trusted. It’s great news for us. Now, this is where things get funny for me.

If we run this query and we say, hey, does stuff in the CT table exist in the FK table? Is it there? Is you is or is you ain’t?

Well, in this query plan, you’ll notice that we only have to touch one table. Right? Even though we say, hey, there’s stuff in here. SQL Server says, we know it’s there.

We got you. It’s fine. Don’t worry. Don’t sweat it. We’re good. We’re fine. We don’t need to go check over there. It’s obviously there.

It’s a foreign key. Foreign key is enabled and not not not on is is untrust untrust. It’s very trustworthy foreign key. Okay?

Let’s leave it at that. It’s very trustworthy. But now when we say, hey, SQL Server, is there anything here that that’s not not in that table? SQL Server, SQL Server loses some of its confidence.

It loses some of its moxie. Moxie, someone someone knocked the cool right out of SQL Server’s walk because now we end up having to check both tables to figure out if stuff’s there or not. SQL Server all of a sudden does not trust.

Lozen does not trust that foreign key so much anymore, does it? We no longer eliminate that join. And that’s a very strange thing to happen because just a second ago when we asked if stuff was there, SQL Server was very sure of itself. Cock sure, you might even say.

And now now we say, is anything not there? Well, who can tell? We have to go check both tables to figure it out. So this is one of those dumb things.

And this actually sort of aligns with a video that I did. Well, at this point it may have been published two or three days as of when you see this. But it almost kind of goes back to the video I did about what annoys me about computed columns and filtered indexes where if you don’t ask the exact right question in the exact right order, it’s all of a sudden SQL Server’s like, I don’t know you.

I can’t use that filtered index. I can’t use that computed column. Can’t do that. Nope. Nope.

You didn’t do things right. Can’t use it. Which is pretty ridiculous, right? And so if you haven’t seen that video, you should go watch that video to see another way in which SQL Server is utterly, patently ridiculous. All right.

So this is a short one because, you know, I don’t want to spend too much time picking on foreign keys. They are useful for ensuring referential integrity. I hate saying those words.

I guess if I had, if I had to, you know, one more word of wisdom slash caution with them is, if you’re, if you’re the type of person who gets these grand ideas in their head about using cascading foreign keys, just be very careful because cascading foreign keys behind the scenes use a serializable isolation level to make sure that everything maintains that referential integrity. So when you update or delete and that cascading foreign, or that foreign key action cascades out, you’re using the, behind the scenes SQL Server is like, nope, serializable.

Which is another great reason to make sure that your foreign keys are well supported by indexes because you don’t want that going on for a very long time. That can be quite a misery. So anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope you find this troublesome and annoying the way I do. Uh, if you remember all the stuff that I said about the liking, the subscribing, training, consulting, great.

If not, you can rewind and get, get reacquainted with all that, that, that vast expanse of knowledge. Anyway, um, I think that’s about it for this one. I have, I have some other videos that I’m going to record.

Uh, you can, you might, you might be able to guess some of the topics by looking at some of the tab names up here. Some of the tab names are none of your business. Don’t look at those.

Some of them will be in, be in upcoming videos. So it’ll be, it’ll be a surprise to you, but not to me. It might, some of the ones that don’t show up might be, might be a disappointment to you, but. All in good time, my friends, all in good time.

Anyway, uh, thank you for watching. Goodbye.

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.

When To Use DBCC DROPCLEANBUFFERS When You’re Tuning SQL Server Queries

---

Thanks for watching!

Video Summary

In this video, I delve into how to determine if a query has been effectively tuned, addressing common questions from clients and colleagues alike. I discuss various initial steps like using recompile hints or experimenting with cardinality estimators before diving deeper into the query logic. The video also explores the use of `DROP CLEANBUFFERS` as a tool for demonstrating server performance issues, particularly when queries exhibit inconsistent speeds due to varying data availability in memory. By running this command and comparing query execution times, I illustrate how hardware limitations can significantly impact query performance, even with well-optimized indexes. This practical demonstration helps highlight the importance of balancing index design with sufficient server resources to ensure reliable query execution.

Full Transcript

Erik Darling here with Darling Data. And in this video, we’re going to talk about how to tell if you’ve tuned a query. Now, this is a question that I get a lot when I’m working with clients, the nice people who pay me to, you know, be able to afford this nice equipment to make these nice videos for you and to not run commercials during my videos and stuff like that. Because, you know, like when I’m tuning a query, there’s a lot of different stuff I’ll try to do. Some of it might be some upfront stuff just to see where things are at. Like, I might throw a recompile hint on there. I might mess with the cardinality estimation model. I might, you know, either force the new cardinality estimator or more commonly force the legacy cardinality estimator to see if I can improve things. There are a lot of different things that you want to try before you start, like, really digging in and restructuring stuff, unless there are some very obvious structural issues with that. The query. Obvious structural issues would fall under the domain of things like joins that have OR clauses in them, CTE with, like, multiple references throughout the query, where you might want to, like, you know, fix the OR clause in the join to do, like, you know, out or apply union, union all type thing.

You might want to dump CTE into a temp table. You know, you might throw a recompile hint on there if you want to test the efficacy of, you know, maybe you have a local variable in the query. Maybe there’s a table variable in the query.

You know, and, you know, maybe using a table variable, you know, you might want to dump that into a temp table and go from there. So, like, there are some obvious, like, some obvious, like, tweaks that don’t involve really messing with the query logic all that much. There’s some domain knowledge stuff that, you know, some of the developers that I work with have about the nature of the data that can make, like, a query, that can make a query tuning exercise a lot easier.

You know, sometimes a developer will come along and say, we actually don’t even need to run that, or this query doesn’t make any sense. Like, I can’t imagine why we’d be doing this. Other times they’ll say, oh, we don’t actually need to join to this other table, which is slowing things down.

We have that column in this other smaller table. It’ll be much easier to go get it from here. You know, there’s some domain knowledge stuff that’s good to have, you know, when I’m working with clients because they can point these things out.

In this video, we’re going to talk a little bit about a question that I get a lot, where it is, you know, someone will say, oh, you know, I thought I made the query faster, but every time I run drop clean buffers, it’s slow again. Okay, well, that’s fair, because when you drop clean buffers, which hopefully you’re not doing in production, you do clear out all the data that’s currently in the buffer pool, which is really only a fair test if you are doing something like, if you’re on a server that has, you know, queries are showing lots of high physical reads, when you look at their metrics from the plan cache or inquiry store, and that’s backed up by a very, very high page IO latch underscore SH or EX weights, where that shows that queries are constantly going out to disk to bring data into memory before they can run.

So, a little bit of housekeeping, as the professionals say. If you like my channel and you want to support me in a very minor way, there are very low-cost memberships that you can sign up for. If this is beyond your means, if the dollar sign is not something that you can contribute, likes, comments, subscribes are equally as profoundly gratifying to me, because I like the engagement, to be honest with you.

I like knowing that every time I put a video out there into the world, lots of people are going to be able to benefit from it. So, if you are in the market for SQL Server Consulting, these are the kind of things that I typically help clients with. If you need something else, let me know what it is. We can figure that out, too.

If you need training that is affordable, that lasts a lifetime, you can go to that link up there. There’s also a link down in the video description where you can purchase my training with the 75% off code. It brings all that stuff down to just about $150 USD, which is also a plenty fine way of buying me a bottle of wine.

Well, you can also do that. I do have some live events coming up in, oh boy, just about two weeks. I will be at Data Saturday Dallas, Friday, September 6th, doing my full-day pre-con, and also at the main event on Saturday, September 7th.

And then November 4th and 5th, I will be co-presenting two days of pre-cons with Kendra Little on November 4th and 5th. And that’s, of course, in Seattle. There are no online versions of these things, so if you want to go, you’ve got to show up.

One is in Dallas, one is in Seattle. If those are helpful locales for you, well, I look forward to seeing you. So now let’s get on with the show here.

Let’s talk about when I might use drop-clean buffers to show people the kind of state that their server is in. Now, for the other stuff that I talked about, recompile, cardinality estimation, temp tables versus table variables, I’ve covered a lot of that stuff in a lot of other videos.

What I haven’t really talked about is how you can use drop-clean buffers to show people how their server, like what’s hurting queries on their server. So sometimes when you do this, one thing that you have to keep in mind is that you might not have good indexes to support your queries.

We’ll talk about that. Let’s say you have a query that’s usually fast, but sometimes it’s slow. So you might blame parameter sniffing, so one thing that you might try is a recompile hint, which is fine. If you try the recompile hint, and sometimes it’s fast and sometimes it’s slow, you probably don’t have a parameter sniffing issue.

What you might have is just sort of a more pathological server configuration issue. One of the places you might look is, like if you look in the query plan and you see, oh, I have good indexes for everything, nothing is particularly painful with anything that I’m doing.

It’s just sometimes seeking into this index takes like five or ten seconds, and sometimes seeking into this index takes a few milliseconds, and you might ask why. Well, this is a pretty good example of why.

What I’m going to do is I’m going to run drop-clean buffers, and then I’m going to run the same count query twice. And so we’ve cleaned the buffers, we’ve scrubbed them clean, we’ve gotten a little soft toothbrush and some toothpicks and q-tips and stuff.

We’ve really gotten the crevices, we’ve detailed those buffers, and they are so clean now. They are spotless, they are sparkling, they are just magnificent buffers.

And if you look at the query plans for these two things, we have, you know, two very different query timing things here. This one runs for about 6.3 seconds, and this one runs for about 631 milliseconds.

That’s a big difference, right? Like if you multiply the 631 milliseconds by some number with a few zeros in it, you will get to about 6.3 seconds like we have up in the first query.

If you have people complaining that queries are sometimes slow, one thing that, you know, some things that you want to dig into on the server are, okay, well, you know, if you have a specific query to look at, you can look at that query and you can look at the resource usage of that query in QueryStore, which SPQuickieStore, MyStore procedure, makes very easy for you.

Or you can look at the plan cache, and you can check out the physical reads for that server. And if you notice that sometimes it’s doing a lot of physical reads, like it might have a high, like, max physical reads and a low min physical reads, that’s a pretty good sign that this query runs sometimes, and all the data that it needs is already in the buffer pool where it’s helpful.

And sometimes it’s not in the buffer pool where it’s not helpful, and you have to go to disk to get it. When you have to go to disk to get it, sometimes this query is going to take 6.3 seconds.

When you don’t have to go to disk to get it, sometimes that query is going to take 630 milliseconds. So this is, drop clean buffers is a pretty good way to say, okay, if I already have good indexes in place, and sometimes this thing is fast and sometimes this thing is slow, maybe we just don’t have enough memory on this server to accommodate the workload we’re asking it to run.

Right? That can totally happen. Depending on the size of the table and index that you’re dealing with, even reading a big nonclustered index can be pretty painful.

So always keep an eye on that. And for the sake of this argument, let’s just say that we quite obviously don’t have a good index.

Both of the queries that ran above, regardless of whether data was in memory or not, both of those queries were saying, hey, an index, please.

S’il vous plaît, as they say in bonjour land. So we can create an index. And when we create an index, we make it easier for a SQL Server to read the amount of data, a smaller amount of data, right?

The post table is a pretty big, gnarly table with like a lot of string data in it, including a big and bar car max column. And even just like reading through all that stuff can be pretty slow when you’re reading straight from disk.

With drop clean buffers in place, what we see is a much different situation. Even when we have no data in memory, right? We drop clean buffers, we select a count, and we do this, we’re able to seek to just the data that we care about.

If this index were set up a little bit different, let’s say that there were another column in the key of the index before score, so that we had to scan the whole index.

The one that reads from disk would take a little bit longer. When we can seek to write to what we care about, we sort of take away some of the pain of having to go to disk for anything. Not only do we have to read far less from disk into memory.

Remember, when you do a seek into an index, you only have to go and get those relevant pages. It’s when you have to scan a whole index. And I don’t mean like a scan with a top or an offset fetch in it where you can stop reading pages after a certain point.

I mean a true index scan where you actually have to scan through the entire thing. Even reading a big enough nonclustered index can be pretty slow. I could create another index and put the body column from the post table in the includes and slow it down even more because I would be inflating that index size.

So obviously, when you’re tuning queries, make sure that you have a reasonable index to find your data with. If you don’t have that, if that is for some reason eluding you, always go with creating the indexes first.

If you already have good supporting indexes for your query and the query is still slow, sometimes, sometimes, fast, sometimes, then one thing that you want to keep an eye on for the workload as a whole are weight stats.

You want to always be looking at the page.io latch underscore something something weights. If those are more than like, you know, 10, 15, 20% of your server’s uptime, that means that most of it, like that percentage of time that queries are running, they’re going out to disk.

So like, let’s say that page.io latch weights are 25% of your server’s uptime. That means that 25% of the time, your queries are a lot slower than usual, right?

Just doing some brute force math there. I’m not that good at like statistical probability stuff, but if page.io latch weights are 25% of your server’s uptime, one out of every four times your query runs, it could be slow.

The other three times that it runs when data’s already in memory, it could be fast. Pretty wild to think about. Another brute force way to think about this is that every time you double memory, you will cut page.io latch weights in half.

So if you have 64 gigs of memory and you go up to 128 gigs of memory, you might see those page.io latch weights go from 25% of your server’s uptime to 12.5% of your server’s uptime.

Yeah, I did it math. On the spot, on the fly. 12 plus 12 is 24 plus 0.5, 225.

I did it. I did it just for you. So these are things to consider. When you’re dealing with, depending on the server situation that you’re dealing with, these are the kind of things that you might have to prove out to either your boss or if you’re a consultant like me that you might have to prove out to clients to say, hey, if you want this to be reliably fast, well, we’ve already got the good indexes in place.

What we need now is hardware that can actually accommodate the workload that it’s running on. You can even run those exact same numbers by them. You can say, hey, page.io latch weights are 30% of your server’s uptime.

We double the memory, it’ll go down to 15%. We’ll have queries that are, instead of being, you know, slow once every three times, they’ll be slow once every some other number of times.

That math I completely skipped out on. So sorry about that. Anyway, these are the kind of stuff, these are the kind of things that you might have to deal with either if you’re a full-time employee or a consultant or, you know, you’re just some schlub that got pulled in off the street and someone said, hey, you look like you can tune a query.

What’s your name? Come look at this. You might be able to find all of these things. You might be able to present reasonable options for them. The use of drop clean buffers, specifically, can help illustrate what happens to a query that, like, you know, one out of every, however many executions, the data isn’t in memory.

And that can be a really powerful thing to show people to get them to buy into either, you know, changing the indexes so that we have a better index to support the query or adding memory to a server so that we have a less variable workload where some, like, we increase the odds that the data that we care about is already in the buffer pool and we don’t have to go to disk for stuff.

So, that is when I use drop clean buffers during a query tuning exercise is to either say, look, we can add an index and we can have it be pretty reliably fast or if we already have good indexes and we still have that variability, say, this is what happens when our data isn’t in memory, this is what happens when it is in memory because going back to that first example and I’m just going to backtrack a little bit here, you know, and, you know, these numbers are pretty small because I am on fantastic hardware.

Fantastic hardware. But let’s say that, you know, you’re on less fantastic hardware or let’s say that you have, there are way more tables involved and these things are way more out of control.

You know, just showing someone that, you know, a query is this slow when the data isn’t in memory, which happens once out of every however many executions depending on what those page IO latch weights are and this is how fast the query is when everything’s already in memory can be a really good tool for showing someone that, you know, either, again, we don’t have good indexes in place or we don’t have a good amount of memory in place.

So you don’t need to use drop clean buffers all the time. Drop clean buffers, recompile, cardinality estimation hints, these are all very situational tools that you want to use to fix very specific problems or to see if, to see if you can, see if, like, adding that in addresses whatever the problem with the query is, right?

There are all sorts of things that, you know, recompile can help with that might say, okay, well, you know, we can recompile this query all the time or we can turn it into dynamic SQL or a substore procedure or something like that.

So there’s all sorts of very situational query tuning tools that you can use for this stuff. Drop clean buffers is one of them, but it’s not going to, it’s not going to make your, it’s not going to prove that you tuned a query by using it necessarily.

It might actually just show that, you know, what is wrong with the query sometimes, right? So keep this stuff in mind. You know, I think, you know, it’s really important, it is important to show that a query is faster sort of regardless of where data lives, but it’s also important to show that the data might not always be where you want it to be and you might have to, you just might need more memory or you might need to make better use of the memory you have with better indexes, better index compression, stuff like that.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will like and subscribe and hire me. I hope that, I don’t know, I just, I want the best for us both as a wise Canadian lady once said.

Or, that wasn’t quite it. She was being a little bit meaner about that, wasn’t she? Yeah, she was mad about something about a movie theater. I forget the details.

I haven’t heard that song in a very long time. Anyway, I’m going to go, oh crap, I got work to do so I’m going to do that. Anyway, 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.

Annoyances With Filtered Indexes And Computed Columns In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into some of my pet peeves with filtered indexes and computed columns in SQL Server. Specifically, I highlight how the query optimizer’s inability to use inverse logic when working with these features can lead to suboptimal performance, especially on larger tables. For instance, creating a filtered index where `isDeleted = 0` works fine, but trying to use `isDeleted != 1` instead triggers an optimizer error, despite the data being present in the index. Similarly, using computed columns for calculations like `upvotes + downvotes > 10,000` can result in full table scans if you mix up the column order, even though indexing the computed column could theoretically speed things up. These issues underscore the importance of careful query design and planning to ensure that your indexes and computed columns are used effectively.

Full Transcript

Erik Darling here with Darling Data. Getting younger and better looking by the day, I’m told. In this video, we’re going to talk about some of my annoyances with filtered indexes and computed columns. Granted, there are more than this, but this is just one that even a very casual observer may find quite annoying. And mostly, it has to do with the expression matching abilities of SQL Server’s query execution engine. Mostly in that it’s very hard for it to, well, actually, not hard. It is not currently implemented in SQL Server’s query optimizer to be able to use inverse logic and still match expressions to things. It’s really annoying. So we’re going to talk about that. As usual, if you would like to support this channel, there are very low-cost memberships. I think the entry-level one is like $4 a month. And that’s a great way to keep from commercials making their way into the Darling Data ecosystem. If for some reason you’re unable to partake in a $4 a month or more subscription, then great ways to show your support are likes and comments and subscribes because those numbers going up make me just as happy as I’m going to be able to share.

So I think that’s my monthly earnings. My monthly earnings going up, which right now are at $30 and 70-something cents. So, you know, I think the bare minimum for a deposit from YouTube is $100. So every four or so months, I get $100 from this channel. So the other numbers going up is equally as gratifying. We’re not talking millions here. So if you need help with SQL Server from a consulting point of view, from a man with a slightly itchy eye, this is the kind of stuff that I normally do. Health checks, performance analysis, hands-on tuning, responding to SQL Server emergencies, and developer training.

If you need something else, give me a holler. We can talk about something else. But this is the stuff that I typically help clients with. If you need low-cost SQL Server training, you can get all 24 hours of mine for about $150 with the discount code SpringCleaning. Of course, if you just click on the link in the video description, you will get a direct link that applies that coupon code for you. So you can do that.

Some upcoming events. Friday, September 6th, I will be at Data Saturday Dallas doing a full-day pre-con. And November 4th and 5th, of course, I will be at Past Data Summit splitting. Well, I mean, like, I have, like, two pre-cons each day split with Kendra Little.

So November 4th and November 5th, me and Kendra will be double-tag-teaming some pre-con material for all of you lovely folks out there who can show up in Seattle. Now let’s get on with my annoyances with filtered indexes and computed columns. We’re going to start over in my crap database.

And we’re going to create a table called isDeleted. It’s just kind of a transient junk table that I’m going to use to show you my first annoyance with these things. I’m not creating it in the Stack Overflow database because I always forget to get rid of these things.

And then I have a bunch of tables that I don’t want or need in the Stack Overflow database. And we’re just going to stick some data in there using the sys.messages built-in DMV table where SQL Server holds all the error messages that SQL Server has in them. If you’re ever exploring a new or different version of SQL Server, looking for new error messages is a really fun thing to do.

You find all sorts of new things. You also find lots of typos and grammatical errors and incomplete error messages and really unhelpful error messages. The quality of Microsoft everything has slowly been like on this decline.

Some of it more like this. Other of it kind of like a step pattern. But the quality of error messages has really been eating it over the last few years.

Some of the error messages that you get, you’re just like, I can’t possibly do anything with that. How do I fix this? So now we’re going to create an index on this table that obeys all of the rules that filtered indexes need to obey in order for us to be able to use them efficiently.

And mostly that is that we’re creating this filtered index on the column DT. We’re including the isDeleted column so that it is part of the index definition. And we have our where clause to where isDeleted equals zero here because no one needs to see things that are deleted.

So if I were to say zero equals isDeleted like in this one, we would do just fine. I’ll show you all this stuff in a second. But if you use the sort of inverse logic and you say not one equals isDeleted or you say isDeleted not equal to one or anything like this, SQL Server will just say, oh, that’s nice, but I’m just going to scan the clustered index instead of using your filtered index instead.

Now, granted, for a table with 300,000 rows in it, it’s not going to be a big performance hit. This is just to show you the behavior that I’m annoyed with. You can imagine where this would be annoying to you in real life on much bigger tables, particularly where isDeleted equals zero is a fairly sizable portion of the table data.

Or like I talked about in the considerations for soft deletes video, maybe where you’ve set up some sort of view or something in order to get back the data you care about. So we even go a step further here, and if we hint the index on isDeleted, the ISD index on isDeleted, and we tell SQL Server, please use this index. It is a perfectly feasible, usable index to satisfy this where clause.

SQL Server will throw an error, an optimizer error. The query processor, I’m on Zoom at Ketchup. The query processor cannot produce a query plan.

Wow. Wow. It’s just, nah. It freaks out that hard about things. It’s kind of ridiculous that this is still something that people have to deal with here in the year 2024. Of course, if we just say, hey, pretty please, let’s just write this the correct way, then SQL Server will seek into our nonclustered index and find those not deleted records.

So that’s my first annoyance. The optimizer is just not geared to use the inverse logic to still be able to use a filtered index, even though exactly what you’re asking for is in that filtered index. It’s a bit column, right?

There’s no two ways about it. Another thing that I find quite annoying is in relation to computed columns. Now, computed columns have nearly the same problem.

In this case, let’s say that we wanted to look at who has a lot of upvotes in the Stack Overflow database. This is just a short query to show you that there are actually people who up and downvote things a lot, including whoever ID negative one is. That seems like a really powerful user.

And, of course, you know what? This create index statement, I’m going to start running that, but then I’m going to move it up here because it should really be up here because this is the important thing. But if we look at the query plan for this, now I’ve talked about this before where it’s sort of like a sargability thing, right?

Search argumentability. This is one of those patterns that ruins search argumentability for queries, right? If you concatenate two columns, in this case, we’re adding two integer values together.

Even with a perfect index in place on upvotes and downvotes, SQL Server doesn’t track that particular correlation, right? It knows the values in upvotes and they’re in order and it knows the values in downvotes and for, you know, for duplicate values in upvotes, the downvotes are ordered in there. But it’s not tracking what upvotes plus downvotes equals, right?

So there’s no way we can seek to that data. The best that we can do is scan through that data, add upvotes and downvotes together, and then see who has more than 1,000 of them. This should actually be 10,000, I think.

Let’s make this not 10. That’s not a zero. That’s an O. If we run this, we’ll get a slightly lower number. But it’ll still be the same deal. So we scan the entire index that we created on upvotes and downvotes.

We had to read all 2.4 million columns. Sorry, all 2.4 million rows, add those columns together, and then figure out if the result of that matched our, was, you know, was correct for our predicate, right? So this plus this is greater than 10,000, right?

Best index in the world still doesn’t beat that. Now, what you can do is you can create a computed column, and you can, even without persisting it, right? Notice there is no persisted in here.

Even without persisting it, you can index that computed column, right? Another thing that slips by a lot of people is that even if you don’t persist a computed column, you can still get statistics generated on it, which is nice, right? Because, you know, persisting computed columns on big tables can be kind of painful.

But if you go and create an index on that column, then you will, thankfully, write that out. There will no longer be a virtual column in this index. It’s still a virtual column in the clustered index or the table if you don’t have a clustered index.

But in this index, all those values are written down. They are permanente in there. So, if we run this and we search for upvotes plus downvotes is greater than 10,000, we will get a nice index seek into our nonclustered index. It will be quick and fun and painless.

But if we mix up the column order in that and we say downvotes plus upvotes is greater than 10,000, SQL Server can no longer figure out just what the heck to do with itself. We are back to scanning the clustered index, reading all 2.4 some odd million rows, and figuring out how to add those columns together and what they equal.

So, when you’re using computed columns and filtered indexes, do try to write them in a way. And I just want to add, if we tried to hint that nonclustered index up there, we would get the same query processor that we saw with the filtered index example that came first.

So, when you’re making computed columns, you’re making filtered indexes, make sure that whatever queries you want to use them are the basis for those computed columns and filtered indexes. If you have a bunch of different sort of query forms that sometimes look for things on slightly different arrangements, SQL Server will, A, not be able to match them directly to the computed column or filtered index, but, B, you’ll get an optimizer error if you try to tell it to.

So, those are the two things to be very careful about with filtered indexes and computed columns, is making sure that they adhere to the queries as they’re written, or how you’re going to write the queries in the future, or how you’re planning on rewriting the queries to match these things so that they actually work together.

So, those are a couple of things that I find annoying. Actually, it’s kind of one thing that I find annoying about both of them, isn’t it? Same problem, twice.

Same problem, one problem, two different facilities in SQL Server. Two different arrangements where things are not good. So, thank you for watching. Remember, all this stuff, like, subscribe, comment, hire me, all that good stuff.

I have training, and I’m going to be doing live things. If you’ve forgotten any of the details there, you can rewind the video and watch the little housekeeping section at the beginning where I talk about all that stuff.

For now, I’m going to go… Well, apparently, I have a call starting in a couple minutes, so I’m going to go do that, and then I have to record 7 million more videos to make sure that things stay flowing in a nice, even pace.

So, thank you for watching. I’m trying to go pay the bills, as they say. All right. Goodbye.

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.

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

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.

Holiday In Suburbia

---

You may be reading this and thinking “what does a consultant know about labor?”

Well, I wasn’t always this idle in my profession. I’ve had a variety of jobs in my life that have hardened the hands and stiffened the back. Now I mostly harden my liver with stiff drinks.

Mostly I’m celebrating that I have soft hands again.

Remember to moisturize, friends.

If you’ve got the day off, make it a good one. See you tomorrow.

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.

Multiple DISTINCT Aggregate Performance In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into a common performance challenge in SQL Server queries: multiple distinct aggregates and their impact on row mode execution. I explore why these operations can significantly slow down your queries, especially when dealing with large datasets. We then dive into how batch mode can mitigate these issues, showcasing the differences between achieving batch mode through temporary objects or compatibility levels, and how this can drastically improve performance. Additionally, I discuss the limitations faced by Standard Edition users, suggesting alternative strategies such as using temp tables to bypass some of the inefficiencies associated with spools. By the end of the video, you’ll understand not only why these issues arise but also practical steps to optimize your queries for better performance.

Full Transcript

Erik Darling here with Darling Data. According to the weather outside, I am recording this video during the end times. It’s wild out there. Whew! Hurricane Debbie, what a gal. Anyway, sorry if that clap was loud. We should clap lower. Maybe that’s less painful for you. I’m never going to hear this, so deal with it. Alright, so let’s talk about what we’re going to talk about today. In this video, we’re going to cover how multiple distinct aggregates are tough for performance in SQL Server queries, but mostly only in row mode. We’re also going to cover how batch mode can help, but also how Microsoft has no respect for standard edition users. So we’re going to cover all the fun stuff today. As usual, this channel is free. I don’t have commercials. I don’t have any paid sponsors.

MeUndies has not beaten down my door, mostly because I don’t wear underwear. So if you want to sign up for a membership to show your love and appreciation for the channel, you can do that. There are some cheap ones. I’m going to work on better stuff for the higher price ones when I have some time to think. That doesn’t happen a lot. But when it does, boy, the gems come out.

If you’re unable to monetarily support the channel, that’s totally cool. There are free ways to show me that you care. You can like, you can subscribe, you can leave comments, all sorts of fun stuff. So if you can’t do one, think about doing the others, because I do enjoy the feedback. I like the engagement. I like knowing that the things that I write and record reach as many people as possible. That’s what makes me feel good. If you’re in need of a SQL Server consultant, you’re having some performance problem or other thing, and you need a trusted, reliable set of eyes and hands, and I’m not going to put feet on your SQL Server. That’s weird. That cost extra.

You can hire me to do that. My rates are reasonable. If you’re looking for affordable SQL Server training at the beginner, intermediate, or advanced levels, you can go to training.erikdarling.com, and you can get 75% off the whole kit and caboodle with the discount code SPRINGCLEANING.

There’s also a link for that in the old show notes. So you can click on that, and it might be a bit easier for you than remembering all those letters up there. URLs. Wow. What a thing. As far as when you can see me live and in person on this slide with a tremendous amount of white space that I have not figured out what to meaningfully do with yet, perhaps I just need a graphic floating over my head or something.

You can catch me Friday, December 6th, doing a full-day pre-con at Data Saturday Dallas. You can also catch me November 4th and 5th at Past Data Summit in Seattle, where I’ll be all week doing Seattle things. I don’t know. There’s a presidential election that week. Who knows if Seattle will survive that? So I don’t know. Whatever. Only time will tell. Let’s get on with the demos for today’s topic of interest. So when you’re working on reporting queries, there are a lot of times when you’ll need to use distinct, lest we end up counting many useless things. It’s not just for counting. It could be for summing or averaging or basically anything else that you can put in an aggregate, min, max, I don’t know, stdev, standard deviations. There are so many things you could do. So many things. But you got to do it. The problem is that when you do it in row mode, it’s very slow. The more you distinct, the more slower you get.

So let’s look at the query plan for this, which has one, two, three, four, five distinct aggregates applied to it. Now, the slow parts of the plan are fairly obvious once you start looking. The stuff that happens over here with the joins, not that big a deal. Where things get wonky, and we talked about this a little bit in the video that I did on top n% performance queries, where as soon as you need to start streaming into a, you know, because, you know, parallel spools, ha ha ha. As soon as you need to start streaming rows from multiple threads into a spool in tempdb, your life is going to be in for a awful, just a awful. Your life is going to be a awful.

A awful. This is not fun. Notice we go from 22 seconds here to a minute and 12 seconds here. That is no bueno. There are zero buenos about this. On the bueno scale, we are at absolute zero. Right? There have been no buenos. Things after that generally don’t change much. Right? Through all this, we’ve added on like another three seconds in this branch from, go from 112. Well, look, 115, and then it’s 115 all across. But then we hit three minutes and two seconds.

Por qué las embarguesas? Why? How did that happen? Well, that happened for, I mean, a few different reasons. You know, notice that we have all of these sort of headless spools. All of these spools are actually fueled by this spool, right? This is, this is, this, this is the spool that fuels the spools.

Ah, that made my mouth feel nasty. Yeah. Spool fuel. Hmm. Spoodum to fuel your spools. Spool your, fuel your spool. Uh, so each one of these spools, uh, has a whole bunch of rows in it. And each one of these spools will go out into an aggregate and, you know, we’ll, we’ll do some work in order to make whatever we’re aggregating distinct. This happens a whole bunch of times, but where this causes a real, a real kerfuffle in the query plan is specifically in this branch where we have a hash aggregate that spools for a very long time. This, my friends is again, this is negative on the Bueno scale. We have gone negative Buenos. Right? There was a, a, a, a Bueno deficit for this particular branch of the query plan. So this, this, this entire thing ends up taking again about three minutes. Now what’s funny is that this isn’t, that you can avoid a lot of these problems with batch mode. And depending on where you are in the SQL Server world, as far as version and addition and, you know, uh, compatibility level and stuff, um, you know, you, you can deal with this in different ways.

The big deal with batch mode is that you get rid of all those gigantic spools. Uh, spools in a SQL Server query plan are often a sign that you have done something drastically, tremendously wrong that needs correcting. Spools are there for your benefit, but they only benefit bad mistakes.

They don’t tend to benefit good choices. They tend to benefit bad choices, right? So they’re, they’re like a little insurance policy. They’re a little, uh, safety rail for, for your queries when you, when you do something terrible in them. So the two ways of approaching batch mode are of course, uh, to, uh, either create a, some sort of object. It doesn’t have to be a temporary object. If you’re using a SQL Server 2019 in memory, temp TB feature, uh, you can’t create a, a temporary object with a columnstore index on it.

So, you know, you can, you’re, but you’re perfectly able to create a stationary permanent object in your database, uh, that has a columnstore index on it with no rows, or you can add a non-clustered filtered columnstore index to a table that has like filters still to something that where nothing, no rows can possibly exist. And when you, when you do this, um, you will get a sort of bastardized version of batch mode and it’s bastardized because it is not fully implemented. It is not as tightly ingrained into the optimizer is when you use a higher compatibility level to get batch mode on row store. I have to be very specific about this, that, uh, fooling SQL Server into using batch mode some of the time is not the same as getting batch mode on rowstore, the intelligent query processing feature.

Uh, the intelligent query processing feature does a whole lot more, uh, is able to do affect a whole lot more operator operations in your query plan than just tricking SQL Server into using batch mode for some operators. All right. So we’re going to run these two and these will not take three minutes to run thankfully. Um, but while these execute, I do, I do want to say, I mean, that’s about seven seconds there. So, uh, well, these are, these execute. I do want to say that part of what makes spools rough is that they have like, when you load data into a temp table, uh, you know, there are all sorts of optimizations that temp table loading has gotten in SQL Server over the years. Like there were some big ones in 2014 and, you know, I’m sure some other ones in newer versions and things have gotten better over time, you know, as they sometimes do. Uh, but part of the problem is loading data into the spool. It’s a single row at a time. It’s very, very slow. You have none of the temp table optimizations behind spools that you have for temp tables. So, you know, like part of what makes that query, aside from that monstrous hash spill up, part of what makes that, those, the multiple distinct query up, they’re really slow. It’s just loading that big spool, right? Loading millions of rows into a spool is always going to be painful because of how little love and attention spools have gotten over the years. So if we look at the query plans for these two, they’re not that different. Now, this is good for us because, you know, if you’re not in a position where you can use a higher compatibility level, even for a single query, like I did for this one, uh, but you can sort of finagle with the query a little bit, either, you know, it’s a tricky SQL Server and using batch mode, you can get much, much better performance. We’re down to four and a half seconds from three minutes. And notice we don’t have any big crazy spools in this thing, right? We’re not spooling in billions of rows. We’re not hash spilling. Nothing awful is happening here. The second query where I do get batch mode on row store, that finishes about 1.3 seconds faster, right? Which is not a not a not a not meaningful improvement, right? The difference is, of course, uh, in reading from the votes table. Uh, so the votes table up here, we take 3.1 seconds to read from that. And down here, we take 1.9 seconds to read from that. The difference, of course, is that in the query where we trick SQL Server into using batch mode for some operators, we still read from the votes table using row mode, right? Because when, again, when we play these tricks on the optimizer, we don’t get the fully ingrained batch mode on rowstore experience. We get like a, like a, like, I don’t know, like, we get like the half naked show, not the fully naked show. So we, well, we do get batch mode for this hash join and for this hash aggregate. We do not get batch mode anywhere else. In neither, in neither of these queries do we read from the post table using batch mode, uh, largely because the post, oh wait, we do. Weird. That didn’t happen before. Hey, that’s funny. We got batch mode on row store here too. Usually I don’t because of the body column in the post table. Uh, maybe I’m just lucky on this one. I don’t know. So, uh, cool. But, uh, the reason why I thought I still wasn’t getting it is because there’s not a really big difference in how long it took to read from there. So, uh, I’m not going to start this video over again. We’re just going to say, cool, it can happen sometimes. Usually I don’t get batch mode on the post table though. Usually the big and VARCAR max columns in there, uh, completely to the SQL servers like, nah, nah, we’re not doing it. So, uh, yeah. So we do save a little bit of time here. Now let’s say you are, uh, on, uh, Enterprise Edition. Either one of these things will be fine for you.

These are great. These are great options if you are on Enterprise Edition and you want this query to go faster. If you are on Standard Edition, if you are cursed, if you are doomed to some bottomless existence, uh, some pit that you will never arise from, uh, using Standard Edition, one thing that you can do is you can replace the spool with a temp table by loading data into the temp table, which has all of the optimizations that you would expect. Uh, just, you know, not, not a spool, right? Uh, you, you might not want to load huge temp tables up, but hey, everyone has their own fetish. If you’re on Standard Edition, you just might fit somewhere in the S or the M world of things. Maybe, maybe even both, right? I don’t know. Uh, so we’re going to try this query out in two different ways. Now, the reason why I bring up Standard Edition here is because in Standard Edition, batch mode stuff, columnstore stuff, uh, is limited to at best a DOP of two. So if you, if you are, if you find yourself in a situation where, um, maybe you get okay performance from a DOP to, uh, batch mode plan in Standard Edition, cool. If not, you’re going to have to do something with yourself that gets you, uh, a, a higher DOP plan. Um, you know, DOP 8 is a pretty good DOP. I’ve had pretty good luck with DOP 8 over the years. Uh, um, you know, my, I am a, I am a DOP 8 proponent in most circumstances. Um, so one thing you can do is you can drop, um, all of the, the things that you want to deal with into a temp table.

Now this loading up the data into this temp table will be a lot faster than loading it into the spool. Remember the spool took like, I don’t know, 30 seconds or something. Loading it into the temp table takes about 7.3 seconds. Is that, is that super fast? No, but it’s better than like 30 seconds.

And then once we work off, once we’re working off that, things will get a bit better for us. So, uh, well, you know, these, I probably should, I probably should have started these running while I was, while I was off talking about Standard Edition and all that goofy stuff. But, uh, if you notice, uh, in this, this query plan, we no longer have all of the terrible awfulness that we had in the other query plan, right? This is the first one, which, which is, which is in row mode again, but we don’t have all of the spooling, right? Because we’re just working off, uh, this temporary object for all of them. And even though we get a spill in the same place for, uh, for this, this leg of the branch, uh, we get, it’s far less of a severe spill. This is not spilled for one minute.

And so this ends up running for about six seconds rather than about three minutes. Now, working off the, uh, temp table, uh, over here, this runs for about a second, right? So pretty good. That’s, and that’s the batch mode version. Cause this is the, in this version, I bumped, uh, the compat level up to 160, 150 would have been sufficient here on developer edition to get batch mode on rowstore. But even like the non batch mode version, working off the temporary object is a lot faster, uh, both because you take the place of the, the very inefficient spool loading in the, in the initial query. And because, uh, you don’t have, you don’t reuse that spool over and over again. And you know, the estimates in this one are better because we’ve stabilized the result set. We are no longer as prone to estimation errors because we have a stable temp table that we’re working off of not like, Oh, I don’t know what this join is going to do. Let’s all, let’s all have a weird day. Uh, so things turn out a bit better in this one.

So, uh, to recap multiple distincts in that and row mode queries, uh, can have a really nasty, we have really nasty performance side effects. Uh, I think probably the best way of dealing with them on enterprise edition is to get batch mode involved in some manner. Um, I think, you know, batch mode on rowstore, uh, does have more optimizer support for more operators than tricking SQL Server into using batch mode, but sometimes just the batch mode trick is enough. Uh, if you’re not on enterprise edition, uh, I would highly suggest the use of temp tables, uh, because when you use temp tables, uh, you take the nasty spool, the nasty, inefficient spool out of the equation and, and you can use DOP up to whatever DOP you have set for either the server database query, wherever you choose to set DOP, wherever your DOP fetish setting lies, you can, you can, you can set DOP there. Um, and, uh, you, you know, not saying it’s not worth testing the, uh, batch mode stuff on standard edition, just in my experience, the DOP two limitation really does hurt performance, uh, uh, you know, in, in many, many situations. Um, you’re, you’d be very lucky if DOP two batch mode were equivalent to like DOP eight row mode depends on some stuff worth testing, but prepare yourself for disappointment. Of course, if you’re using standard edition, you are already well positioned to deal with disappointment. So you have that going for you. Um, there, there’s always a silver lining in the, in the, in the darling data playbook. So, uh, I think that’s good for this. Um, uh, I hope you enjoyed yourselves. I hope you, hope you learned something. Uh, I hope you paid attention to me babbling about liking and subscribing at the beginning of the video, because I’m not going to do it again at the end, because let’s face it, this, this isn’t where, where any, anyone else, uh, anyone, anyone pays attention. That’s this, it’s over by now. You’ve drifted away. You’ve gone to sleep.

You’ve, you’ve hit fast forward. You’ve, you’ve, you’ve, you’ve absorbed all of the usefulness of this. And now you are just, we just had this useless husk of time left to, to, uh, to, to, to, to close things out. So, uh, I’m going to go stand in the rain for a little bit, take myself a nature shower. All right, cool. 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.

“SARGable” Sorting and Grouping In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the concept of sargability in SQL Server, expanding on its traditional usage in join and where clauses to include sorting and grouping. I explain how wrapping columns or functions around expressions can disrupt index usage, leading to suboptimal query performance even when the differences might seem negligible at first glance. By sharing practical examples and execution plans, I illustrate the importance of maintaining sargability not just for search operations but also for overall data integrity within indexes. Throughout the video, I emphasize the broader implications of non-sargable predicates on code quality and database performance, encouraging viewers to adopt cleaner coding practices that can prevent future performance issues.

Full Transcript

Erik Darling here with Darling Data and continuing with my new channel setup where I’m going to do my begging and pleading at the beginning of videos where people are still paying attention. Of course, you fully have the option to skip over this part. That’s up to you. It’s a cruel world. What can you do? This video we’re going to talk…we’re going to use the term Sargable a little more loosely. …than some people might be comfortable with. Sargability typically deals with you writing expressions in a way that allow SQL Server to effectively use indexes for them. Whether it’s a join or a where clause. But we’re going to talk about the Sargability even though there’s not technically a search argument involved in the context of sorting and grouping because it can do stuff there. So, this is a This is my YouTube channel. All of this content is free. I don’t have any problems except Intel timing out looking for drivers constantly. God, I hate that thing. And I’d like to keep it free. So, in order to help me do that, you can subscribe to the channel. There are very low-cost memberships available where you can donate a buck or two if you’re feeling generous.

But if you can’t be bothered with a buck or two, then please. I like likes. I like comments. I like subscribers. I like growing this channel so that someday when I do have to spring commercials on everyone, it can be to the largest possible audience. I’m also a SQL Server performance tuning consultant. I can do all of these things in order to make your life with SQL Server better and easier. And you can hate SQL Server less because I’ll take that on for you. I will be your SQL Server sin eater. If you need SQL Server training at a low cost, you can get 75% off mine with the coupon code SPRINGCLEANING. Just go to training.erikdarling.com, plug that code in, and off you go. Stream away, my friends. Stream away.

As far as where you can catch me live and in the flesh, Friday, September 6th, I have a full-day pre-con at Data Saturday Dallas. And November 4th and 5th, I have full-day pre-cons both days at Pass Data Summit in Seattle. And I also have regular sessions attached to those events. So, if you’re going there, stop by, give me a hug, come to my pre-cons.

It’s a nice thing to do. All right. Cool. Everything went black. Most people are used to dealing with the term sargability in the context of join and where clauses. So, if you do something like this, or if you do something like this, most right-minded people will call you a damn fool.

When you wrap columns and functions like this, you ruin SQL Server’s ability to interpret indexes in an orderly manner and find and join your data in a quick and orderly manner. It is generally not great for performance when you do this.

It’s not going to hurt you every single time. But it is something that you’re going to want to avoid because someday it will hurt. Or someday someone will copy what you did in your code and it will hurt.

And they’ll think you’re an idiot for doing these things. So, generally, this is sort of like a sanitary code thing for me. You know, avoiding this sort of stuff generally tends to help your code bases in general be cleaner.

I’ve said it on the channel before in other videos. But when you write code, code is culture. So, if your company culture has good, clean code in it that doesn’t have a bunch of things like non-sargable predicates, then you will tend to have a better, more quickly functioning code base than if you allow this sort of crap to go through.

Because this is ideally not what you want to see in your queries. This can also apply to other parts of the query, though. It’s not just joins and where clauses.

So, just to give a quick example, none of these queries are going to be bafflingly slow or awful or ugly. But you will see differences. I created an index on the comments table just on the score column.

Because it’s a good column to create an index on. Especially for the demos I’m about to run. Because, um…

Would you believe that I wrote this specifically to show you these problems? I don’t know if you would believe that. People think I’m a lot more slapdash than I am.

Maybe I am. So, let’s run these two queries. And we’re just going to get the top one score from the comments table. And we actually sort of got lucky.

Because, you know, the score column is not unique in any regard. But we did not have a tie for the highest scoring comment. How a comment got a score of 1270 is a real remarkable thing to me.

Because this is the comment that has a high score of 1270. Did you really stumble upon that exact situation in a real life scenario? Or was this question only meant to be a puzzler just for the fun of it?

Space? Question mark. Apparently that was profound to almost 1300 people. So, I wonder…

I don’t know how many… I don’t know what that comment’s current upvote situation is. I might go to the Stack Overflow Data Explorer after I record this and see. Because I’m a little fascinated now.

So, both of these queries return the same row. Which is, in our case, lucky. Because when you have non-unique columns that you’re ordering by, you often need a tiebreaker column to give you a reliable result that is a unique column.

You would need to order by, like, the score column and then, like, ID descending or something. Because ID is an identity column and you generally won’t see too many dupes in identity columns. But coming to the execution plans, look what happens here.

And the one where we have a score unencumbered by is null, this whole thing runs in zero seconds with a serial execution plan. Right?

Even though we have to scan this index, that’s okay. Because all we have to do is grab one row out of the index. We do a lookup to get whatever else we want. But this lookup is for one row. And would you believe a one row lookup is not the end of the world?

It’s not a tremendously big penalty on a one row lookup unless you’re using columnstore. So, this is pretty quick. In the second query, where we wrapped the score column in an is null, right?

So, if there were any nulls in this, we replaced it with zero. We have a parallel execution plan. This thing runs at DOP 8.

It takes almost a second to run. And SQL Server finds it necessary to sort the data. Notice we don’t have any sorts in this query plan up here, where we didn’t wrap score in is null, because the index presents score to us in an orderly manner, right?

Indexes put data in order. This is one of the things they do that makes queries go faster. We have ordered data.

It’s good for searching. It’s good for already having data prepared in the order that we need to present it in. So, all good there. But as soon as we wrap score in is null, SQL Server says, oh, no, no.

This is no longer in order. We’ve got to sort this, right? And you can see that this sort is on an expression. Expression 1, 0, 0, 1 descending. That expression maps directly to is null score 0 over there.

So, this goes parallel, requires almost a full second to run, takes about a, oh, I guess that’s one meg memory grant. Not the end of the world, but, you know, just needing one at all for this, it’s pretty ridiculous because we have an index that has this in order.

But we ruin that index’s ability, or rather we ruin SQL Server’s ability to use that index effectively by doing this. This can also happen with grouping, right?

And, you know, this is probably less of a performance suck than I think the ordering problem is. But I want you to pay attention to a couple of things in these query plans because they’re interesting, right?

There’s not a vast difference in timing between the two, right? And these both generate parallel execution plans. This one takes about 500 milliseconds. This one takes about 700 milliseconds.

So, there’s only about a 200 millisecond difference. But in the plan where we wrap the score column in is null, and we group by that, SQL Server has to add extra operators in, and we have to do extra work in order to do that.

For this small query right here that we’re doing, it’s not that big a deal. For bigger queries that process way more rows, or even bigger queries where, you know, you might join stuff off together, and there might be all sorts of other crazy things going on in the query, this stuff has sort of like a snowball effect on things.

And, you know, I think it’s important to understand, again, coming back to like the code is culture thing, don’t make, if you write cleaner code, you’ll have fewer problems in general.

Like paying attention to the basics and the fundamentals of things is really important because then you have, like your code is more, your code runs more reliably fast. You don’t have as many weird bugs and other things to worry about. And you don’t have to worry about these pile-on effects where, I don’t know, let’s say that you stuck this group by query in a CTE, and then you were joining it off to something else.

SQL Server might make all sorts of bad choices because it had to make extra steps, do things less efficiently, maybe make worse guesses as far as cardinality goes. All sorts of pile-on effects can happen as soon as you start allowing these little unwanted in your code base.

Now, for this one, notice something kind of interesting here. SQL Server starts with a stream aggregate, right?

And this basically reads the data in order from here. So rather than having to, you know, sort data or do anything else, we have the data that we care about in order here.

We do need to sort after this one because the repartition streams, I don’t believe was helping us maintain data in order. That’s okay. But really the important thing here is that we just don’t have any unnecessary additional steps in this plan in order to get the results that we need.

In this one, we have an extra compute scalar to generate the isNull on the score column for grouping. And then we have this partial aggregate where we partially aggregate data.

And we are off a little bit in the cardinality estimation department. Again, this is, again, pile-on effect, right? In SQL Server, this could make a more profound difference where the cardinality estimation was off by more and we had to do something else with this result, join it off somewhere else, you know, whatever it is.

And then, you know, coming over here, we do have to, you know, again, repartition, sort, stream aggregate. So we do just a little bit more work in this query. The amount of work that these two queries do, and even that the two sorts do, pretty inconsequential in the, and like, you know, if you just looked at that case, like, you know, just on its own, you know, for the sorts, you would probably be like, oh, yeah, a serial plan that finishes in zero seconds is better than a parallel plan that takes a full second.

For the grouping queries, you know, again, nothing, nothing end of the world here. I just want you to understand that when you start adding in these extra unnecessary things, you start adding in extra unnecessary work for SQL Server to do in order to produce the result you want.

So the concept of sargability for me, you know, well, it is, you know, if you, if you want to polish some ivory on it, it is purely for situations like this, where, you know, you’re wrapping columns and functions and those, those, those functions prevent SQL Server from being able to use your indexes effectively to find data, right?

That’s search argumentable. But sargability for me kind of goes a little bit further because I feel like the concept of sargability is important to grasp because it’s not just for searching things.

Sargability is literally just about searching things, but I think that the concept of not disturbing the contents of your indexes with function calls wrapped around columns goes, needs like a bigger word for it.

There has to be like a concept beyond just like, sargability is this one part of it, and then there’s this other part of it where like, you’re just disturbing index order, and that disturbing that index order messes up SQL Server in all sorts of weird ways.

So it’s not just when like the sargability problem, when you’re searching on stuff. There’s also this problem where, you know, as soon as you like wrap a column in a function, SQL Server has to process things in a different place, right?

Because things like reading data and things like, you know, preserving index ordering, that’s like storage engine stuff. That’s stuff that happens like way, way down over here in a query plan, right? That happens like way, way down deep in the query plan.

Running functions, even built-in functions, for SQL Server, which have absolutely no relational meaning to the engine. Those happen like a few layers up in this like expression service of the query execution engine.

So they don’t happen down here. They don’t happen down here. They happen way up here. So like a bunch of stuff has to happen down here before you get to the point where you’re calling that function.

And that’s where you can really start screwing things up, is because you take away SQL Server’s ability to do things way deep down in the execution plan or in the storage engine.

And you all of a sudden force it to start doing stuff way up here where it’s far less efficient, right? And again, for this query, there’s only a couple hundred milliseconds difference. But you wouldn’t, I don’t think most people would expect to see a couple hundred milliseconds difference when the only thing that we’re doing really differently is either grouping by score on its own or grouping by is null score here, right?

So this is where things get, this is where we put the extra work in for this query. Now, again, 200 millisecond difference, not gonna end your world, but as you start dealing with bigger and bigger data or you start using data that you do this sort of thing to in the context of bigger queries, you’re gonna start noticing more and more profound differences and you’re gonna start seeing more and more profound problems with query performance as time goes on and your data grows and your queries get slower and you start wondering why.

And then you say, gosh, I ought to hire that Erik Darling to fix these problems. And then my job is to come along and delete is null. So, you know, there is that.

Anyway, thank you for watching. I hope you enjoyed yourselves. Hope you learned something. I hope that you didn’t skip over the part of the video where I asked you to like and subscribe and comment and hire me and buy training from me because that would be mean.

And I’m not mean to you. I don’t know why you’d be mean to me. So I’m gonna go upload this and then, I don’t know, maybe I’m gonna record something else.

We’ll see how it goes. I do need to get to the gym today. I do need to keep up this godlike physique that you see before you. So we’re gonna go to the gym.

Maybe we’ll talk more about that in a different video. I don’t know. Maybe that’ll be a TikTok exclusive. We’ll see what happens.

All right. Cool. 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.

Spring Training

---

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Register for Data Saturday, on September 7th here!

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 Tuning TOP PERCENT Queries In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into performance tuning top and percent queries—a topic that might not come up every day but is crucial when it does. We explore how to make these queries run much faster by using Common Table Expressions (CTEs) or derived tables instead of parameterized queries. By doing so, we eliminate the need for sorts in parallel plans, significantly reducing execution time. I walk through a stored procedure example where I demonstrate this technique step-by-step, showing how it can drastically improve performance without complicating your code too much. Additionally, I share some insights on upcoming events like Data Saturday Dallas and Past Data Summit, where you might catch me live. If you’re interested in learning more about SQL Server performance tuning or need help with health checks, performance analysis, or training, feel free to reach out; my channel is here for you.

Full Transcript

Erik Darling here with Darling Data. Look at that handsome logo. Look at that handsome head. Look at all that handsome floating around in here. We’re going to try something a little bit different in these videos because I did notice that when I told people to do stuff in the beginning of the video, they did stuff. So we’re going to start off with my appeals. at the beginning of the videos rather than just at the very end of the videos where most people have stopped watching because they’ve got what they needed from me. And usually those transactions don’t end quite as well. And also, it’s my channel and I can do what I want. When you get your channel, you can do what you want. In today’s video, we’re going to be talking about performance tuning top and percent queries, which is something you might not have to do very often in your career. But when you do have to do it, it can be quite an excruciating process. And we’re going to talk about how you can make it much easier on yourself. So about my channel, all of this content is free. And I’d like to keep it that way without commercials and other crap. So thank you, Intel for chiming in about the driver timeout. Great. Intel’s not doing so great lately. Their CEO was was praying on Twitter the other day as stocks are melting.

So we’re going to leave Intel be for a little bit. But yeah, all of this content is free. I like likes, I like comments, and I like subscribers. It’s a good way for me to know if I’m doing a good job or not. If you feel like supporting my channel, there are like low cost memberships where it’s like a few bucks a month if you just feel like saying thank you. You don’t have to, but if you’re feeling generous, go for it. I haven’t really promoted this much because I don’t really know what I want to do with it just yet. I think that ideally I would like to turn it into some more sort of like private one-on-one, well, maybe not one-on-one, but like private group stuff for people who donate at higher levels. We’ll see where it goes. I haven’t quite fleshed it all out yet, and I don’t know when I’m going to. So we’ll get to that. We’ll cross that bridge when we come to it. Hopefully, hopefully, and hopefully in a nice car. I’m a consultant. I do SQL Server performance tuning. If you need health checks, performance analysis, hands-on tuning, you have a SQL Server emergency, or you want to get your developers trained so that they don’t stink at SQL Server anymore, you can hire me. I do those things for money. That’s a good transaction for most people. I also sell training. If you go to training.erikdarling.com, there’s also a link in the show notes for this stuff.

You can get over 24 hours of streaming SQL Server performance tuning content at the beginner, intermediate, and advanced levels. And if you pay enough attention to this video or click the link in the show notes, you can get 75% off everything with the discount code SPRINGCLEANING. As far as upcoming events goes, where you can catch me live and in person, Friday, September 6th of this current year, 2024, I will be at Data Saturday Dallas. I have a pre-con on the Friday, and then I’m speaking at the regular event on Saturday. And of course, November 4th and 5th, I will be at Past Data Summit. This slide has a lot of white space on it, and I haven’t quite figured out how I want to fill that yet. It’s also possible that after September 6th, this slide will have a lot more white space on it, because I will be purely focused on being prepared for Past Data Summit.

So if you feel like seeing me in the flesh, as they say, those are two good ways to catch me there. And now we have gone black, which is a lot of the opposite problem of white space. And we’re going to move on and cover the subject of our video.

And well, let me actually close this. The query plans, we’re going to get to the query plans. But I have a couple indexes on the post table. I have one that supports what we’re searching for and what we’re ordering by.

And I have one that just supports what we’re searching for, because I want to show you two sort of different query plans related to that. Now, here’s the store procedure that I have that is looking for the top percent, top whatever percent we plug in rows for a specific post ID. You know, this probably is not, again, this is not the most common routine for returning rows out to a client.

But I do see a lot of people use it just because, you know, sometimes, you know, it becomes kind of wonky if you’re like, yeah, give me the top thousand rows, but a thousand rows don’t exist. So some people prefer to return a non-hard-coded number or a non-default number. They want to return a number of rows based on the population of data that they’ve got.

So, you know, it’s a somewhat, you know, somewhat less common thing to do, but it can also be a little bit less confusing when you’re like, yeah, give me the top thousand rows. But again, the thousand rows don’t come back. So I’ve got this query set up in a few different ways.

Four to be precise. Four. We’ll count them. Number one, just a regular top end percent query to start things off right here, where we’re just doing what we’re doing with no interference. And then below it, I have a query hinted to use the index that supports both the searching and the sorting here.

Then the third query is hinted to use the index that just supports the searching. And then finally, I have a query at the bottom where this is all expressed without any parameters. This is expressed with literal values, because what I wanted to show you is that this is not an effect of the top percent being parameterized.

This is just what top percent query plans tend to look like. Below this, I’m going to show you a way to make these go way faster. Okay.

So coming back to the query plans, I think potentially my new least favorite trail of query plan operators is a sort, which of course is parallel, because right next to it, we have a gather streams. And then that goes into an eager table spool. And the reason why I pre-ran all of these is you can probably guess by looking at some of the numbers here.

These are not the fastest boys in the world. No, these are very slow boys. This one takes nearly a full minute.

This one down here takes 30 seconds, which is an improvement by nearly half. But still not great. Still not what I would call fast.

And then, you know, down here, this is… I think the reason why I wanted to have the parallel… Rather, I wanted to have an index where both search and ordering is supported and where only search is supported is because I think the query plan difference here is interesting.

And the reason why I think the query plan difference here is interesting is, of course, because when you have a nested loops join, SQL Server only considers the stuff over here for, you know, how much faster it would be to execute the nested loops in parallel. Because parallel nested loops don’t operate the way…

Don’t always operate cooperatively the way that, like, parallel merge or hash joins do. Parallel merge or hash joins, you’re probably very used to seeing those where on the inner side of the join, the number of rows in the table is split up equally or split up hopefully as close to equally as possible amongst the dot threads in the query.

With a parallel nested loops join, you’re really running dot copies of the nested loops on the inner side. So every thread is going to have every row on it. There are a couple caveats to that that are too involved to cover here.

But just in general, when you see parallel nested loops, you should know that you’re dealing probably with a lot more rows than you think you are per thread. So this one is interesting because this query where we have the search and the sorting supported does not get a parallel plan. This gets a serial plan which runs for about 30 seconds, which again is twice as fast as any parallel plan that we have in here.

The one up top, fully parallel plan. Granted, we scanned the clustered index. We have kind of a nasty sort here.

But even going from the sort to the gather streams, that goes from 18 seconds, jumps up to 35 seconds. And then going into the eager table spool, that jumps up to 58 seconds. So really awful sort of like chunks of time spent waiting for those operators to process rows around.

The serial plan where we lose the sort, things do improve, but not, I mean, they improve and they improve dramatically. They improve their, this query is twice as fast as the other one, but it’s still slow as hell. Right.

And a lot of the time, you know, we have a full 18 seconds getting into there. And then, you know, this seek to key lookup thing is not a great scenario. We spend about almost 11 seconds just in these, these three operators alone. So that’s, that’s also unfortunate.

But what I thought what was really interesting was when we flip back to the parallel plan with this sort in it, we go from about three seconds here. This jumps up to 11 seconds here. So we spend, you know, just about eight seconds, you know, in the sort.

And then from there, it’s another like 15 seconds in the gather streams. And then a whole bunch of time in the eager index. So this, that series of operators is just real unfortunate in these plans.

And then, of course, the one way down at the bottom is, this is just the one with the literal values, which is pretty much an exact duplicate of the one up at top of the parameter. It’s a few seconds faster.

It’s about seven seconds faster, I think, for, you know, I don’t know, whatever reason. I didn’t really dig into why this one’s seven seconds faster. It’s not really pertinent. But in the moment, mainly what I wanted to show you here was just that you get the same plan shape with using literal values as parameterized values. So one way to tune these queries and to get much faster performance out of them is to use, you can use either a CTE or a derived table, whatever you want to do.

So I use the CTE here because I like to, you know, I like to give equal opportunity to queries where it doesn’t make a difference. And inside of the CTE, what I’m doing is I’m calculating the percentage using the top parameter and multiplying that by the count of records that we get from when we look at that. And then you divide that by 100 to get the percentage that you’re looking for, right?

So this piece of math right here will get us the top percent that we care about. And then what I’m doing down here is I am saying I am selecting. So that PCT table where I get the percent, that’s a pretty short hand abbreviation for percent.

I’m cross applying to the post table and I’m getting the top calculation from up from up here. So this, this records column, this is the one that this is the percentage. This is the number of rows that we need to get as that percentage.

And I’m passing that percentage in here. And I’m doing the same thing in here with the where clause. And I’m doing the same thing here with the ordering. And then one thing that, you know, you always need to be thinking about is that SQL Server does not guarantee ordering unless you tell it what to order by. So we’re even so just getting the select top percent in here ordered by creation date descending is not enough to guarantee that the external provided results, the presentation, the presented results will also be ordered correctly.

So we have another order by out here to take care of that. And the nice thing is that when we create this store procedure or rather when we alter that store procedure to use the new one, and I’m going to execute this the exact same way. Top one, post type ID equals one.

I’m going to execute this the exact same way. And this is going to be a lot faster. You’ll notice that, I mean, a lot of the time was spent returning the 60,000 rows out. The query itself actually finishes in about half a second.

So it’s very, very easy and convenient for us to seek to the rows we care about in here to aggregate, you know, do our count to get generate a number. And then inside the top here to get that 60,003 rows, which is the top 1%. All sounds great.

Everything’s good here. Where this pattern will generally apply well to most executions. As the percentage gets higher for a lot of rows, performance is going to suffer.

So if we crank this up to, let’s say, the top 20%, I mean, on top of the fact that, you know, we’re going to spend more time sending rows out to SSMS because, you know, 20% of 6 million is a, you know, fairly high number. Like, this is going to slow things down. But most of the slowdown is returning the results to SQL Server Management Studio.

You know, of course, I could dump it into a temp table and, you know, you know, return, maybe ignore it or return the results from there. But a lot of the time in here is just time spent returning the results. If you look at the actual query itself, the actual query itself finished executing in about three seconds flat.

We spent a whole bunch of extra time returning 1.2 million rows out. Now, there is a slight downside to this in that the top is always going to estimate 100 rows. But as long as your indexes are set up to support the seeking and ordering that you care about for the top end percent, that’s not going to hurt you too much.

Where it would hurt you is if you didn’t have the sorting element assigned in the index and you had a sort operator that had very variable, like, memory grant requirements. You know, obviously sorting 60,000 rows is a lot different than sorting 1.2 million rows as far as how much memory you’d need. Maybe if you’d want a parallel plan for that, stuff, things along those lines.

But generally, this pattern works out way better, like, up until a very, very high row count. And even then, so, like, let’s be a little bit honest about, like, high row count return queries. This thing dumped out 1.2 million rows, right?

That’s 20% of the 6 million something rows that have a post type ID of 1 in the post table. Cool. If you are returning 1.2 million rows to anybody with the exception of, like, exporting to a Excel file or some other kind of file format, ain’t no one looking at 1.2 million rows.

No one is going to go through all 1.2 million rows and do something with them aside from, like, copy and paste them to another thing to make those results more portable. Most likely an Excel file. Most, you know, if you dump out 1.2 million rows to an end user, they’re not going to do anything with 1.2 million rows in your application results, most likely.

They’re going to take those 1.2 million rows, paste them into Excel, do whatever, you know, goofy Excel stuff people do in Excel, and then, you know, use that for whatever they’re building, right? Whether it’s a chart, graph, something, VLOOKUP, I don’t know. Whatever people do in Excel.

It’s crazy. So, returning 1.2 million rows out to SQL Server Management Studio or an application is generally not something. Like, if you’re at the point where you’re doing that, I have questions that you can pay me to ask you about just what the hell you’re doing or what the hell an end user would be doing with that much data getting returned to them. Because most of the time, they’re not going to get to the end of 1.2 million results and be like, hmm, I’m satisfied now.

They’re just not. That’s generally not the way most human brains work, especially in this day and age where everyone is in therapy and medicated and has some sort of neurodivergency that prevents them from paying attention to 1.2 million rows of anything. Right?

Or that’s like, maybe that’s just basic human sanity. Like, 1.2 million rows? I’m not looking at all that. You would forget everything you saw. Right?

There’s generally no point to that. So, you know, I think for most people doing top and percent things, you’re going to be pretty safe with this setup because you will hopefully never be returning, you know, millions and millions of rows, especially to SQL Server Management Studio, which has a notoriously difficult time of ingesting, displaying, formatting, all that stuff quickly. It’s not a fast boy for that.

So, anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. I hope that you paid attention earlier in the video where I talked about liking and subscribing and hiring me and buying training and all the other stuff. Because, you know, I like to have friends when I record these and I don’t like to feel lonely.

So, anyway, thank you for watching. I’m going to prepare one of these other demos. You see I have many tabs open up at the top there.

I’m going to go prepare one of those to record. And then I’m going to change one slide in the deck and I’m going to do that all again. So, anyway, it’s my channel. Remember?

Do what I want. Okay, cool. 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.

A Little About Computed Columns, Filtered Indexes, and Indexed Views in SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the world of indexed views in SQL Server and how they can be used to circumvent some of the limitations associated with computed columns and indexes. I share my experience from a previous attempt where my voice sounded like it had emerged from a crypt, which led me to create this video with a bit more preparation. We explore why you can’t create filtered indexes on computed columns or indexed views, and how we can work around these limitations by incorporating the filter expression directly into the view definition. I also discuss the practical implications for standard edition users and enterprise edition users, emphasizing the importance of using the `NOEXPAND` hint to ensure that SQL Server utilizes the index view effectively.

Full Transcript

Erik Darling here with Darling Data. And this is actually my second take of this video because on the first take, it was actually the first words that I had spoken aloud all day. And I sounded like I had just emerged from a crypt. And it was actually, it even felt strange for me. So in this video, we are going to talk about how you can use indexed views to get around some of the limitations with computing. columns and indexes in SQL Server. It’s going to be a lot of fun, assuming I don’t start sneezing at any moment now. So the big problem with computed columns is you can’t create filtered indexes on them. You just can’t do it. It’s impossible. You just get errors. It’s actually kind of a miserable experience. Like many other things in SQL Server, it is pure pain and agony and suffering. So here’s what we’re going to do. We’re going to create this table called indexed view, which is not confusing at all, is it? It’s sort of like when you have a table with TBL in it, and then eventually something happens where you have to convert that to a view, it hits like a table with a different definition. And now you have a view called TBL something. It’s the same deal, right? So that’s good times. And we’re going to stick a little bit of data into this table just to make it semi-realistic. It’s only about 15,000 rows. Not a lot. That’s okay. We don’t need a lot to sort of prove through the concept. Now, the problem, like I was saying, is that you cannot create a computed column on an indexed view.

I’m sorry, on a filtered index. So what it says here is, filtered index n cannot be created on table dbo.indexed view because the column, not fizzbuzz in the filter expression is a computed column. So helpful. Rewrite the filter expression so that it does not include this column. Well, it’s the only column that I care about filtering, so what should I do there, Microsoft? Tell me what I should do. Illuminate me. I would love to know more. The one thing that I want to do can’t be done. Why bother telling me to do something else?

So here comes the indexed view. And before I create this, I should pause for a moment because, you know, again, like most things in SQL Server, there are agony and pain and suffering. Indexed views and their limitations are certainly a large source of pain and agony and suffering for many SQL Server users. They can be used to great effect in very simple scenarios. And perhaps their limitations are a feature and not just pure, you know, unabashed laziness.

Because they do kind of prevent people from putting really crazy things into index views and maybe index view maintenance for those things would be tough. But it is a real shame that even basic things like min and max aren’t supported by index views. Legend and lore has it it’s because Connor Cunningham decided one day that he decided when they’re putting index views in that having an additional nonclustered index to support the min and max was just too much.

So we don’t get that now. Great. Connor is working on CPU instructions and we’re still suffering with not being able to put min and max into an index view.

So the happy time for us, us leftovers in SQL Server. Grand. So, yeah, we can’t create this. We get enough errors.

No matter how many times you click this, we get errors. Now, the main place that I still find enough valid use for indexed views is on standard edition. And they’re great for standard edition because Microsoft has hobbled columnstore in batch mode in standard edition to the point of utter disrespect for their users.

So, like, often it’s just like, we can try it. What’s the point? It’s just, it’s like nothing.

It’s pointless. So, indexed views can be good because, you know, where columnstore in enterprise edition excels at being able to do large aggregations very quickly, you can often use indexed views to pre-compute those aggregations.

Indexed views do, of course, have, you know, maintenance downsides. But, you know, even in enterprise edition workloads where there’s some, you know, something about the workload or something about the table or something about, I don’t know, a million other things that make using columnstore indexes impractical or impossible, indexed views can even still be good there.

But, you know, in this case, we’re using it to get around a rowstore limitation with filtered indexes. So, let us continue. We are going to create a view.

And I just want to show you this part first because this part is interesting, too. We’re going to create this view called computed column. Again, not at all confusing. And we’re going to attempt to create a computed column, attempt to create a filtered index on that.

Now, we’re going to get a completely different error here. We are going to, it’s going to say, you know, let’s put this, let’s format this a little bit for easy reading. All right?

We don’t want, we don’t want to make reading any harder than it is. We are, we are high school dropouts after all. So, the filtered index can’t be created on computed column because it is not a user table. It is an indexed view.

Indexed views in general cannot have filtered indexes on them. So, but at least this error message is somewhat helpful. Consider creating an indexed view with the filter expression incorporated in the view definition.

Boy, howdy. We can do that, can’t we? We can follow those instructions. Those are actually useful instructions.

Whoever wrote that error message, you deserve some kind of, some sort of gold medal. I hope that you have gotten a good job after that. So, we’re going to put our filter expression into the indexed view.

And we are going to quote this part of the indexed creation out. And we are going to recreate both indexes. Now, this gets us where we want to go.

We have essentially an indexed materialized pre-computed thing filtered to the stuff that we care about. Right? So, like that computed column.

And the big problem, of course, was that we can’t create a filtered index on a computed column. We can’t create the indexed view to materialize that. And, you know, under most circumstances, as long as you have reasonable supporting backing indexes between, you know, the tables and the indexed view and the indexed view.

And, you know, just being honest, I’m really not a big fan of indexed views that span multiple tables. I’d rather create, like, two, like, indexed views with, like, one table apiece and then them join two tables in an indexed view most of the time. And it’s a treacherous set of circumstances.

But now we have what we want there. And part of this is going to be because of the small data set that I’m using. We don’t get a very, you know, interesting, we don’t get an interesting enough query plan just selecting some data from the table or just getting a count from the table matched on the not fizzbuzz column being equal to zero.

Right? We just, it’s just, it’s not 15,000 rows. It’s just, like, in real life, if someone was, like, we need an indexed view on this 15,000 row table, I would probably punch them.

It’s not reasonable. So when we run this query and we look at the execution plan, we will see something that we’ve seen in a few other examples and videos that I’ve recorded here before. This query is at the mercy of both getting a trivial plan and simple parameterization.

We can tell because the literal value that I used before has been replaced with at one. And that we have all these silly brackets that are completely unnecessary injected into our code. And we have a complete lack of as in the aliasing, which is something that I would never do because I’m a professional human being.

So in order to get around that, you know, you can always do the old one equals select one trick. If you’re going to, if you’re thinking about typing in the comments, why are you using one equals select one? I have bad news for you.

Type it into a search engine instead, and you will get both a blog post and a video where I explain it. So if you ask in the comments, you’re out banning you from my channel for life. Kidding.

I’m not. I will probably make fun of you a little bit, though. So if I run this and I look at the execution plan. Oh, yeah. Well, actually, I should backtrack a little bit.

What I meant to say up here is that because of all that, you can notice that instead of using the index view that I created, we’re using the base table, which is named index view. So that’s the thing we don’t like there. But if we add in the one equals select one, we avoid the trivial plan and the simple parameterization, we switch to using the index view that I created called computed column, which is precisely what we wanted.

But an important thing, particularly for standard edition users, the much abused, left behind standard edition users, is that you usually want to include the no expand hint. Standard edition does nothing for like computed column or rather does nothing for like index view matching. The no expand hint is necessary in like 99 point nearly infinite nine cases where I need where I want.

I need to I need to make sure that the index view is routinely hit rather than the base table base table behind the index view. The other really important thing, even for enterprise edition users, when it comes to index views and using the no expand hint, is this is the only way for SQL Server to create system statistics on columns in the index view. If you don’t put no expand in there, you don’t get any like it doesn’t create histograms for you.

It’s really weird. I don’t get it personally, but it is it is it does appear to be the case. So sometimes in query plans, you might see a warning that says like like, oh, columns with no columns with no statistics or something like that.

It’s a very misleading warning. Sometimes it’ll happen because it’s a index view and you don’t have statistics because of it. You didn’t use no expand when you queried it.

Other times it’s because you don’t have statistics in the very specific column order that SQL Server would want. And so like you do have statistics on these columns. It’s just not the exact it’s it’s almost like a like like a missing index request that’s wrong.

That’s like a missing statistics request that’s wrong because you do have statistics. It’s just not the specific statistics that SQL Server wants. So what did we learn today?

What did what did what did we learn? SQL Server has a lot of bizarre limitations. You know, you can create computed columns. You can create filtered indexes.

You just can’t create filtered indexes on computed columns. You can create index views, but you can’t create filtered indexes on index views. You can apply filters to index views and then create whatever indexes you want on that index on that view clustered and then whatever nonclustered indexes. But index views are a tough or a tough sell in a lot of cases.

You know, with the exception of people on standard edition who are have who, you know, can’t have like a just beat to death version of columnstore and batch mode available. You know, that’s that’s no good. And then like index views can be good there.

And then if you’re on enterprise edition, but for some reason column stores and column stores and no go for you for whatever other, you know, weird pathological reasons you have in your database, whether it’s, you know, the data types, cursors, you know, other kind of constraints and stuff. That’s another, you know, another story. columnstore does have some limitations, but, you know.

So I am still rather fond of columnstore most of the time. Rather, rather, rather sunny about columnstore. Got some good stuff going for it.

And Microsoft actually actively seems to be working on columnstore, which is a nice change of pace from index views, filtered indexes, partitioning, you know, a billion and a half other features that have been left at sort of like V1, V2 with no real investment afterwards. So, you know, columnstore at least has that going for it. It’s okay there.

So, there we go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, then you should, you can like the video. You can comment on the video.

You can subscribe to my channel, and you can join over 4,000 other lovely data darlings and getting notified every time. I publish one of these videos so that you can stay on the, I don’t know. I’d call it the cutting edge, but, gosh, is SQL Server the cutting edge anymore?

I often wonder. I often wonder. It’s a fun thing. Fun thing to ponder.

Fun thing to consider. Why? What happened to SQL Server? Why is everything seemingly spinning out of control? So, you can do that.

And then, I don’t know. Maybe you just like looking at me. Maybe you just like the sound of my voice. I don’t know. I don’t know what it is that gets people to subscribe.

But, if you do that, you get notified. And I’ll get larger subscriber counts. And larger subscriber counts are kind of my fetish at this point. So, you know, the higher that number goes, the happier I am.

And the happier I am, the more I record. So, it’s like a good feedback mechanism. You subscribe. I record.

We’re good. Right? Everyone. It’s a happy ending for everyone. All right. Apparently, I have a call starting soon. So, I’m going to go do that. And that call is with a nice client who is paying me money so that I can record these things for free.

There aren’t even commercials on my channel. Like some other SQL Server channels. So, you can watch these things uninterrupted.

And if you would like to hire me for consulting, well, I mean, you know my name. You know that my name is my website. And you can always get in touch with me that way.

All right. Great. Cool. Now, we’ve done all our plugs at the end. Because I’m an idiot and I should do it at the beginning. But, you know. Can’t teach an old dog new dogs.

All right. Goodbye. 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.