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.
Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper with you, and decided to submit two precons this year that we’d co-present.
Amazingly, they both got accepted. Cheers and applause. So this year, we’ll be double-teaming Monday and Tuesday with a couple pretty cool precons.
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
Here are the details!
Day One: A Practical Guide to Performance Tuning Internals
Whether you’re aiming to be the next great query tuning wizard or you simply need to tackle tough business problems at work, you need to understand what makes a workload run fast– and especially what makes it run slowly.
Erik Darling and Kendra Little will show you the practical way forward, and will introduce you to the internal subsystems of SQL Server with a practical guide to their capabilities, weaknesses, and most importantly what you need to know to troubleshoot them as a developer or DBA.
They’ll teach you how to use your understanding of the database engine, the storage engine, and the query optimizer to analyze problems and identify what is a nothingburger best practice and what changes will pay off with measurable improvements.
With a blend of bad jokes, expertise, and proven strategies, Erik and Kendra will set you up with practical skills and a clear understanding of how to apply these lessons to see immediate improvements in your own environments.
Day Two: Query Quest: Conquer SQL Server Performance Monsters
Picture this: a day crammed with fun, fascinating demonstrations for SQL Server and Azure SQL.
This isn’t your typical training day; this session follows the mantra of “learning by doing,” with a good dose of the unexpected. Think of this as a SQL Server video game, where Erik Darling and Kendra Little guide you through levels of weird query monsters and performance tuning obstacles.
By the time we reach the final boss, you’ll have developed an appetite for exploring the unknown and leveled up your confidence to tackle even the most daunting of database dilemmas.
It’s SQL Server, but not as you know it—more fun, more fascinating, and more scalable than you thought possible.
Going Further
We’re both really excited to deliver these, and have BIG PLANS to have these sessions build on each other so folks who attend both days have a real sense of continuity.
Of course, you’re welcome to pick and choose, but who’d wanna miss out on either of these with accolades like this?
pretty, pretty, pretty, pretty good
You can register for PASS Summit here, taking place live and in-person November 4-8 in Seattle.
See you there!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
A 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.
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.
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.
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.
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.
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.
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.
