Tag: Query Plan

A Little About Filtering On Dates In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into some quirky issues related to working with dates in SQL Server that have popped up during recent client engagements. I start by discussing a peculiar situation where a client was applying the `AT TIME ZONE` function incorrectly on a column instead of using it properly within a parameter or variable. This led to significant performance degradation, as demonstrated through query execution plans and performance tests. I then move on to another interesting scenario involving date-time columns, where over-communicating with SQL Server’s optimizer by filtering for non-null values in conjunction with a date parameter resulted in suboptimal query plans. By stripping away unnecessary predicates and using the correct data types, we were able to achieve much more efficient execution plans that significantly reduced read operations and improved overall performance.

Full Transcript

Erik Darling here with, well, you probably, you may have guessed by now, with Darling Data. And in this video, I’m going to talk about a couple kind of funny, quirky things that I’ve seen happen with clients lately. And, you know, clients, you know, the nice people who pay me money. If you’re interested in hiring me as a consultant, you can do that from my website. You can hire me. You can say, Erik Darling, please do your shamanic dances. Shamanic? Shamanic? Shamanic? Please come make our SQL Server faster, which I will do in exchange for money. Anyway, a couple funny type things that I’ve seen happen at clients recently had to do with working with dates. And, of course, I hear especially for the current generation of kids, dating is weird. Apparently, dating is also weird in databases. All sorts of strange things can happen. And we’re going to look at a couple strange things that I’ve seen recently. So, let’s do that. Alright. So, I’ve got an index, as I usually do, as I am want to have, and want to come packing an index on the creation date column. And what I found the client doing, was something backwards. Now, newer versions of SQL Server, and I say newer, but it’s been around for like 8,000 years at this point, have this fun at time zone thing. And what these crazy kids were doing is exactly what you see here, where they were using the at time zone function on a column, rather than using the at time zone function on a column, rather than using the at time zone function on a column.

So, we’re going to run a parameter or variable. In this case, it was a parameter. But for the purposes of this demo, I’m going to use a local variable because it’s a little bit more convenient for me. But I’m going to throw a recompile hint on these so that the local variable effect is taken out of the picture. Alright. So, if we run these two queries, we will see a rather profound difference in performance between them. Alright.

Alright. And running, and running, and running, and running, and running. And in the query where we call at time zone, on the comments table, on the creation date column in the comments table, we end up taking about almost six seconds to scan the index, apply the at time zone function, and compare it to the variable here. Right. Which is not good from a performance point of view.

We have an index. We came prepared. We came fully indexed to support this query, but we still had to scan that whole column. And whatever at time zone does behind the scenes is filthy foul. Because, like, you know, a dot eight query taking, like, almost six seconds to do this stuff? Bonkers.

Right? So, please, if you’re going to be doing time zone stuff in your queries, do not apply at time zone to a column in your join or where clause. Because it’ll be bad for you.

And the query where we apply at time zone to the, in this case, the local variable that we’re using in conjunction with a recompile hint to make the local variable not awful. This thing finishes in 80 milliseconds. Right?

And we efficiently use our index. So, we did a good here. Right? We did a just fine here. And, of course, you know, the results match. We get the same number of rows back from both of these.

We didn’t screw anything up too hard. And, again, you know, like I keep saying, in this case, anything you do that makes your job easier, like this, makes SQL Server’s job harder, like this. Right?

No good. No fun here. And then, you know, so there was that. And then another funny thing that I saw was, you know, like when you work with date and time information in SQL Server, often it can be challenging to get filtering right. You know, and there are, there are a lot of, there are a lot of circumstances when writing queries, tuning queries, making sure that they go as fast as they can.

We’re, you know, over communicating to SQL Server’s query optimizer can be a good thing. Right? Making sure that it has all of the relevant information available to it in order for it to make the best possible choices and come up with the best possible plan.

It’s a noble effort. Right? Right.

But what I ran into recently, and, you know, again, stepping back a little bit, was, you know, when you’re, when you’re working with date time columns, and the date time, the time portion is populated, but you don’t necessarily care about it too much. Like, you know, you’re usually looking for like a whole day, or a whole like month, or something like that. The time is often irrelevant.

But a lot of people will do, at least, so, there really, there are two groups of people. There are people who will, like, cast a date time to a VARCAR 10, which will get them, you know, the year, month, day, you know, four digits, and two digits, and two digits with a little doodad between them. It’ll get you a 10 character, 10 byte string with, you know, reasonably formatted date.

Those are some of the worst people you’ll ever meet. Another thing that some people will do, and this is the slightly better group of people, is they will just use a date parameter or variable or something, because that chops off the time portion, which you don’t, might not care about for every single predicate. So what I ran into was a query that looked a little bit like this, where there was an index, there was a perfectly good index on a date column, a date time column.

And people were using the data type to cut the time off a search query. And they were also over communicating by saying, well, we also just want to get where the data is not null. We just, we don’t care about nulls.

Which, you know, again, not always the worst idea in the world to do this. There are definitely some times, especially with joins, where I found this very useful. But, in this case, something strange was happening.

I’m going to show you what that something strange is via the query plan. So, I’m going to run just this one. And we’re going to look at the execution plan.

And, you know, this doesn’t run for a terribly long time. This isn’t by, this is by no stretch of the imagination, the worst, most inefficient query you’ll ever see. It was a lot worse with the client that I was working with because it was a much bigger table, right?

The post table in the Stack Overflow database, about 17 or so million rows. So, we can at least work our way through that data with an OK index pretty quickly. The real life example that I was looking at was a couple billion rows.

And things were, things were not good. We were reading, we were reading through a lot more data, which was taking a lot longer because the data didn’t always fit in memory. And things were just kind of icky.

We’re doing a lot of physical reads. So, I’m going to show you what happens in here. Where, what we do is rather than just find all the rows that qualify for where the last edit date is greater than the date parameter. We have this funny empty seek predicate down here.

And that’s looking for the is not null part, right? That’s what this is. We’re seeking to where is not null first. And then we have a residual predicate after we’ve eliminated the nulls to filter to the dates that we care about.

And we end up reading almost, what’s that? 6599963. So, almost 6.6 million rows when we only produce about 170, almost 173,000 rows.

Right? So, that’s not really a good read to row ratio, right? We’re doing a lot more work than we need to.

And with the client example again, because they were physical reads and not just logical reads, that was the real problem that we were having. And so, what I did first was, well, it’s like, you know, we don’t really need this predicate. Well, if you, you know, we just do, if we just filter for this, we’ll find everything we need.

It’s not like SQL Server needs to figure out what’s null or not first. It’s because nulls are never going to eat this, equal this. And, you know, it’s one of those weird things where, you know, developers who come from different backgrounds and, you know, like, you know, purely working with databases, they have a much harder time conceptualizing how to work with nulls in a database.

You’ll see a lot of use of isnull coalesce just unnecessarily, right? Just bad idea genes, throw isnull on everything because they’re used to nulls creating errors, right? There was a recent developer issue with nulls, apparently, over at CrowdStrike.

At least I think that’s what it was. I keep reading differing takes on exactly what happened. Some, everyone sees, a lot of people say it’s a null pointer.

Other people, no, it was this other thing. I don’t know. I’m not good enough at all that stuff to figure out exactly who’s right or wrong. You know, everyone seems to have their own hot take on the stack dumps there. But the first thing that I did was think, well, you know, it kind of doesn’t make sense for our seek predicate to find all the not null stuff and then a residual predicate to have to do all the other stuff.

So I just took the is not null portion out of there, right? And, you know, if we run this now, we go from 600 something milliseconds with a seek plus a residual predicate to about 17 milliseconds at the very end with a seek predicate to just the rows that we care about, right? If we look at this now, our seek predicate is, you know, alone.

And just comparing the last edit date, right? So we just do a regular plain seek to the dates that we care about here. Now, that’s a much better plan generally.

And the date thing in here is, you know, it doesn’t really cause any performance issues. I think this should be the execution plan with the thing that I want to show you in it. But like SQL, like SQL servers optimizer has these cool built in rules for dealing with like temporal stuff that like would usually be like a convert implicit and lead to like the bad kind of scans and with other data types.

So built in to the optimizer, because we have a date time column that has a time portion on it, but our parameter in this case is only a date, we have this get range with mismatched types thing come in. And this is just a thing that the optimizer has built in to say, okay, like I can still compare very easily the date portion of a date time column to a date, right? I don’t need to evaluate the binary representation of the time, right?

I can ignore that. So you have this get range with mismatched type thing, which is very useful in this case, because we can take a date and compare it to a date time and just compare the date portion of it, right? So we have that ability.

It’s pretty nice. There are a couple other optimizer rules that you’ll see around temporal stuff like that. If you go digging in the execution plan XML, you won’t see that looking through like just any of the tooltips or the properties or anything. It’s something that’s only in the XML.

So the other thing that you could do, which, you know, of course, if you wanted to retain your not dealing with the nullness of things, and you still wanted to, you know, it’s like, you pass it, you declare a date time, you know, like down here, I’m going to use date time, you can use this without a time portion affixed to it, and SQL Server will just zero it out. So you get effectively the same thing.

But you could also just, you know, pass in the right data type here. And even if you maintain the, you know, greater than date time thing and the is not null thing, if you use the right data type, these two will flip, right? So in the very first query plan that we looked at, the seek predicate was to is not null, and the residual predicate was to the date range.

But for this one, the seek predicate is now the date range and the residual predicate is is not null. So, you know, you could keep you could keep the is not null if you want and use the right data type and get effectively the same thing. I kind of like this query, you know, even like with or without the is not null, I generally like this query plan a little bit better because there’s a little bit less weirdness to it.

So we just do a plain old index seek here, and we produce our results in about 19 milliseconds. If we go back to this query plan real quick, I don’t know if you remember too much about this, but we had a little bit of extra stuff in the query plan, where SQL Server was producing, had this constant scan compute scale our stuff in it. Again, it’s not the end of the world, right? And these two queries, you know, they finish within a couple milliseconds of each other if you just keep running them over and over again.

Sometimes they’re a little bit different apart, sometimes they’re just exactly the same. So it’s not really a big deal here. It’s just it’s one of those things where in other cases where you see query patterns like this, especially if these constant scans produce a lot of rows, you’ll see this a lot in like query like joins with or clauses. You’ll see query patterns that sort of look like this with like constant scans and merge intervals and distinct sorts and then like a loop join.

And when you’re producing a lot of rows from these, it can be really awful. When you’re just when you’re actually just producing one row from this stuff, it’s not that big a deal. You can just do a seek, but sometimes getting stuck in this nested loops join when these when the operators like this produce a lot of rows that can be hellish for performance. So we want to avoid that. So anyway, that’s about all I had to say here today.

Some quirky stuff when working with dates and SQL Server, right? Of course. And we’re going to go through our usual spiel again, aren’t we? We’re going to we’re going to we’re going to say or I’m going to say not we I have I have nothing else.

I do not contain multitudes. I get I get nothing. There’s just there’s just what you see is what you get. Thank you for watching. Especially you. Hope you enjoyed yourselves. Hope you learned something. If you like SQL Server videos like this, you can you can a like this video by way of a thumbs up or a comment.

The comment does not have to be positive. But I will fight you over excessively negative comments in person. Will physically feels it. I will physically altercation you. If if you like this video again thumbs up comments if you like this sort of content, you can subscribe to the channel and join over 4000 other data darlings out there in the data verse.

This is getting awful. This is getting awful. In subscribing to the channel so you can get notified when I do these things and I make a complete buffoon of myself in front of you.

So yeah, and you can also hire me to do this personally like I’ll give you like a like a private dance of me doing this. It looks wonderful. It’s a whole whole whole thing that people just love right. I’m coming up on 700 clients since since I started this business and I’m pretty pretty psyched on that.

So if you want to be lucky number 700 if you want to join the 700 Club not not the that channel thing, but like, you know, having been someone who worked with me. That’d be cool, too. Anyway, I’m gonna go do actual work now.

Thank you for watching and I will see you in the next video.

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 Filter Operators In SQL Server Query Plans

---

Thanks for watching!

Video Summary

In this video, I delve into the importance of filter operators in query plans and why you shouldn’t ignore them. Erik Darling from Darling Data shares insights on when these operators are expected to appear and when they might indicate performance issues. I cover various scenarios where filters can be seen as normal, such as with halving clauses or windowing functions, but emphasize that a high number of filter operators generally suggests potential problems. The video also highlights examples like non-sargable predicates and max data types, explaining how these can lead to unnecessary row filtering late in the query plan, which is inefficient. Throughout, I provide practical advice on how to identify and address performance issues related to filter operators, including testing different query permutations and considering alternative join methods for better optimization.

Full Transcript

Erik Darling here with Darling Data, and today we’re going to talk about why you shouldn’t ignore filter operators in query plans. Now, I’ve done a few videos where I sort of touched on the filter operators. I mean, not like actually touched on the filter operators. Guy could go to jail for that. But just where I sort of talked a little bit about them and sort of like sometimes why they show up, sometimes when they’re bad. So I wanted to do one kind of specifically about filter operators, why you might see them and when you might see them and when you should pay attention to them and also like sort of how to fix queries where you see them and the queries are slow. Now, in general, filter operators are not a good sign. We’re going to talk about when they’re sort of expected sign and when they’re a bad sign. But in general, when I see query plans with lots of lots of filters in them, I get a little nervous. Mostly because, you know, filter operators are a lot of filter operators are a lot of filters in them. So, you know, filter operators are SQL Server removing rows from a result set at some point in a query plan, right? But really, you don’t want to see rows get removed from a query plan like over here. You want to see rows get removed from a query plan like as close to when you touch an index as possible. Ideally, as you’re actually touching the index, right? Like when you seek into an index, when you scan an index, that’s when you want to see rows get removed from a query plan.

SQL Server filtering out the rows. You don’t want those rows carried over through other operations and then removed later, right? You want to work with a minimal set of rows as possible. Grammatically, that seems daft. That was a bad sentence. You want SQL Server to deal with as few rows as possible as early as possible, right? You want to remove as many rows as possible as early in the query plan, as far to the right in a query plan as you can. As soon as you start removing queries, as soon as you start removing rows far to the left in a query plan, you have started to fail from a performance perspective.

So, when you would expect to see filters in query plans, here are a few examples. One is when you use a halving clause. And since halving clauses work on aggregates, you have to figure out exactly which rows meet that requirement. No rows are going to meet this requirement. That’s totally okay. I just need to show you a query plan where, you know, the SQL Server doesn’t know how many rows are going to meet that count predicate from the halving clause, right?

Where count big is greater than 2.1 billion, blah, blah, blahs. Well, you have to fully execute that query and do that count to figure out which rows would actually qualify for that. So, when you use a halving clause, you can rightly expect to see a query plan with a filter in it to filter out those rows after that expression has been calculated.

Another sort of similar idea is when you’re filtering out on like a windowing function because you have to, at runtime, like again, just like a count big, at runtime, you have to execute that windowing function, come up with what that windowing function expresses itself out to over the results that you’re asking it to express over and filter that out later. So, here’s another example of a query plan with a filter in it.

And this filter is totally reasonable because we need to generate that row number and figure out where it meets the predicate applied to it, right? That row number has to get calculated just like the count big. Similarly, you might see, so, this one’s a little tricky to explain because there are, you know, some caveats and, you know, other things to consider with it.

But just to make it, give you an easy example, if you play tricks on SQL Server to get batch mode into your queries, like create, like my example over here where I’m creating a temp table with a columnstore index on it, and I’m doing this sort of silly join to that table in here, this will generally get SQL Server to do something batch mode-y at some point in the query plan. But like I’ve talked about in other videos, this does not get you batch mode on rowstore the way the batch mode on rowstore feature gets you batch mode on rowstore.

This might get you a few operators to operate in batch mode, but it is not the full batch mode on rowstore experience. So, you end up with this query plan that’s a mix of row and batch mode operators, and batch mode bitmaps are not really tightly integrated into that. So, you’ll end up with a query plan that looks a little something like this, where you have a filter over here, and that filter comes from a batch mode bitmap, right?

Because if you look over here at this hatch join, hash, hash join, not hatch join, we are not hatcheting anyone. This hash join does execute in batch mode, and it does produce a bitmap, right? If you right-click on this, where is that thing?

Batch, yes, define values, opt bitmap. So, this is a hash join where there is a bitmap involved, and that bitmap gets applied, but not over here. We’re like, you know, a row mode bitmap, wow, row mode bitmap, we’re not even going to repeat that one.

Row mode bitmap, you would normally see applied when you scan or seek in a clustered index. In this case, though, it is not applied over here, it is applied in a filter operator after the fact, which can actually be kind of crappy for performance if you’re scanning really big tables.

Another one that I’ve covered in a previous video is you might see filter operators for startup expression predicates. Now, you can ignore the sort of red squiggles in here, because this is just a statement from a store procedure that I pulled out. This is the store procedure that I pulled it out from, I just wanted to show you what it looks like, where there’s sort of this conditional join logic.

So, if we are going to check the post table, then we actually do this join, or if we are going to check the comments table, then we actually do this join. And what that looks like from the query plan perspective is something like this, where you have one, two filter operators in the query plan.

I’m not covering up either of them, good for me. But if you look at the filter operator, you have this startup expression predicate. So, if check posts is true, then we will touch the post table, or if check comments is true, then we will touch the comments table over here, which I am blocking a little bit, but that’s okay.

This filter operator, you can still mostly see what we care about in here, right? So, these are some reasons why you might see sort of expected filter operators. These aren’t necessarily good, bad, ugly, somewhere in between for performance, but they are things that I would generally keep an eye on, and just, you know, make sure that I understand why they’re showing up.

And especially in the case of the startup expression predicate, I would definitely want to test this store procedure like I did in the video that I recorded for you, which I’ll put a link to in the show notes here.

I would definitely want to check this, any query that does something like this, with different permutations using, or reusing a query plan for check post equals one, and then check common SQL is one, because you can run into some really tricky performance issues when you do things like this.

Once again, anything you do that makes your job easier makes SQL Server’s job harder. These are words to live by when you are developing in T-SQL, or probably just any even unhyphenated SQL.

So, of course, times when you will see filters in query plans, because you did something necessarily silly, would be if you wrote a completely non-sargable predicate.

Say, we’re going to actually just, you know, we’re going to run these two queries together, and mondu, I did not semicolon that query. Let’s, you know what, let’s do a little plug for my friend SQL prompt, and let’s say, let’s insert semicolons so that we make sure we don’t have that problem again.

Thank you, SQL prompt. This video is now unofficially sponsored by SQL prompt, which can help you insert your semicolon. I often lovingly and jokingly refer to SQL prompt as my $75 a year semicolon inserter.

You can, if you feel that was maybe a not exactly family-friendly name for it, well, that’s on you, that’s not on me. So, for these two queries, the first one, of course, we are doing something awful with our lives.

We are wrapping not one, but two columns in a date diff function. And, you know, this is generally not a great way to write the query. And I’ll show you the difference between these two.

Now, even though we are going to break the sargability rules a little bit here, and we are going to have at least these columns in a non-sargable sort of fashion in the where clause here, we should see a pretty decent improvement in query speed by doing more work in writing some additional stuff out.

So, this top query up here, of course, we have a filter. Way later in the query plan, we fully scan the post table. We fully scan our index on the post table.

That’s not really the point here. The point here is that in the bottom query plan, if we look at the nonclustered index scan here, we have absolutely no predicate applied to it at all.

All of the rows from the post table are filtered out way over here, right? This predicate greater than five. If we do things a little bit differently, we still end up with an index scan, but we reduce all the rows at the index scan here.

Now, granted, this is still not fantastic. We are still scanning the index. We are not seeking into the index. Partially, this is because of the way the index is designed, because we have the owner user ID column that we’re joining on first.

We don’t have the creation date or last activity date column prioritized in the index for seeking. It’s okay here, right? Not every index can be absolutely perfect.

Excuse me. But, you know, at least writing the query in a slightly more sane way, right, where we at least have some clean expression on the creation date and last activity date columns, even though we do something non-sargible with them over here, which, you know, again, not great, but at least tolerable in this point, we actually get a pretty decent speed up on this query.

You know, not a huge, awful thing anyway. This was 1.6 seconds, almost 1.7 seconds, and this was just under a second. So we shaved a pretty okay amount of time off this query just by typing a little bit more. Again, we made our lives a lot harder.

We typed a lot more stuff. We had to do a lot more work. But when we did it the easy way, we made SQL Server’s job harder. And the harder you make SQL Server work, the angrier your end users are.

All right. Again, words to live by. Another time you might see that in a sort of similar way. And again, this is a matter of surgability.

If you were looking to figure out if a column meets a certain length requirement, wrapping that column in a function can be pretty bad. Really, all you need to do is see if that column contains any character in it, right?

And the underscore wildcard is any single character. So if we run these two queries, again, sort of a similar setup. The user’s table is very small.

It’s only a couple million rows. So you’re not going to see a dramatic performance improvement here. But in real life, in your actual queries, you might find this sort of change in querying to be very helpful. So if we look at these two.

Oops, that did not drag where I wanted it to. Curse you. Damn your eyes, SQL Server Management Studio. Why won’t you give me the draggy thing? If we look at these two queries, of course, the one where we use data length and the where clause results in a filter after scanning the entire user’s table for all the rows.

In this one here, we don’t have that filter operator, right? We have a like of a single underscore character, which is, again, wildcard for any character. They both get the same results. And, you know, again, because the user’s table isn’t huge, there’s not a terrible time difference on this.

But in real life, you can probably see more dramatic results with, you know, the queries that you’re working on tuning, right? So, you know, this is generally bad up here. This is generally a better way to figure out if a column meets a certain length requirement.

Another time when you’ll see filters is when you make bad join decisions. Again, if you go through any, you know, sort of SQL, you know, code camp thing, they’ll only show you the first way of figuring out rows in a table that don’t exist in another table.

And it’s doing a left join from one table to another and then figuring out where the primary key or keys of the column that you’re left joining to are null. Now, again, this isn’t going to be a gigantic speedup, but it’ll be noticeable.

Where the top query with the left join, we have to fully join users to posts, right? We do absolutely no filtering here.

We do all the filtering much later on over here, right? And that filter operator is, of course, asking for where the ID column in the post table is null, right? So this filter operator is usually a pretty bad sign.

This won’t always, always, always, always, always, a thousand million billion percent be the case. There will, of course, be times where, you know, you might test the same query paradigm two different ways, right?

You might test it with the left join, searching for the null. You might check with not exists. Depending on indexing and a number of other things, you might find that the left join is faster.

It’s totally happened to me in my life where I was just like, I know how to make this better. And then it did not get better, at least not without some, you know, indexing magic and stuff.

So definitely two ways to check on here. But, you know, whenever I see a filter operator in a left join type query like this, I get nervous and I want to try, at least try the rewrite using not exists instead of the left join thing.

Another thing that can cause filter operators to show up in your query plans are when you are dealing with max data types. This goes for column data types and this also goes for parameter or variable data types.

Now, I have a recompile hint on the query over here because I want you to, what I want you to see is that just declaring the variable as max means that SQL Server can do nothing helpful for you.

Right? So this and this query up here, the about me column is an envarchar maximum. We can see that right here. And if we want to search on that column, we’re going to have a filter operator in our query plan.

And unfortunately, there is no helping this. You could create a computed column that is a substring of like a reasonable search index for your query, right?

Like you might not need to search all 2.0 bajillion gigs of the bytes of data in this column. You might only need the first like 100 or even if you made this an envarchar 4000, which is a decent chunk of stuff to be able to search through, this would be okay.

A lot of people might say, oh, full text indexes, but full text indexes in SQL Server stink. They’re awful. Don’t even bother. You’re better off using like Elasticsearch or something.

Once you start thinking about full text indexes in SQL Server, you should mentally just jump right to Elasticsearch because that will actually be better for you than anything else.

And again, if we define our parameters and variables with reasonable data types, we will not see filter operators in our query plans. We will see regular index seeks and scans like in this one down here.

This one is where the location variable is declared as an envarchar 100, which matches the length of the location column. And this one up here, where I made location in envarchar max, we filter that data out later.

Even with a recompile hint, SQL Server can’t like sniff the fact that we only had, that the length of our local variable was four bytes. It was here, or I guess eight bytes because it’s unicode.

SQL Server can’t figure that out, right? So SQL Server can’t just discard the maxness of the one up there. It has to, it has to, you know, fully scan whatever table, apply that, and apply that envarchar max filter later.

Another time when it’s particularly bad, and I’m going to start this running because this is kind of an amusing one, is when you put UDFs of nearly any variety in your join or where clause.

Now, you, so let’s limit this at first to non-inlineable scalar UDFs because, you know, SQL Server 2019, at least, you know, if you, you know, have all the right incantations, will attempt to inline some scalar UDFs.

There are a lot of restrictions on it. There are a lot of caveats to it. And performance isn’t always better. Sometimes you might start seeing eager index pools and query plans where SQL Server has rewritten your scalar UDFs as inline versions.

That just means you stink at indexing and you need to fix something. But in this case, you know, you could also see this with multi-statement table-dialued functions, inline table-dialued functions.

You are far less inclined to see issues like this. You still might, but very, again, you have to really screw something up within the inline table-dialued function to make that happen.

So here are two queries, the top 175 and the top 180. And what’s kind of funny is if we look at how long each of these ran for, when we, for the top 175, we do okay.

All right? We still had to read a bunch of rows here, but not nearly as many as we read down here. And what’s very funny about all this, right, is that this query took three, oh, sorry, I messed up highlighting on that.

This query took about four seconds. This query took 34 seconds. All right? And why? I’m going to tell you why. This is very funny. This is kind of like a good, this is a good, like, like, level 500 DBA row goal joke.

This top query found if I lift up this arm and I zoom in correctly and appropriately and I don’t mess anything up. Ah, gosh darn you.

We get 175 rows back. Good on us, right? That’s the one that took four seconds. If you look at this query, well, you might, I’m going to get things lined up down here.

I’m going to lift up my arm. I’m going to scroll over and keep scrolling over and there we go. We are at 179 rows. So I asked for the top 180, but only 179 rows qualify.

So we, we, we wasted a lot of time trying to meet, trying to find that 180th row. That’s why, in the query plan for this one, we could actually get done pretty quickly because we only needed 175 rows.

In this one, where we asked for one more row that actually existed, we needed to have all 2.4 million rows come out of the user’s table to search for that elusive 180th row, but we did not find it.

And that is why we had so much more work done in here. Uh, and this filter operator, the filter operator for both of these is going to be our scalar UDF. Now, I know you’re going to, you’re going to say something, uh, about putting scalar UDFs in computed columns.

And maybe you might even say something about schema binding. Uh, if you’re the type of person who puts scalar UDFs in computed columns, you deserve to be smacked with everything that someone can find within reasonable reaching, reasonable reaching distance and, and smacked with.

Whether it’s a fish or rubber hosing or, uh, you know, a chair or something like that. Because sticking computed columns, sticking scalar UDFs in computed columns will, will doom you to a lifetime of, of query misery.

Um, they will force every single query without like, you know, trace, you can use trace lag 174 to get around this sometimes. Uh, but in general, every single query that touches your table will be forced to run single threaded.

Uh, it, you will have a bad time with all sorts of indexing and maintenance tasks. Uh, you will have a bad time with DBCC, check DB, unless you’re just running in a physical only. A lot of things get bonked up.

That also goes for computed columns and, uh, check constraints, sorry, scalar UDFs and check constraints. So, scalar UDFs and check constraints and computed columns. again, uh, you know, think the immortal words of 50 cent.

We will, we will get the strap. All right. another thing that will cause, uh, filter operators to appear in your query plans. Now, uh, this, this door procedure called Longingly is available at my GitHub repo.

Uh, it really only has one use and that is to, I’m going to show you what it does in the debug mode for a small number of loops. Uh, what it does is, um, it will, uh, declare, uh, like however many, whatever you set for loops, that’s how many, uh, uh, variables it will declare.

Right? So, I chose five, so we have five variables in here. Uh, it’ll also generate this select count from users where reputation is less than zero or where display name is in however many, uh, parameters we decide to create and then the query that it executes final at the end is all those declares with the, uh, with the final statement.

Right? So, fun times there. Now, the execution plan for this one is useless. We don’t need to look at it, but the execution plans for these are a little bit more interesting because what we’re going to see is that when we only search for 14, uh, of those, we will have a clustered index scan and we will filter out rows at the cluster index scan.

Clustered index scan. I, I can’t remember if I said that right the first time, but this is basically what that looks like where reputation is less than zero or display name is in, or display name equals blah, blah, blah, blah, blah up through parameter 14.

Right? We can see parameter 14 right there or P14, which is, I guess it’s a local variable because I declared it. As soon as we hit 15, SQL Server says, this is far too complicated for me.

You have made my job hard. I’m going to use a filter. I’m going to scan the whole table and we are going to apply the filter later. So again, we’re bringing all the rows from, uh, the, from the user’s table.

Every single one, we apply no predicate here. We apply the entire predicate in this filter. So where reputation is less than zero or, uh, we are in, well, I mean, you can see P15 there.

So at least, you know, for whatever value of complexity we have in this query, uh, 15 in, in, in clause arguments was where SQL Server went, I’m done.

You might see this with fewer, uh, in clause arguments. You might see this with more in clause arguments. I don’t know. It depends on a lot of, it depends on a lot of things, but, this is just another example of where SQL Server might throw a filter operator at you when you were expecting those rows to get eliminated a lot earlier.

Again, performance for this particular example isn’t disastrous because, you know, again, user’s table, pretty small. We’re not doing a ton of work. Uh, we don’t have, like, a much bigger query where we did lots of other joins and stuff and then that filter got applied way later.

Just a simple example so you can kind of see what I mean. So, that brings us to the end of, of my examples. Uh, I’m gonna hit save there. Oh, no, I’m gonna hit, I think I hit the wrong button.

We’re gonna hit save there to make sure that I retain all my semicolons. Uh, once again, thank you to Redgate SQL Prompt for inserting semicolons for me. Automatically.

Is that DevOps? I don’t know. We’ll find out. It’ll be fun. So, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. Uh, if you like this video, uh, I like thumbs.

I like comments. You can leave me, I mean, you know, good, bad, or ugly. I don’t care. Comments in general. Anything that I can respond to, right? Uh, we like to, we like to generate, um, we like to generate, lots of, uh, interaction, right, on these videos.

the more interaction I get, the better. Even if, even if you call me a fat dummy. But then I’ll come find you. I don’t care where you live. I got, I got, I got, I got, I got Delta Miles.

Boy, howdy. I can, I can come find anyone. Uh, uh, if you like this sort of SQL Server content, you can subscribe to my channel. There’s a, there are buttons that you can push, uh, where you can get notified.

And I’m going to make sure that I have the most, um, up to date count. Uh, so you can join 4,009. I finally crossed the 4,000 mark. I’m going to, I’m going to overtake a few Amiga repair channels at some point.

Uh, you can join 4,009, uh, data darlings, uh, and, and getting notified when, when I, well, actually, you know, I mean, some people might have subscribed and turned notifications off.

I got Delta Miles for you too. All right. Um, so we’ll, we’ll, we’ll get that done. All right. Anyway, uh, I think that’s about good here.

I’m going to get this uploaded and scheduled and all that good stuff. Uh, thank you for watching. Uh, all, all of my data darlings. I, I appreciate you. Oh, so very much.

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.

How String Columns Can Mess Up Query Performance In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into how string data in your tables can significantly impact query performance, particularly focusing on memory grants. I explore why SQL Server estimates that half of a string column’s byte length is full, leading to potential issues when you select multiple text columns or have non-ideal indexes for sorting operations. By running through two example queries—one including the text column and one not—I demonstrate how this can cause substantial differences in execution time and memory usage, with spills to tempDB being a common issue. I also highlight more extreme cases where selecting large string data can lead to severe performance degradation, even causing SQL Server to run out of temporary disk space and fail queries.

Full Transcript

Erik Darling here with Darling Data. And, you know, I’m having a nice day. I returned from my family vacation. I say family trip because when you travel with kids, it’s not a vacation, it’s a trip. Just careful distinction there. There is not a lot of rest and relaxation. There is just playing referee in a different place. So, you know, here I am. And in this video, we’re going to talk about, well, basically how string data in your tables can mess up query performance. Now, there are a couple other ways that we’re going to discuss today. But today, this one, we’re going to focus on how string, columns can mess up query performance in the area of memory grants. Now, if you’ve watched other videos of mine, you may recall me talking about how when SQL Server estimates memory for a query, it starts with a serial execution plan. And it figures out how much memory would take for a single thread to execute your execution plan, right? Whatever the executable plan for the query is.

The things that primarily impact memory grants are sorts and hashes. Okay? It’s because of the way SQL Server estimates string column fullness, where whatever the byte length of your string column is, SQL Server assumes that about half of it will be full. So, for a VARCAR 100, SQL Server assumes that every row will have 50 bytes full. The more rows you select and the more text columns you select, or the more string columns, I’m going to say text because text is an actual data type. The more string columns you select, depending on their unicode-ness and other things like that, you can really blow up memory grants.

Now, I have a pretty non-ideal index for the query I’m going to run, but that’s because I want to show you what happens when we need to sort data, right? So, I have two different, well, I mean, basically, I have the same query twice. The only difference is up here in the select list. I am including the text column from the comments table, and the text column isn’t in VARCAR 700. And the second query, even though I’m doing a select C dot star here, since I’m not pulling the text column out in the final select, SQL Server is free to ignore it. So, and I have recompile hints on these queries, just because I don’t want any sort of automation or, you know, behind the scenes, you know, intelligent query stuff messing with my demos, right? I just want to show you, like, out of the box, you run this thing, what stinks about it, right?

So, the first query runs, and we select the text column, and the second query runs, we don’t select the text column, and let’s look at some differences between these two execution plans. Well, right off the bat, you might notice that this query runs for about twice as long as this query, right? Let’s just call that close enough to twice as long, right? 4.7 and 9.1 something, good enough. A couple hundred milliseconds off, who’s counting?

The databases, do we care about milliseconds? Yes, we do. We care about everything. And the primary source of the slowness is in the sort operator, where we spill here, right? And that takes about 8 seconds, and this takes about 3.8 seconds. And of course, it’s going to be because the spills are different, right? This one, oops, I didn’t go down far enough. Story of my life.

And this one, we have the warning. We spilled to level 2, and all 8 threads went kaboom. And that’s a big number of pages. If we put some commas in there, because SSMS, I don’t know, comma allergy. Let’s just put a little dot here. Oh, wait, that didn’t dot. That is close enough to a dot. And then 3 more, and that’s 411 million pages or something like that. I can’t even count that high. I’ve run out of fingers very early on. So that’s a pretty big spill.

And if we compare that to the spill down here, even though this is still spill level 2 and 8 spilled threads, that’s a lot fewer pages. And the reason we have more pages spilling is, of course, because we have that text column. And even though, well, let’s look before we say even though, right? Let’s make sure that we understand.

This one asks for a query of 4,296 KB. And this one asks for, well, the exact same memory grant. SQL Server asked for the exact same memory grant for both. And this one spilled a lot more, didn’t it? Interesting stuff there, right?

Even though we don’t have the text column in this one, right? Creation date, post ID score. And this one, we have our text column. Even though SQL Server knew that there was that text column in there, it asked for the same memory grant. That memory grant just didn’t do well for either of these.

Now, if we redo the index a little bit, and the point of redoing the index is to help out our windowing function on post ID and score. And just keep in mind, of course, that the order of the sorting for the windowing function makes a huge difference. Now, granted, I don’t, I mean, at least I’m pretty sure.

In all of the documentation I’ve read, you can’t partition by something descending. You can only partition by something ascending, probably. You can order by things descending.

So if we really do need the score descending here in order to make this index effective for the query that we’re running. And what sucks about both of them, too, is that if you don’t include all the columns that you’re selecting out of the table, you really, really knock down the chance of SQL Server choosing a narrow, nonclustered index to help with this stuff.

Because it just straight up doesn’t want to do lookups for row number a lot of the time, right? Just forget about it, right? Unless it’s a very, very small number of rows.

So looking at these two queries again, right? And this time, we’re going to completely get rid of the sort and the need for, well, the need to sort data to apply that dense rank windowing function in here. There we go.

With that in place, both of these queries take just about the same amount of time, right? We don’t have any big difference here because we don’t have that sort that asks for the same amount of memory in spills and all hell breaks loose, where one spill took twice as long as the other spill.

So we completely got rid of the portion of the sort and the query plan that used to live in this section, right? And now we just have the seek and the building of the windowing function stuff in there. So a lot of the times, you can fix problems with these sorts of things just by having the better indexing in place, right?

So not all that, like, you know, you can’t always just go tweaking indexes on the fly. But if you spot these things, then, you know, indexing can be one way to help out with the pains of string columns, especially when sorting data. The other thing that we want to look at is a more egregious example.

So I have two different queries here against the post table. And now the text column in the comments table is an envarkar 700, which is pretty small beans in the grand scheme of things, right? 700, you know, I guess 1,400 bytes, seven gizzard unicodes, and blah, blah, blah.

But the body table in the post table is an envarkar maximum. It’s a big one. Big boy.

Big boy. And if we run these two queries, we also might be able to spot some differences here. Some significant differences. So that first query finished.

And now we’re waiting on this second query to finish, aren’t we? We’re going to wait a little bit longer on this. Not going so well.

Second query. But that first query finished, I don’t know, it seemed like about eight, nine seconds maybe. I don’t know. This one’s really dragging on with that body column. I’m starting to think that strings are a mistake.

Especially, you know, maybe, you know, storing entire potential two-point-something gig strings in a database was maybe not an awesome idea. Maybe we shouldn’t be doing this. Maybe we should, you know, put them somewhere else.

Let’s put them in a text file on disk, right? And just have a pointer to the text file on disk. Or I don’t know.

Perhaps there is some other storage engine that stores that sort of large text more effectively. But holy cow, we have some differences here, don’t we? We have some big differences.

We have another very big spill difference. Let’s edge these two things a little bit closer together. There we go. We have one sort that took 5.5 seconds and another sort that took almost 49 seconds. Well, I mean, not technically 49 seconds.

Because we did some stuff over here. We did some stuff over here. And we’re going to ignore the time in the repartition streams because the timing code for parallel exchanges is absolute garbage. It just looks dumb and stupid and weird everywhere.

So just ignore that. Just forget that part for this example. Now, let’s compare memory grants. All right.

1214 megs for the one where we just select ID. And for the one where we also select the body column, we end up with a 17 gig memory grant. 17 gigs of our precious memory.

All dedicated to a sort. All right. Just one sort operator was like, hey, 17 gigs, pretty please. I could use that for sorting things.

You have an Envarcar Max. What do I know? Right? That’s pretty foul. And the differences in the spills are, of course, you know, spill level 1, 8 spilled threads, 40,000 pages. And here we have spill level 1, 8 spilled threads, but 3881976.

So 3.8 million pages. But this has had a significantly worse time because we have that body column involved. And we have to write that body column, this sort of stuff out to disk and reread it back in.

And gosh, what a pain that is. Now, at the far more extreme end of that, I’m going to show you two queries that ran over here. And if I lift up my left armpit and we focus in on the time down here, we have three minutes.

Let me move over. Three minutes and 30 seconds of execution time. Now, you may also notice that there is a little message down here that says query completed with errors.

What errors, pray tell? What happened? Hmm.

I don’t know. Let’s find out. Let’s go to the messages tab. And let’s see. Wow, that’s a lot of red text. I’m going to have to duck a little bit. But so basically what happened is that first query spilled so much to tempDB that I blew up tempDB.

I exploded it. I ran out of tempDB. I ran out of drive space on my VM.

I didn’t just run out of like, I didn’t just like fill up tempDB files because I have like an unreasonable max size on them. No, no, no. If you look at, I have a load disk space message on my VM.

And if we look at Windows Explorer and let’s look at, oh, let’s go to this PC where we see it. You know, I’ve got a 400 gig drive on this thing. And I have filled up tempDB nearly to the size of the drive.

Right. So I blew up a lot of tempDB space with this query. Now, of course, because of that, I don’t have an execution plan for this query. But I can show you, of course, that in the second query, if I quote out the body column, right, just I green text that body column.

Again, sometimes green text is very, very good in SQL Server, right? Sometimes we have wonderful uses of green text by getting rid of envarchar max columns from our select list. Well, go to the execution plan over here.

This query still ran for a minute and 14 seconds. All right. It’s a pretty long running query. So if we subtract the minute 14 from the 330 over here, that other query ran for like, you know, I guess a couple minutes and then failed.

Right. Filled up tempDB and failed. All because we were, you know, selecting that body column.

And that body column had to pass through a couple different sort operators. Well, actually, just one sort operator. The votes table down here. I mean, you know, I think there was another video that I recorded where I talked about a very, very offensive execution plan.

Where SQL Server chose a merge join with the votes table and chose to sort the entire votes table. All right. But it’s not this sort’s fault.

This just sucks in general. Right. This just makes all sorts of awful things happen. But the big problem is in the final sort. Because this final sort is where the body column from the post table would be involved.

The body column from the post table has nothing to do with sorting the votes table down here. It’s all this sort where, you know, even without the body column, we still have a spill. And we wrote, let’s see, 1904184.1.9 million pages out to disk.

And that’s just without the body. So, like, SQL Server just, you know, kind of chose a, I don’t know, just not good enough memory grant for this thing. Let’s see what it chose.

17 gigs again. Again, so, why 17 gigs? Well, on my server, which I believe, I forget what I have max server memory set to. I think it’s like 90 something gigs.

SQL Server is willing to give out 25% of your max server memory setting to any query, any single query running. So, 17 gigs is about the max memory grant that I can hand out to anyone. And 17 gigs is not big enough for some of these queries.

All right, just 17 gigs, we still spill out to disk. Whether that’s a function of, you know, the way memory is shared between operators and query plans, which, you know, it totally can be, or something else, just, you know, the amount of data we’re selecting.

You know, that’s a fairly large number of rows and a fairly big number of columns. I mean, from the post table, I mean, realistically, we do have a couple other text columns in here, like tags and title and last editor display name.

And if we quoted those out, you know, maybe we would reduce the spill. It might still spill. I don’t know.

It’s too lazy to check on that right now. Got other things to do. But, yeah, so when you run into these things, a lot of the times the worst spills that you will find are because you, either you did something that required SQL Server to sort data.

You have a presentation order by. You have top with an order by or offset fetch. Or you have a windowing function without an index to support the partition by order by part of the windowing function in order to get you this data in the order that you need it to, you know, apply whatever windowing you need to your data.

You know, those are things that will add, you know, sort problems in. Now, I think what’s really interesting with this one is that, and I sort of wish I had saved it, but it wouldn’t really have done you any good.

This query will still error out if I put a hash join hint on here. Because, again, the only part of this query that, you know, had like a dumb injected sort is when SQL Server chose a merge join here, right?

So SQL Server costed a merge join as being the more effective join type here, which requires sorted data. So SQL Server sorts the votes table, and so this does sort and spill, and this does require memory, but the problem sort is the one up here because we don’t have an index to support the windowing function that we are applying to the post table.

So this is the real big one because this is an absolutely phenomenal number of rows, and a lot of this stuff spills out. So even with just a couple text columns in there, we can still have some pretty significantly bad query performance.

So, in summation, as much as you can, as much as possible, you know, if you’re going to put strings in your database, avoid max data types wherever you can, right?

I understand some queries require them. So rather, some data structures require them. You might be storing XML or JSON or, like, you know, notes, like, you know, whatever. Some, like, open text entry field.

You know, there are reasons why you might need in VARCAR or VARCAR max columns in your data or in your tables, but, you know, just don’t use them unnecessarily. Please try to right-size your string columns as much as possible.

And when you’re doing things like if you really need sorted data for some reason, the two things that you can try to do are pre-sort your data and then fetch text columns later. I do have videos about that, informational versus relational columns.

It’s a very important concept. You know, like, you know, usually in order to provide a result set for someone, your joins, your where clause, your sorting can be done in a different stage of the query processing than retrieving all the columns you need to show someone.

So, like, not doing everything in one big swoop like this is, you know, usually a pretty good idea for query performance. You know, sometimes temp tables are a good use.

Sometimes you can use derived queries in some manner, whether it’s a CTE or, you know, it’s a, you know, sort of a derived table expression. There are different ways to separate those things out so that you don’t have to, you don’t have to select every single column and apply sorting to all of those columns.

You can do the initial set of data joining, filtering, sorting, and then go get the columns that you’re interested in displaying, right, the informational columns after you process the relational columns.

I have videos about that somewhere on my channel. I’ll go try to find them when I publish this. But no promises because, I don’t know, searching is hard, isn’t it? A little difficult.

So, anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something about how awful strings are in databases. I hope that you have now started a project to move all your strings to text files on the file system and just have pointers to the text files.

Kidding. I mean, if they’re PDFs or, like, Word docs, fine, like, file stream type stuff, fine. But, you know, if it’s just something for the table, I guess, I guess you could deal with that. Yeah.

Anyway, I have things to do, so I’m going to go do them. Again, well, I guess, I was going to think of one another. Oh, yeah. If you like this video, you can subscribe to my channel. And you can join the nearly 4,000 other data darlings.

Actually, maybe by the time this thing actually gets published, there will be 4,000 data darlings out there in the known universe. If you like this video, give it a thumbs up.

If you have anything to tell me about this video, you can comment on it. Good, bad, or ugly? Just don’t call me ugly. That’s personal.

Then we have to fight. But, yeah, you know, strings and databases are a very perilous thing. The number of problems that they can cause. So I’m going to be recording another video soon about other problems that max data types and string filtering can cause in queries, especially query performance issues.

We’re going to have one of those coming up soon. It’s maybe even in the text file right over here called don’t ignore filters. Might even be right in there.

Yeah, but, you know, strings and databases are a perilous thing. The bigger they are, the harder your queries fall. And just like we saw in this query where I was able to completely blow up 10DB with a sort, you know, it’s probably not why you want your queries failing.

You probably don’t want queries failing at all. Right? So, yeah, watch out for those string columns, George. Boom.

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

What Clients See

---

One of the chief things my clients complain about when opening support cases with Microsoft is that the people working support tickets on the Microsoft side… Aren’t actually Microsoft employees.

They’re third party vendors.

When they end up working with a third party vendor, the lack of expertise and poor communication is obvious and painful.

Many support cases are handled by third party vendors, and my clients often complain that their staff has totally inadequate SQL Server knowledge to do the job.

Even when my clients do get someone from Microsoft, the person they get is juggling an enormous caseload. The truly knowledgeable folks are worked to the bone and scattered across hundreds of cases.

The amount of turnover in support roles at Microsoft is also pretty astounding. I used to know a lot of people who worked in them, and… they’ve all moved on to other roles.

At this point, you’re lucky to get someone with 18 months of SQL Server experience handling your support case. That’s a far cry from when 18 months was about the minimum experience you’d find.

The worst part? I have clients who have paid Microsoft upwards of $75k USD for support contracts, and they can’t get anyone to answer basic questions or help with basic troubleshooting.

If you want to read a Great Post™️ about how bad things have gotten, head over here: How to Survive Opening A Microsoft Support Ticket for SQL Server or Azure SQL

You Deserve Better

---

But you won’t get it any time soon from Microsoft.

They’ve cratered their once excellent support infrastructure and replaced it with a frustrating, pass-the-buck, often indifferent group of third-party vendors, and overworked internal staff.

The decline in Microsoft SQL Server support quality can have serious consequences for your business. Inefficient support can lead to extended downtime, data loss, and decreased productivity.

Your business deserves better, and that’s where I come in.

With years of experience in SQL Server management and support, I understand the complexities and the importance of prompt, effective support.

Here’s what I offer:

1. Expert Knowledge: As a recognized SQL Server expert, I have a deep understanding of SQL Server and can provide the expert support that outsourced teams often lack.
2. Clear Communication: I prioritize clear, effective communication to ensure that I fully understand your issues and provide the best possible solutions.
3. Tailored Solutions: I take the time to understand your specific environment and needs, providing solutions that are tailored to your business.
4. Consistent Support: You’ll receive consistent, high-quality support that you can rely on to keep your systems running smoothly.
5. Help Dealing with Microsoft Support: You need someone on your side who can work through support cases, provide necessary technical details, and call support staff on their BS when they give you lousy reports.

Don’t let declining support quality affect your business. Hire me to handle your support tickets and ensure that your SQL Server environment receives the expert care it deserves.

With my help, you can focus on what you do best — running your business — while I take care of the rest.

Reach out today to discuss how I can support your SQL Server needs and provide the expertise and reliability that your business deserves.

Together, we can ensure that your technology works for you, not against you.

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.

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.

A Performance Mystery With Parameterized TOP In SQL Server

---

Thanks for watching!

Video Summary

In this video, I dive into an intriguing and somewhat obscure performance issue in SQL Server related to parameterizing the `TOP` clause within stored procedures. You’ll see how using a literal value for `TOP` can lead to more efficient query plans compared to parameterizing it, especially when dealing with small numbers under 101. This behavior is due to exchange packet sending rules that allow earlier data transmission in certain scenarios. I explore this issue through various examples and demonstrate the impact on query performance, showing why this problem might go unnoticed until it causes significant delays. By the end of the video, you’ll understand how to mitigate this issue with better indexing strategies, using `OPTIMIZE FOR` hints, or by rewriting your queries for optimal performance.

Full Transcript

Erik Darling here with Darling Data, cleaning up a few silly little things in my way. And in this video, we’re going to talk about the dangers of parameterizing top in SQL Server. And this is maybe the weirdest query performance problem. that I have ever run across that wasn’t like a bug. It was actually sort of known behavior by like three people in the world. So that was fun. So what I have here is a store procedure. And this store procedure really only takes one parameter because that’s all it has to. And that one parameter is used here. where we select a top number of rows from the users table. And then we cross apply to the badges table to find the top one. Well, actually just mean to put this into English, to find the most recent badge that a user has gotten. And for this, the purpose of this query, we only care about users who have a reputation over, I think that’s 10,000. And we were going to order by reputation descending. All right, cool. So that’s the store procedure version. We also have the just the plainly written version with a literal value here for 38. And then we have the sniff top version here where we’re saying at top equals 38. The query plans for these, well, I had to run these ahead of time, because I don’t want to stand here for a minute and 13 seconds waiting for this query to finish. Much like we’re probably going to stand here for a minute and 13 seconds while I try to grab the right part of the query plan to mush things together. So looking at these two queries, and I I think, I mean, you know, probably the funniest part about all this is if like, if you saw this query, like in the plan cache or something, or in query store, monitoring tool or something like that, it would be really hard to decipher, maybe why one time it ran for seven seconds, and the other time it ran for a minute and 13 seconds. You know, cost costing being what it is and all that.

Nothing, nothing is very clear about this. And I know that there’s some green text up there. And the green text isn’t totally wrong. The green text is right. This, this, both of these would be faster if I had an index on the badges table. But I need to show you the behavior without the index, where things are really terrible and crappy, so that you understand what’s happening and what you need to fix. All right. So yes, an index would be helpful here. But that’s not that’s what I’m trying to explain the behavior to you. Not teach you another, not teach you another, this is not just, this is not another stupid video about adding an index. Any, any half-witted consultant in the world can make another post about just adding an index.

Me, I try to give you a little bit more, because you’re nice people and you deserve more than half-witted consultants just telling you to add indexes. So I need to move over a little bit to make sure all the green stays on the screen. Unofficially, when a top uses a constant, and the constant is a small number under 101, the exchange packets are allowed to send earlier than normal, as long as the exchange is below the top operator. The exchange packets are allowed to send as soon as they have a row, rather than waiting for those exchange packets to fill up completely.

This can only happen with constants or another thing that I’m going to show you in a minute. And this behavior is true going back to SQL Server 2005. I’m of course doing my testing on SQL Server 2016. Sorry, 2022 version 16, which you can see if you look right there under my armpit.

It may change in the future. So if you’re reading this at some far-off date and Microsoft has decided to fix this stupid issue, then I’m wrong all of a sudden. Well, you know, sorry. I was right for a long time, though. So I had that going for me.

When you parameterize top, it’s not considered safe to send exchange buffers early anymore, because you could stick any number in there up through the big int max, because top takes up to the big int max for a number of rows.

In cases where you’ve got a big top, say the big int max, which is this nine, this is that number right there that starts with a nine and has one, two, three, four, five, six commas in it. A lot of commas.

If you were sending that many rows one row at a time, it would be significantly more awful than sending over a smaller number of very full exchange buffers. If you’re surprised to hear that parallel exchange buffers can send at different times, depending on different situations, you’re not alone.

I was also very surprised. Not in a good way. Not in a, I get a golden ticket. It was more like, like when the doctor calls you on a weekend.

You’re like, what happened? So in the second query, where the exchange buffers are only sent when full, we spend a lot longer waiting for those exchange buffers to fill up so they can send.

This isn’t exposed anywhere in the query plan. You’re not going to be able to see this anywhere else in the world, unless you have a good, unless you’re handy with a debugger or you pay attention to things I say.

So obviously that’s not a good situation. And there are a couple ways to fix it. Of course, there actually, there are a few ways to fix it.

One way, you know, in my case, better indexing would mask a lot of the problem, but not completely solve the problem. One way of doing it is to add an optimize for hint so that your query, so that you tell SQL Server to optimize the query plan for top being equal to one.

And if you do that, this isn’t going to make the query faster than the original version with the literals. It’s going to make it on par with the original version with the literals.

That’s all. This, this thing is still going to take seven or eight seconds because I don’t have any good indexes, but this at least gets us a plan that doesn’t take like almost a minute and a half. Right.

And then another way of doing it is to rewrite the query to. And so, you know, like one thing that I try to stress whenever I’m teaching people about SQL Server stuff is there are a number of different ways to write a query.

And if you’re not getting very good performance with one of them, you should try another one. That’s query tuning. Top one is, you know, a sort of a shortcut, you know, top one with an order by.

You know, it’s sort of a shortcut for like getting a max or something or using row number. In this case, if we use a row number and we pump up the optimizer cardinality compatibility level, it’s not, not cardinality compatibility level to 150.

or 160. So that we get batch mode on rowstore. Oh, this, this formatting murder, murder on me.

Did I fix this one? I didn’t. Oh boy. SQL prompt is really letting me down. I’m going to mess everything up here. Another. So if we rewrite the query to get the top one using a slightly different method, and we allow SQL Server to use batch mode, this is, this will completely negate the need for an index because this finishes just about as quickly as one could hope for.

Sans and index, right? Cause this, this gets batch mode. You can tell by the window aggregate there. That’s a good thing. All sorts of good things happen in this query plan using batch mode that weren’t happening in the other plans.

So, uh, you don’t always need an index, do you? You don’t always need an index, do you?

You know what else you don’t always need? Half-witted consultants. You need full-witted consultants. Full of wits. Lots of wits. So, if you’re out there in the crazy borderlands of SQL Server, and you run into a query with a top that’s parameterized, and the execution is real weird, the execution time is real weird, well, you might be running into this.

And if you’re running into this, things you can do, one, um, explore, uh, better indexing scenarios so that, uh, you can at least mask the problem a little bit.

Uh, two, uh, stick an optimize for, uh, on the query, uh, optimize for the top being equal to one. That will at least help you understand if you’re hitting this problem.

Uh, that actually, that would really help you understand if you’re hitting the problem, and it might actually, uh, improve performance. And of course, number three, you can rewrite the query in a way that gets you, uh, you know, things differently, right?

Like one thing, like I said, I always try to teach people. There are many ways to write queries. Uh, if the first way isn’t fast enough, try the second way. The second way for me here was using row number rather than, uh, top one, because the row number, uh, gives you different sort of set of, uh, optimizer stuff that the top one thing, usually won’t get you, like the batch mode on rowstore for the row number thing there.

So, a few things you can try if you’re running into this problem. I hope you never run into this problem. It is a terrible, awful, no good, very bad problem.

Uh, uh, and when I ran into it, I almost cried. But, then I remembered, Robert Smith told me, boys don’t cry.

And so, well, didn’t, didn’t cry. Never, never had, never will. It was, I don’t, I don’t want to let Robert Smith down. He seems, he seems very sad.

if he, if he, if he knew I was out there crying, I don’t know, I don’t know that he could take it. I don’t know how long he would be for the world. So anyway, hope you enjoyed yourselves.

Hope you learned something. I, I actually, I truly hope that all of you have learned this from this video. Cause if you knew about this before, I don’t know.

I, I would, I would feel terrible. It’s, God, this sucks. Uh, if you like this video, which you might be, if you, if you like watching videos of car accidents and, um, like early, early road runner videos, uh, you, you can, you can give me a thumbs up.

Uh, you can, you can leave a, uh, motivationally, uh, I don’t know. Nice. Just a nice comment. Something, something sweet roses and stuff.

Uh, if you like this sort of SQL Server performance tuning content, uh, you can subscribe to the whole channel and you can get what can watch all the videos and you can get notifications every time I post a new one, which will be pretty frequently because I have nothing else to do except post videos for you.

Cause I love you and I miss you. And it’s been, it’s been too long. We haven’t hung out. Anyway, I’m going, I’m thinking I’m going to find something to do. It’s not standing in front of my computer for a little bit because I think the lights are beginning to affect me.

So thank you for watching. the industry is. 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 T-SQL Query To Get The Text Between Two Delimiters In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of using the `SUBSTRING` function in SQL Server to extract text between two delimiters. Erik Darling from Darling Data shares his insights on a common pitfall: misunderstanding how the arguments work. He explains that while many assume `SUBSTRING` works with start and end positions, it actually requires specifying the length after the starting point. To illustrate this, he uses examples from the `sys.messages` view, highlighting the importance of being precise when defining these parameters to avoid errors like “invalid length passed.” The video is a must-watch for anyone who frequently deals with string manipulation in SQL Server, as it demystifies a function that, despite its simplicity, often trips up even experienced users.

Full Transcript

Erik Darling here with Darling Data. Of course, who else would have me at this point? I can’t imagine. I can’t imagine at all. In today’s video, we’re going to talk about how to get the text between two delimiters in a SQL Server query. The reason why we’re going to do that, and I know this may sound dull and pedestrian to many of you, the reason why I’m going to do this is because every month I get this email from Google, and it’s all about my website, search results, performance, and stuff like that. And every month, the blog post that I wrote about this is the number one post. All the stuff that I write and record about, all the things that I do, how to get the text between two delimiters is the big winner. Alright, fine. Okay, you get a YouTube video too. That’s your prize. For being the number one blog post on erikdarling.com, formerly ericdarlingdata.com, you get a YouTube video. Memorialized, canonized, itemized, you get a YouTube video.

So the first thing that everyone gets wrong about substring is what each of the arguments does. And this isn’t purely in the context of SQL Server. There may be other implementations of substring that do things differently. But in SQL Server, the substring function has three arguments, and they are the input expression, in other words, the thing that we are going to get a substring of, the start position of that string, and then the length of the string. It is not start and end, right? This third one does not meant to find the end position of a string.

This is where you tell substring how long the string is after the starting point. Alright? So the way this works, and I’m going to be using the sys.messages built-in view for this.

And I’m going to specifically be searching for entries in the text field that have at least two semicolons in them so that this works. If you don’t do that, or if you don’t have something, if you don’t, like, if you’re not really confident about the substring stuff of the character presence or position in the string, you’re going to write a lot of really defensive, like, code around this.

Otherwise, you’re just going to get a bunch of, like, you know, invalid length passed to whatever errors. And those suck, and I hate debugging them, and it’s miserable, and I don’t want to do it. So I try to be as defensive as I can when I write these things.

So anyway, this is sort of a basic way of doing stuff. The fully parsed string portion is down here. This is the only section…

Zoom it, you are on my last nerve. This is the only section that’s going to actually do what I’m trying to teach you how to do. There are a few other columns in here to enumerate what the actual first, second… Come on, zoom it.

…and third arguments are as they’re passed in. All right? So we’re going to run this. And we’re going to look at the results. And what we’re going to see is…

Well, I mean, exactly what I was telling you about here. The first argument is the text of the error messages from sys.messages. The second argument is, like I said, the starting point of the string.

And the third argument… Notice that this is not always a higher number than this, because all this is is the length of the string after this argument, right?

So for this one, we go from the 71st byte and then we go 6 bytes over. This one, we start at byte 59 and we go 441 bytes over. This one, we start at byte 31 and we go to a 13, right?

So again, first argument, the input string. Second argument, starting position. Third argument, length of the string.

That’s why when you’re doing this stuff, what you have to do to make sure that you get all that positioning right is a lot of jumping around.

And it’s really annoying and I hate it. And I’ve had to do this for my entire SQL Server career. And I almost never remember how to do it correctly the first time around. And if it’s one of those situations where we need to do like a reverse and find like the last thing in a string and then jump, forget it.

I have to look for like 15 different blog posts before I can get that one right. So for the parse string one, though, the thing that we’re actually learning how to do today, the first argument, pretty self-explanatory, just m.text, right?

That’s all we need there. The second one, we want the car index in the text column of colon plus the length of whatever you’re searching for. So if we were searching for something that were two characters here, if we were searching for like double colon, then the len would have to change to two, all right?

Or we’d have to change plus two or plus double colon here. Otherwise, we would have an inaccurate starting position. The third one for the length of the string, this is where things get annoying and complicated, right?

The first two things, fairly self-explanatory, right? The thing that we want to split, the thing that we want to substring rather, and then the car index of the first thing that we care about plus the length of that first thing that we care about so that we start on the thing, like right next to it, right?

We like colon right next to the colon. You get what I mean. The third one, this is the tough part.

So one of the nice things about car index as a function is that it, where that, so pad index does not have this, car index does have this, is car index supports a third argument optionally, where you can tell it the starting position that you want to start looking at.

So for this is going to look exactly like it did up above. We’re looking for the first colon in the string, but the third argument is going to tell our query to look for, is going to start looking after it finds this.

So basically, after it finds this thing plus the length of this thing, right? This line up here is basically just a copy and paste of this. We want to find the very next colon in text after we find a colon in the text plus the length of the colon right there, right?

So because remember, we got to bump up one over so we don’t capture like anything stupid. Then, and this is where, this is the part that always murders my brain. After that, we have to subtract, right?

We have a little subtracty thing here. That means subtract. The subtracty thing means subtract. We have to subtract the length of the thing that we’re looking for. And we also have to subtract the car index of the first position that we find, right?

So essentially, we have to subtract this from this so that we get that, we get the correct string length. Because remember, we’re not, substring in SQL Server is not expression start end, it’s expression start length.

So in order to figure out how many characters or how many bytes over we need to go from the thing we care about, we need to subtract the first position that we find and the length of the thing from the second position that we found, right?

Because that’s what gives us the text between delimiters and not just text from delimiter to the end of the string. I know.

This stuff isn’t fun. Most sensible people, if they have a job and they work for a place, they’ll probably write an inline table valued function to do this because remembering this is awful.

But it is the most popular post on my blog, a popular blog on my entire website, so it gets a YouTube video.

And that’s about it. So apparently a lot of people always forget how to do this. Myself included. Anyway, thank you for watching.

I hope you finally learned how to use substring in SQL Server. I hope you enjoyed yourselves. I hope you learned something, like maybe what substring actually does in SQL Server.

If you like this video, lots of thumbs, lots of comments. I’ll take anything.

Send me a letter. Just kidding. I don’t want, I can’t read, I can’t read. And if you like this sort of SQL Server content, maybe not exactly this SQL Server content.

Apparently a lot of people really like this. Maybe this is what I’m going to blow up on. If you like this sort of SQL Server content, you can subscribe to the channel, like 4,000 almost other people have.

I don’t know what I’m going to start saying when over 4,000 people have subscribed. I’m optimistic that I’ll survive, I’ll live to see that.

But anyway, thank you for watching. Happy, happy strings, happy substringing. Oh, yeah.

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

How To Get Only Letters Or Numbers From A String For Free In SQL Server

---

Thanks for reading!

Video Summary

In this video, I delve into three underappreciated functions from my GitHub repository that can significantly enhance your SQL Server string manipulation tasks. Often, when working with clients, I encounter subpar versions of these functions—clunky and inefficient code that could be easily replaced by the well-optimized solutions I present here. The focus is on `get letters`, `get numbers`, and `strip characters`—functions designed to streamline common string operations like extracting specific character sets or removing unwanted characters from strings. These inline table-valued functions are efficient, avoiding the pitfalls of scalar UDFs and multi-statement table valued functions, making them a valuable addition to your SQL toolkit. Whether you’re dealing with phone numbers, usernames, or any other text data that requires cleaning, these functions can save you time and effort. So, give them a try and see how they can improve your workflow!

Full Transcript

Erik Darling here. Still alive. Amazingly. Still talking about SQL Server. Probably a little less amazingly. In this video I want to talk a little bit about three functions that are in my GitHub repo that don’t get enough, if any, attention whatsoever. Look, you know, look, look, I got a GitHub repo. Ooh, everyone has a GitHub repo. It’s not anything crazy to have a GitHub repo. Most of what gets attention in my GitHub repo are the stored procedures I write to help with SQL Server troubleshooting. Things like the stuff over here that starts with the SPs like SP Health Parser and SP Human Events and SP Log Hunter and SP Pressure Detector and SP Quickie Store. There are a lot of SPs in there. These string functions get very, very, very, very much, attention. And it’s sort of a shame because when I’m working with clients, I often see like really crappy versions of these functions like copy and pasted from some website from the year 2000. Inevitably scale our UDFs, every single one of them. Some of them multi statement table valued functions. But like always like while loops and other crap that make your query easier.

Keep those queries awful and hate you slow and make you need to hire me. Maybe I shouldn’t be talking about it. Keep those queries slow. Call me. I’ll do something. I’ll wave my hands at them. But the three basic things that I see a lot of people trying to do in queries a lot has to do with like string manipulation. Microsoft has given us some help with string manipulation lately, sort of. We got a string split and a string ag and I don’t know. I guess that’s it. You get what you pay for. It’s only $7,000 a core. Why develop anything useful? Apparently we’re getting regex. Azure SQL DB has regex in preview, which can only mean SQL Server vNext is getting regex in preview. So, um, can’t wait to start seeing regex in a where clause. To be honest with you, gonna make the, revive the entire SQL Server community having regex in where clause. Everyone’s coming back. Everyone’s coming back.

All right. Not a sink and ship at all. So, uh, these are the, what the three functions do. Uh, they are called get letters, get numbers, and strip characters. Uh, they all do nearly the same thing. I’m going to talk about what’s in these files a little bit before I show you the, the stuff. So in every file, there’s two versions of these. Uh, there’s one version where you can use a numbers table and the numbers table does help a little bit with performance. There’s also another version that uses a CTE to build up a sort of, uh, internal numbers table, uh, that looks like this. Right? So there’s a little bit more work in there. Uh, the numbers table is a bit faster. It’s not, you know, depending on what you’re doing, the, this, the speed difference is, uh, somewhere between negligible and, um, uh, profound.

So, uh, you know, make sure that whatever version of these you’re using suits your use case appropriately. Uh, so what these things both do is, uh, this one is get letters. So what this does is it basically, uh, uses that string splitting thing to do some XML-ing. And what we do is look for, uh, basically any single character in a string that matches the AZ-AZ, uh, uh, uh, pattern, right? So that’s all the, you know, characters between A and Z. Um, so that, that, that, that’s what that does. Uh, if you need out weird characters, I don’t know, write your own function. Uh, and then the, the get numbers function does just about the same thing, except it only looks for where, uh, the single character is like zero to nine.

The strip characters one is a little bit different because what this does is it seeks to remove, uh, some, some matched expression from, uh, from your strings. All right. So they, they all function slightly differently. Um, I think maybe, I mean, sort of theoretically, technically, strip characters could replace both get letters and get numbers. But, um, I, I sometimes find that it is, it is, it is helpful to write code that is geared towards a specific task.

Uh, the, the more, um, the more that your code might have to do or, like, the more generalized your code is, sometimes the less efficient it is. Uh, overly generalized code, big, bulky, slow, lots of thinking, decision-making, uh, things to check on. Uh, more focused, narrow-casted code generally tends to be faster and work better.

So, uh, with that out of the way, let’s go over to Management Studio and let’s just see a couple examples of these things working. So, uh, this is the get numbers, uh, function. And if I run this, uh, we’ll see, uh, a bunch of nulls where some usernames don’t have numbers.

And then for lines where the usernames do have numbers, we will only get the numbers from them. Things like 4614. Without the user, right?

So, that’s, that’s, that’s get numbers, which is pretty handy. Uh, get letters does the exact opposite of get numbers, where, uh, when we get down to this batch of users, where there, there, there were numbers there at some point, uh, the numbers have been completely stripped out.

All right? So, fun stuff there. Uh, the strip characters one, well, like I said, there’s a, there’s a solid case for strip characters replacing both. So, both of those, uh, in different ways.

But, um, well, so like there are some weird, there are some weird characters in some of these. It’s a screwy Unicode thing. Uh, the results look a little weird sometimes with this.

But if you look at, uh, the, the, the display name column in the Stack Overflow database has some, has some very weird things in it. It’s an Envarcar and strange things happen. So, but we, when we want to, uh, strip out characters, um, well, this is, this is the one where we, where we were removing numbers.

And we can see the numbers get removed in here. Like that. And for the one where we were looking to, uh, do the opposite, which is one where we were trying to remove all that stuff.

Uh, so there’s, like I said, there are some strange things in here. But the, the important thing is that for these, for these rows, uh, we only get the numbers back from those. Uh, for, for these, uh, I would have to, like, go through and, like, do, like, an ASCII or Unicode check to figure out exactly what is odd in here.

But Tomek Melissa, I guarantee you, has some weird Unicode character somewhere in that name. Uh, we just, I, I just didn’t really do all that much digging on it. So, if you, if you have a need in your database to either, um, a lot of, I see a lot of this stuff with, like, phone numbers or, you know, um, something along, something along those lines.

Uh, and it’s usually pretty good for, these are pretty good for that stuff. Uh, they are inline table valued functions, so they don’t have the same problems that scalar UDFs or multi-statement table valued functions would have. So, if you want to give these a shot and see if they fit your use case, you know, I got get letters.

I got get numbers. And I got strip characters. And they all do, you know, just exactly what they sound like. So, you should try them.

And if you, I don’t know, like them or you find bugs or you find things that can be improved from a performance perspective, well, this is what GitHub is for. We collaborate. We’re a community.

We all high-five each other. No one gets paid. No one gets paid. All right. Cool. Uh, thank you for watching.

I hope you enjoyed yourselves. I hope you’ll try my handy little functions out. Uh, I hope you learned something. Uh, what else? If you like this video, I like thumbs.

I love thumbs. Thumbs are the best. Comments are nice, too. Uh, and I also like subscriptions. Subscribers.

I like when people say, I want to hear from you more often, Erik Darling. Uh, because, you know, that’s how I make friends. I yell at my camera on YouTube.

So, anyway. Like. Subscribe. Hang out. Spend some time with me. Mm. Try some functions out. They’re free.

The first function’s free, kid. All right. Uh, thank you for watching.

Going Further

---

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

Check Constraints And Query Performance In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the world of database constraints and their impact on query performance, particularly focusing on how tweaking these constraints can lead to more efficient execution plans. While most viewers might not have constraints in their databases, I explore a scenario using Stack Overflow data to illustrate why constraints are important for maintaining data integrity and optimizing queries. By adding and modifying constraints, we see firsthand how SQL Server processes queries differently, leading to significant improvements in plan shape and performance. This video is part of my ongoing series on database optimization and query tuning, aimed at helping those who want to improve their skills in these areas. If you found this content amusing or informative, I’d love to hear your thoughts—thumbs up and helpful comments are always welcome!

Full Transcript

Erik Darling here with Darling Data. In today’s video, we’re going to talk about how you can tweak constraints in your database to improve query performance. Now, this video is admittedly a pretty good bit of narrowcasting. I know most of you out there, you do not have constraints in your database. If it’s a data warehouse, I’m with you, though. Like, check constraints, unique constraints, foreign keys, and data warehouses. The domain of morons. Do not mess with your database load times. Your data warehouse load times. And your OLTP databases, OLTT, not a thing. Oh, your OLTP databases, where, you know, referential integrity and things like that are pretty important, you should have constraints like uniqueness and check and foreign key because they’re good for your data. They’re good little data vitamins. You should have your data on those vitamins. They can get big and strong like Fred Flintstone. So, I mean, this is just kind of a funny thing. It amuses me. And if it amuses me, I record it. And hopefully it amuses you, too. That’s why at the end of every video, say, I hope you enjoyed yourselves. Because, oh, crap, I hope it’s funny to someone.

If it’s just me laughing at my own jokes, boy, that would be so depressing. Oh, dear. Oh, dear. I’m spiraling. Spiraling real hard. So, I’ve got my Stack Overflow database, and I’ve already created this index. And this index is on ZoomIt. I’m having real ZoomIt problems. I might have to, like, restart my computer or something because ZoomIt is just being acting a wild. Wild fool on me. This index is keyed on reputation and upvotes, and it includes display name. Now, what I want to point out about this index before we go any further is that this index does, in fact, have a standard, let’s see, is that, well, almost 200 row history. I’m 194 rows. And you can see all of the histogram stuff in here. This is our 194th row. Maybe if we, oh, I mean, we created the index, so that is a full scan.

There is our 194th row. This is our highest value, 1047863. 1047863. Oh, that’s seven digits. That’s John Skeet. And then we have various and assorted reputations up here. And the very first reputation in the table has a range high key of one, right? So SQL Server knows that the lowest range high key that it got from this data is the number one. The thing that this doesn’t guarantee in any way, shape, or form is that the lowest number that will always be in here will always be the number one, right?

SQL Server has no logical guarantees about that because we don’t have any constraints that enforce that. So what we’re going to do next is look at a query plan. And when we’re going to look at this query plan, we’re all going to gasp. We’re going to be shocked and awed and horrified at how disgusting this query plan is. I mean, it runs pretty quickly because let’s, like, look. They don’t all have to be awful. Some of them can just get the point across.

Some of them, you really just… See the pictures up there? There’s a seek and a sort and a parallel gather streams and a top. Sometimes you just need the pictures to tell the story. In a way, you do kind of miss the old days when there were no operator times in query plans because then you could just, like, take a picture of something and be like, trust me, it was awful. But this, you know, keeps you a little bit more honest. So me being honest with you, I just want you to pay attention to the operators in the plan and the plan shape.

Namely, we have a parallel plan. Our plan has a sort in it. Those are the two things that are important here. Now, if you’ve watched my other videos about indexes and indexing and how indexes put SQL Server data in order, you would think that, you know, having a less than or equal to predicate on reputation would make it fairly easy to… For SQL Server to know that, like, it doesn’t have to sort upvotes, right? We order by upvotes here, but we have, like, an equality predicate.

And one is the lowest in the table. So, SQL Server, you should know. But it doesn’t know because we don’t tell it that. We have to over-communicate to databases. You know, they’re very neat. Databases are like big neat balls.

Like, you’ve got to really spend a lot of time coaching and coaxing. It’s a whole thing. So, if we add this constraint, where we say reputation greater than zero, SQL Server knows right now that every single reputation in the users table is greater than zero.

And so, you would think that SQL Server would logically be able to say, when this query runs, well, everything’s greater than zero. So, that really means that the only number that could show up in here is the number one. I mean, yes, theoretically, technically, scientifically, there are numbers between zero and one.

But this is an integer column. But we don’t have that level of precision. But SQL Server, still not very confident. And we end up with the exact same execution plan with the seek and the sort, and it’s all parallel.

It’s very interesting, isn’t it? If we want to fix that, if we want to change that for good, we need to get rid of the constraint as it exists here. And we need to change the constraint. Constraint? Constraint? I don’t know what a constraint is.

I don’t think that’s a word. We need to change our constraint to be reputation is greater than or equal to one. And when we do that, now SQL Server changes its whole mind.

We have a single-threaded index seek with no sort and no need for a parallelism gather stream because there’s no parallelism. So, what’s kind of funny here is if you look at this index seek, this just says seek predicate reputation equals one. So, we have that equality predicate now on reputation.

And we have the SQL Server with that knows that upvotes is all in the order that we want it to be in. So, we don’t have to actually physically sort anything. Upvotes is all in the right order.

If we go back a step, oh, dear, what happened? That was a bit scary. That was like one of those end-of-the-world movies. If we go back a bit and we look at this constraint or look at the query plan with the greater than zero constraints in place, the index seek looks a little bit different.

Right? We still have the seek predicate, but it’s less than or equal to one. So, just having that zero in there, just having greater than zero doesn’t really help us. Having the greater than or equal to one gives us a much tidier query.

So, let’s be honest here. Very few of you have constraints. Very few of you pay this much attention to query plan details and maybe to the way constraints are written.

And how that can be changed to improve query performance. Very few of you would even probably pay attention to that query plan that we just saw. Why?

It runs in 100 milliseconds. Are you going to tune that? I don’t know. Do you need to? Maybe. Could you do it by changing a constraint that you probably don’t have on your table anyway? Yes.

That’s the magic of databases. These are the kind of little things that matter. I’ve said it before in other videos. You know, the further along you get in your database.

I mean, I gear myself towards the query tuners. The further you get along in your query tuning career, the more these small details and these little minutiaes make a difference. So if you want to improve, leave the query performance stuff aside for a little bit.

If you just want to improve yourself and you want to improve your query tuning abilities, these are the kind of things that you’re going to have to start paying attention to. Now, let’s say we’re starting from a place where you have no constraints on this table. That’s the first thing you’re going to want to go after, right?

Because if this is an OLTP environment, not a data warehouse. Data warehouse, constraints, no. Get out. You’re in an OLTP environment and there’s like specific domain rules and regulations around some of the columns in your database. Overcommunicate to SQL Server what can and should be in those columns.

It will help you in the long run. And then further on, when you start to have really insane problems in databases, pay attention to how those constraints are written. Because to SQL Server, which apparently does not trust integers, there’s a big difference between greater than zero and greater than equal to one.

So just stuff to keep in mind. Stuff like this I find all the time. Stuff like this I find and it surprises even me.

It just kind of takes a little bit of like the curiosity to tinker and, you know, toggle with stuff a little bit and try to figure out exactly what’s wrong with SQL Server today. Because it’s always something. Always something wrong with SQL Server.

It’s sort of like a Munchausen type thing. I’m not a doctor, but it sounds like a reasonable diagnosis. Anyway.

Yeah. One, get some constraints. Two, write your constraints right. Important lessons there.

Right? For you. Thank you for watching. I hope you enjoyed yourselves. I found this particular scenario funny. I hope you also found it funny.

If you didn’t, well, I don’t know. Maybe the next one will be better. I can’t make any promises, though.

If you like this video, thumbs ups and helpful, not hurtful comments are always appreciated. And, of course, if you like this sort of SQL Server content generally, and you would like to get notified whenever I talk about things that amuse me, well, you can subscribe to the channel, and you can get the notification along with, like, 4,000 other people.

And like we’ve said before, 4,000 people have never been wrong. Never once. All right.

Thank you for watching. I need to go do something else for a moment. What it is, you’ll never know. You can only guess. You can only dream. You can only speculate. You can only be conjecture.

But I’ll know. I’ll know.

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.