Tag: Query Tuning

A Little About Why I Don’t Like The Read Committed Isolation Level

Video Summary

In this video, I delve into the reasons why I believe that the read committed isolation level is not ideal for most SQL Server databases and advocate for using an optimistic isolation level instead. Starting with a simple example in my Stack Overflow database, I demonstrate how read committed can lead to inconsistent results due to its lack of a consistent view of data. By switching to read committed snapshot isolation, we achieve consistent query results every time. Additionally, I explore the blocking and deadlock issues that can arise when using read committed, especially with concurrent read and write queries. Through an example stored procedure, I illustrate how these queries can block each other, leading to deadlocks. The video concludes by recommending read committed snapshot isolation as a better default setting for all databases, offering practical advice on how to implement it effectively.

Full Transcript

Erik Darling here with Darling Data, fresh off the heels of a great weekend. And I’m going to duck down a little bit so that you can read the full screen here. It says, why I think that read committed… I messed up my green screen a little bit. That’s how fragile these things are. Why I think read committed is not a very good isolation level, and why I think you should be using an optimistic isolation level instead. So, we’re going to start off with a pretty simple case, where you can get strangely incorrect results. Now, the problem that the main three problems that I have with read committed, the default isolation level for all the sorry souls, except the sorry souls who have to use Azure SQL DB. The main problem with it is that it doesn’t give you a consistent view of the data. So, if you have a scan that’s moving along a table, and some modification comes along and updates something over here, that row can move to this side of the scan and the scan will completely miss it.

Likewise, you can have something that the scan reads here, and then after the scan reads it and releases its locks because reads don’t hold on to locks for anything. That’s why you can’t have lock escalation with the read. So, if you have a scan read a row here, and then an update says, oh, I need to change something with this row, and that row moves over here, you can read that row twice. So, there’s not good side effects from not having a consistent view of the data. This isn’t to say that you should haul off and use repeatable read or serializable, but it is to say that there are better options out there in the world, read committed snapshot isolation and snapshot isolation being the two of them.

I do have a slight preference for snapshot isolation, but as far as ease of use and getting queries to be agreeable, read committed snapshot isolation is the easier choice there. So, we’re going to create, oh, actually, you know what, we’re going to go back and we’re going to use the right database first. I just realized we’re in the wrong one for me to show you what happens when things get weird.

Now I have these two tables sitting around in my stack overflow database, look very unprofessional. So, I have this query set up to use this read committed lock hint because I already have read committed snapshot turned on for the crap database. That is already done, that is already in there.

And if we run this query and we look at the results, we’re going to see 110 for all of them. That’s because there is no other concurrent activity going on in the database. And if I take this tiny little query and I put this in a different window, this is just going to update things in a loop forever and ever so I can keep running this query and showing you how goofy things are.

So, I’m going to kick this off and we’re going to come back over here and we’re going to look at what happens when I start running this. So, just on the first execution there, you can see that not all these rows have the same value in them. All right, these all changed.

All right, that one’s 10, this one’s 30, 50, 70, 90, 410, 430. So, and if we keep running this, we’ll see that consistent inconsistency every time this executes. All right.

And if I just quote out this read committed lock hint and I allow my query to use the read committed snapshot. Oops. I allow the query to use the read committed snapshot isolation level.

We will get consistent results on every execution. All right. So, every time you run this, the numbers are the same down the line. We don’t get inconsistent results from our select query.

So, we don’t get weird results moving up and down, which is good. What we want is consistency in our data. All right.

So, that’s the first reason. The second and third reasons are things that I have talked about a few times before. Let me just fix that up a little bit.

Sorry about that, folks out there. Like I said, it’s an early morning for me and my third tiny cup of espresso is still brimming. So, please, please do forgive me. It’s a little bit like being jet lagged.

Except it’s just the entire weekend catching up with you at once. So, that’s that one. The other problems that I have with the read committed isolation level are because of two things that I run into when helping clients with deadlocks and blocking problems quite a bit. And that is when you have read queries, read queries and write queries executing concurrently in the same database where you’re using read committed as the default isolation level.

They can block and deadlock with each other. This is something that not a lot of people realize. This is also something that not a lot of people…

Well, I’m not going to say it. Many people have avoided these scenarios by slathering no-lock hints all over their queries. Even their modification queries.

Just update with no-lock. Okay. That’s a joke. But when you use no-lock, you don’t run into the same necessarily locking problems that you do when you use the default read committed isolation level. This isn’t an argument in favor of no-lock.

This is me making fun of people who use no-lock. Ha ha ha ha. You fools. And this is me saying that you’re better off using an optimistic isolation level where you won’t run into this sort of thing. So the first thing I’m going to show you is this.

Nice. So we have a store procedure here. And we are going to intentionally parameter sniff our store procedure. The first execution we’re going to do of it is going to be for a very small date range.

And let me turn on query plans. See, this one finishes quickly. Right here.

Look how fast this one finishes. All right. Some seven milliseconds or something. Pretty good. Query tuned to perfection. Except now we are going to get parameter sniffed to perfection.

And I just want to point out really quickly that this is SQL Server 2022 running in the 160 compatibility level. But the parameter sensitive plan optimization doesn’t kick in because our only predicate that involves a parameter uses an inequality predicate for the search. So the parameter sensitive plan optimization is only available for a quality predicate.

So we don’t get any help there. So one thing that I want to do is grab this and stick that in this window. And we have to go back over to Stack Overflow.

And I’m going to kick off a very slow version of this store procedure. So doing this for, I think, like a day, let’s call it. This is like the final day of data that exists in the Stack Overflow 2013 database.

If we do it for this date, we don’t find a lot of rows. If we do it for this date, I mean, technically we only need to go back to like 2007 or something. But this is just funny to me.

And it’s also a nice piece of SQL trivia. If you ever want to know why the low value for some date types in SQL Server is 1753.0101, you can search for the name Philip Stanhope. And you can find out way more than you ever wanted to know about calendars.

So I’m going to kick this off. And it’s going to run terribly slowly because it’s going to find a lot more data. And I’m going to run that update query. And we’re going to go look at SP who is active.

And we’re going to see that our select query is blocking our modification query. Here’s our select running happily. And here’s our update sitting around waiting to get these intent exclusive locks so it can do its updating.

This happens because of a sort of funny thing with the query plan. And this thing is done now, so I can show you that. And if you look at the execution plan, you’re going to see that this thing did run for 15 seconds.

And the reason why this blocked the modification query is this portion of the plan. We have an index seek. We have a nested loops join.

And then we have a lookup back to our clustered index. I recorded a video pretty recently about key lookups. So if you’re really unfamiliar with what those are, you’ll want to go watch that video. I promise it’s not hard to find.

It’s the one where I look really, really cute in an Adidas t-shirt. Anyway, let’s get back to business. So what happens is our modification query wants to update the votes table.

And it needs to update, at minimum, the clustered index so that we can change data there and then, you know, push those changes out to any other nonclustered indexes on the table that have the column in it that is being updated. So we have an index seek where we find the rows that we care about here. Every time we find a row that we care about here, we pass it to this nested loops join.

And that nested loops join goes and looks in the clustered index for those rows. Now, remember when I talked about the read committed isolation level not providing a consistent snapshot of the data? So you could miss or see duplicates of rows, right?

So what happens here is SQL Server does some locking magic in order to prevent those inconsistent phenomena or phenomenon, depending on how you speak English. I speak it incorrectly, so I’m actually not sure which one to use there. And what it does is it uses an object level shared lock to lock down the clustered index so that no data can change in that clustered index while it’s trying to locate rows that it’s finding from the index seek.

All right, so every row that comes out, we go look. And the entire time that’s happening, the clustered index has an object level shared lock on it, which is preventing those intent exclusive locks from the update from coming in. Cool.

We got that. So that’s how read queries can block write queries in the read committed isolation level. Another thing that can happen is we can run into a situation for absolutely similar reasons where read queries and write queries can deadlock with each other for sort of similar reasons. Now, I have a select query that runs in a loop and just looks for anything with the vote type ID of 8 in the votes table.

And what I’m going to do as soon as that index is done is, well, I can do this before the index is done. I’m going to stick this into this window here. This window is getting a lot of reuse, right?

Doing a good job recycling things here. So we have this update query that is going to just flip the vote type ID column back and forth for a single row in the votes table. Just one row.

All it’s doing is changing vote type ID from 8 to whatever. And because our select query has a semi-covering index, it will have a key lookup in that plan and we’ll end up with the same object level shared lock issue that we saw before. Now, sometimes we end up with a little bit of getting lucky with the timing.

So I got to kick that off and kick that off. Oops, I didn’t select the whole thing. And I probably have to go back over here and rerun this now.

There we go. All right. So while this update is executing, we ran the select query and the select query ended up in a deadlocked state with the other query. All right.

So that update query and this select query have deadlocked with each other and we have, well, our select query has been murdered, essentially. That happens again because there’s a key lookup in the select query, the object level shared lock, and the locks that the modification query are trying to take are fighting with each other. And then, like, we have the nonclustered index and it needs to update that and read from that.

And this one’s trying to select this and lock this. And just because of the way that those things sort of do their little murder dance, this select query ends up being the victim of a deadlock. Usually, SQL Server will try to choose who should be the deadlock victim by looking at which transaction would be the easiest to roll back.

And typically, select queries are the easiest to roll back because there’s no transaction logging to deal with. So this is obviously not ideal. Again, you know, a certain level of no lock hinting would prevent these issues from happening.

And also, sort of interestingly, there are other things that would fix it. You know, we could use an optimistic isolation level, again, read committed. If we had a covering index so that we didn’t have to do those lookups, then we could avoid that situation as well.

The slight danger here is that sometimes, you know, these select queries are doing, you know, select star or the equivalent of select star. It would be really onerous to create very wide indexes to solve all those scenarios when there are typically better options that fix it without you having to, you know, adjust many indexes, add lots of columns to them as include, stuff like that. There’s also some other things that we could do.

If we wrote the query and, you know, sort of like wrote our own sort of lookup and we used a, like we did a self join or used a hash or merge join hint, then we would be able to avoid that scenario because it’s really the lookup in there that causes the problem. Anyway, that’s a little bit about the read committed isolation level, why I sort of don’t like it very much, why I think read committed snapshot isolation is a better isolation level. It should be the default, it should be your default for all your databases.

If it’s not, just flip the switch, it’s the worst that happens. Everyone loves you, just like they love me. Anyway, if you turn on read committed snapshot isolation today, I will send you an Adidas t-shirt.

Promise. It’s only good for May the 1st of 2023. So I don’t want to get an email in like a year saying I did it and I want my Adidas t-shirt because the offer has an expiration date.

Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. Remember, like and subscribe and all that stuff.

I do appreciate likes and subscribes. Views are also nice, but, you know, I want to make sure you keep viewing. So there’s that.

Anyway, I’m going to can this one, upload it, and start talking about something else because I have agreed to go roller skating today. And this might be the final piece of work that I do for SQL Server because I don’t have a lot of confidence in me on wheels. That’s why I don’t have a driver’s license either.

Anyway, time to go. 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.

A Little About Trivial Plans In SQL Server

Video Summary

In this video, I dive into the fascinating world of trivial execution plans in SQL Server and how they can sometimes lead to unexpected query behaviors. I explore why certain queries might be considered “trivial” by the optimizer and what that means for performance tuning. By walking through specific examples, I demonstrate how even simple changes in query structure or indexing can significantly impact whether a plan is marked as trivial or fully optimized, revealing the intricate decision-making process behind SQL Server’s cost-based optimization.

Full Transcript

Erik Darling here with Darling Data. Here bright and early on what I presume is a Friday morning, but could be temporally inaccurate depending on how much you actually think time is real. Might not be. Might not be. I’ve heard various thoughts on the subject recently that really made me question the whole of existence. Alright, so I’m going to switch things up. a little bit. I’ve been recording like 10 videos recently about stored procedures that I write, how to use them, how to get information from them, stuff like that. And I wanted to do a few more videos just purely about SQL Server interestingness. Things that, I mean, I’ve generally have found interesting over the course of my long and storied career with SQL Server. And things that I like sharing with people. This is a material that I do during, you know, conferences and stuff like that. But, oh, you know, just trying to democratize things a little bit, make sure, make sure everyone can see it. You know, it shouldn’t just be, no, it shouldn’t just be limited to people who can take a week off work and spend five grand showing up somewhere. I don’t know. I guess I’m like Jesus in that way. Leave any further jokes about that alone. Alright, so let’s actually read the script.

Let’s turn reading comprehension up to 11. Read the script and turn on query plans here. Oh, this video is going to be about trivial execution plans. Trivial execution plans are a thing that have been not solely, but certainly partially responsible for a lot of strange things that I’ve seen over the course of my query tuning life. And not only query plans, but sort of by extension, simple parameterization, which can occur when a plan is marked as trivial. So first I’m going to show you exactly where you can locate information about a plan being trivialized by the optimizer. This is sort of like the optimizer just saying, yes, dear. I don’t, I don’t, I don’t really have time. This is, you know, agreeing to agree. Yes, I’ve got your query. It’s very important. Super important. I just, yeah, we’re going to take care of that.

So if we look at the properties of this right here, floating above my head is the important part. We’re going to try and move this thing around a little bit so that we can get to where it should be. Look at that. Oh, beautiful. All right. It’s going to put that right next to me. There we go. So this plan’s optimization level was trivial, not full, just trivial. Just, we don’t really have any choices to make. SQL Server is driven by a cost-based optimizer. And if there are no cost-based choices to make, like which index to choose, which operators would be best to use in the query plan, right?

There’s a whole bunch of different operators that are possible of, they’re capable of doing the same job. And sometimes SQL Server has to think about how much each one would cost and say, oh, well, I choose any, me, me, me, you’re the cheapest. There are no, there are no choices like that for this execution plan. This, it’s the user’s table. All it has is the clustered primary key on there. And because that is the only source of data and because we are only asking for 100 rows unordered, all SQL Server has to do is say, okay, give me those first 100 rows.

You can do some pretty funny looking things that are still considered to be trivial. Now, if we look at this query, one might think that generating a row number would make SQL Server have to think long and hard about the best way to generate that row number. But it doesn’t because in this case, at least, and let’s just make this super clear here, do some formatting and live formatting. Make sure our query is nice and pretty for the camera.

In this case, we are partitioning by and ordering by, in all cases, the ID column. And the ID column is the clustered primary key of the user’s table. So, it is unique.

So, partitioning by this will not really do a whole lot. Ordering by it is free because it is the leading key column of an index. Right?

So, like, just without a where clause or anything else to pollute this thing, we have everything that we want in order in both of these cases. So, if we run this query, and again, we come back to the execution plan, even though it looks like SQL Server, well, I mean, it doesn’t just look like SQL Server, even though there are a couple more operators between here, there was no cost-based choice in generating these operators.

This is how row numbers and many other windowing functions are produced in row mode. In batch mode, you might see a window aggregate. Sometimes you might even see, well, not in batch, but in general, you may also see a window spool for some windowing, windowed, windowing, window, whatever, some windowing functions.

And so, these two operators are responsible for generating that row number, but since we have an index that completely supports the partitioning and ordering of our windowing function, SQL Server has no cost-based choice to make when it comes down to this.

Likewise, since we have a clustered primary key in the table, and that’s going to be part of the select star here, you can actually see it right there up, oh, it disappeared on me, you bum.

There we go. And I don’t know why, but sometimes zoom in is a little bit laggy when I’m recording. It’s kind of strange, but whatever. Can’t have everything, so they say.

But since the ID column is our primary key, if we had another column that was marked in unique some way, like a unique nonclustered index, constraint, something like that, we might see something similar happen.

But if we select the distinct top 1,000 everything from the users table, because we have that ID column in there, SQL Server has a pretty good guarantee that absolutely everything will be unique because of that index, and we don’t have to do any work to try and produce a unique result set.

And so if we do a little flip here, and this is something that annoys me quite a bit about SSMS, is that this context is just not this context, and I can’t click on this context to get this context.

I have to click over here to get this context, and then I have to click back to get this context. But in case my beautiful hair, which is long nearly to the point of a pompadour at this point, I’m getting a haircut in a couple of days, don’t worry.

If we look over here, and let’s put that right next to me again, because I want to be the star of the show. Trivial Eric, they call me. In the pursuit of all things trivial.

So this is, oh, wrong hand. So this is, again, a trivial plan. We didn’t have to do any work to find a plan for that. Where this changes is when SQL Server has choices to make.

So I’m going to run all three of these queries in a row. And while those run, which, I mean, they’re already done, but I just want to explain a little bit up front.

And the reason that I’m forcing compat level 140 here and here is to avoid the automatic batch mode on rowstore feature from kicking in. I have that in developer edition.

So lucky me. And the reason why I’m using this hint down here is because I want to show you what a stream aggregate plan would look like for this query. Because these are the three choices that SQL Server gets to make when it runs this query.

After this, I’ll show you what happens with batch mode on rowstore. So if we look at these execution plans, the first execution plan chooses a hash match flow distinct.

Why did it choose that? Good question. Because the top that we chose completely met SQL Server’s cardinality estimate for how many unique rows we might get out of here.

This next one, or we were asking for a number of rows less than or equal to the number of rows that SQL Server thinks will be distinct in the table is the more correct way of putting it. Every time I say something, I picture people tutting at me for saying it incorrectly.

So I do try to correct myself when I catch myself. Since this query is asking for more than the cardinality estimate of the grouped or the distinct tuples in this index, SQL Server chooses a full-on hash match here.

See the little difference with the flow distinct and the aggregate here. Now, good job us. If we look down at the bottom query where I told SQL Server, I really, really want you to use a stream aggregate, this order group hint will do that.

SQL Server had to sort all that data because stream aggregates expect data to show up in order. So we had to sort the whole table, do a stream aggregate, do more parallelism, do some more stream aggregating, and then produce those rows.

So we had sort of a double aggregate on that one. It’s a bit strange, but we got the result back. But this was a cost.

So like this is the stuff that the optimizer thought about while it was coming up with execution plans. If we just compare the cost of the top 5,000 plan right above it without the flow distinct choice being in there, we can see the estimated subtree cost for this query is 47.5-ish query bucks.

And we can see the estimated subtree cost for this query was 94.7 some odd query bucks. Not that I care so much about how much a query costs.

This is just how SQL Server chose the plan it was going to use. If you’re the kind of person who cares about query costs, you should really stop.

They are not good things to measure a query by. So if we had an index on reputation that put reputation in order, then all of this stream aggregating would have been a whole lot cheaper to the optimizer because we wouldn’t have had to sort the data.

It would have been sorted for us in the index. Now I know I promised you, I’d show you what happens and why I have this hint in here. So just to remind you, if I run this query, we get the hash match flow distinct operator up there.

But if I run that query without the hint, then I get a different query plan. I get a hash match aggregate right there, right?

That’s the full hash match aggregate. The real reason that I get that is because SQL Server has chosen to execute this operator using batch mode. So if we highlight that, this happens in batch mode.

Batch mode on rowstore kicks in, thinks that this query is going to be, is heuristically expensive enough to require batch mode to be quick. And so it kicks in and we get it. And does it make a huge difference?

No, not for that, not for that query. Makes a big difference in some other queries. This one, not so much. So, there are some perhaps obvious things that will get you full optimization.

In this case, we’re going to try, we’re going to, if we order by a column that has nothing to do with any index currently on the user’s table, if we ask SQL Server to give us the top one row ordered by account ID, this will be a fully optimized plan.

The reason that we can tell it’s a fully optimized plan without even digging into the details of it is that it’s parallel. If a query’s cost to the optimizer is greater than the cost threshold for parallelism setting, the plan will not be trivial.

There are a whole bunch of heuristics and other things that kick in. You know, SQL Server looking at like the logical trees and stuff for a query to figure out like an initial cost so it can figure out some other stuff.

It’s a little bit beyond the scope of what I can cover in this video, but it’s, it’s definitely, there’s definitely a lot going on in there. A lot of math. I don’t know math. There are also some not very obvious things that might get a full optimization.

So let’s just run these two queries. They’re both logically identical. The only difference that we have is this little set of parentheses around a select for the ID column here.

But this little nudge is apparently just enough to make SQL Server think real long and hard about how it wants to run this query. So if we look at the details of this query plan, this optimization level is trivial there.

And if we look at the details of this query plan, this optimization level, which I believe is the first time we’ve seen, oh, actually no, because it was in, uh, it was in those distinct queries.

This optimization level will be full. So SQL Server had a, um, a cost-based decision to make apparently, like maybe where to put the compute scale are.

I don’t know. Um, couldn’t tell you on that one. So let’s try an even simpler subquery here. Let’s just, uh, let’s run these two queries.

We’re just getting a count. And then this count where one equals select one, one will always equal select one, but SQL Server is kind of, I don’t know, uh, kind of picky about these things.

And if it’ll let me grab it, no, that just moved that query plan. There we go. All right. So if we look at these two query plans, they are absolute, well, they’re not absolutely identical.

Um, this is another one of those funny things with batch mode on rowstore. Uh, and, uh, and prior presentations of this, where I wasn’t using, uh, the, the newer compat levels, uh, these plans would be exactly the same.

If I, if I set this back to, uh, the, what do you call it there? The one 40 compat level, we would not get batch mode here, but, um, this is, uh, perhaps, a cost-based decision that is of some, uh, interest to you because, uh, one thing that cost-based optimization will do is figure out if batch mode on rowstore would be a good choice for your query.

Uh, in this query, we do not get that, uh, this query, if we look at the details over here, you can probably see it right over my head.

It is trivial. Uh, and this query down here, if we look, if I duck down a little bit, it has full optimization. So, cost-based optimization hides a lot of stuff, right?

Like, a lot of stuff is just, like, on the other side of that door. If you get a trivial plan, you don’t go through that door, you just, you know, get whatever gets handed out that door. Uh, there’s a lot of cool stuff behind it. It’s like the, the backstage area, right?

All the good, that’s where all the good stuff happens. So, the brown M&Ms and pharmaceutical grade cocaine are. So, one other thing, and this is, this is going to lead into a minor tangent, uh, is if we run these two queries, and we look at the query plans for them, whenever my, RDP decides to become responsive, maybe my RDP needs some pharmaceutical grade cocaine.

Uh, so if we look at these two queries, this one up here, scans the clustered index, this one down here, scans the clustered index, but only the query that will get full optimization decides that an index might be useful.

Now, I bring this up, uh, because I want to show you other things that are on the other side of, in the backstage of SQL servers, I’m trying to, the full optimization party, uh, and SQL servers optimizer, but also because, uh, I, I, I need y’all to chill with adding every missing index request that you see, or that you might find via scripting, because a lot of the times, uh, they are not quite, uh, go, they’re not quite going to do as much, uh, good for your query as they, you might think they’re going to do.

You might see this show up in front of you someday. You might say, wow, this thing has a cost of 36 query bucks. That sure sounds pricey. Uh, and you might look at this and say, wow, I can make this query cost 99.2491% less if I had this index.

The thing is, uh, do we really need to improve a 288 millisecond query? Is that really important to you?

Is that worth it? I don’t think so. Don’t believe so. But anyway, back to, uh, why, and this is something that I, I, I, I foreshadowed ominously earlier in the video, back to sort of why this query, uh, does not have a missing index request.

Is, part of, part of it is the, uh, simple parametization. Part of it is the trivial plan. And part of it is, uh, that SQL Server just didn’t care all that much about our query.

It did not, did not break, not, not, did not get full optimization, not break any boundaries.

So when trivial plans, uh, can be a real letdown, is when we have a constraint like this, where we are telling SQL Server, exactly the upper and lower boundaries of the reputation column.

And, uh, this constraint is trusted, right? If we look at, if we look at the, this, this data, right?

This is our, this is our index. This is our constraint rather. And we can see that this can, this constraint is trusted because there’s a zero for is not trusted. If it were one, then it wouldn’t be trusted.

But zero for is not trusted means it is trusted. All right. Cool. So if we run this query and we look at what happens, and, uh, I got ahead of myself a little bit when I was talking about the last thing, because it’s really more pertinent to when I talk about this thing, uh, is the reason why, uh, this query, despite knowing the upper and lower boundaries of, uh, the, the reputation column, and despite us looking for a value in the reputation column that, uh, falls within, falls, uh, out of those boundaries and would not possibly exist in the table.

Because SQL Server gives this thing a trivial plan. And because SQL Server chooses to, uh, simple, simple parameterize this plan, the simple parameter plan has to be safe for any other, uh, value that gets plopped into, uh, the where clause and gets also simple parameterized.

We could search for a value that’s within the boundary and we would have to scan the clustered index. So this is one of those funny things about, you know, like, you know, all the moving parts in SQL Server that can certainly make things, uh, tough to, um, tough to deal with a little bit.

But if we, so actually one thing I want to look at real quick, we should go over here and we should actually validate that SQL Server read every single row, uh, in the, in the user’s table when it did that work.

And it did, it read all of them. Good for us. We figured it out, cracked the case. I’m going to make, make detective sergeant major, whatever.

I don’t, I’m not good at ranks like that, but, uh, this query, if we look at this, if we run this query and we get it to full optimization, this query will, will bail out, uh, because all we get, uh, we can see the full optimization again, floating over my head right there.

Full. Uh, the reason why we get a constant scan rather than a clustered index scan is because rather than, um, being simple parameterized and SQL Server having to come up with a plan that’s safe for any other value that might go in there. Uh, we have a literal value here and SQL Server is able to evaluate that literal value, uh, not have to come up with a plan that’s safe to reuse for that literal value and say, no, we don’t have any rows for that.

Thanks for asking though. So anyway, uh, trivial plans are kind of weird. Um, you know, I don’t, I don’t like them very much.

In, in a lot of cases, uh, I find that they pop up at very inopportune times. I find that they are not, um, they are used when they shouldn’t be.

They are misused, uh, quite a bit. And, um, the only thing that you can do is, is look at your query plans. Uh, look at the, uh, look at the, uh, the operator properties of the root operator.

See if your plan is getting full or trivial, uh, optimization and then, uh, work from there. Uh, the one equals select one thing is a really cheap and easy way out.

You know, I’ve, and if you are able to modify any queries that, um, that, uh, that are not running well because of the, the trivial optimization level, then we might, you might want to do that.

Anyway, uh, I’ve been talking long enough. I got stuff to do. Uh, I’m going to try to upload this video. This is my longest video yet. I’m surprised the camera is still, still operating.

Uh, and, uh, yeah, uh, I’m going to, I’ll record another one in a little, in a little bit. I got, I got some, I got some actual work to do after this. Anyway, thank you for watching. I got some quite strong Раз soloing rhythm. I got some good heart.

Going Further

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

Why You Probably Don’t Want ISNULL Or COALESCE In Your SQL Server Queries

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 Parameter Sensitivity In SQL Server

Video Summary

In this video, I delve into two key SQL Server scenarios that highlight the importance of avoiding overly clever solutions and understanding parameter-sensitive plan optimization in SQL Server 2022. First, we examine a situation where attempting to outsmart the optimizer can lead to suboptimal performance. Specifically, I discuss how using bit parameters in stored procedures can backfire, causing SQL Server to generate plans that are not as efficient as they could be. This leads us into an exploration of why parameter-sensitive plan optimization doesn’t always come to the rescue, especially when dealing with startup expression predicates that lack histogram support. By rewriting a procedure using dynamic SQL, I demonstrate how making life harder for yourself can actually simplify things for SQL Server, resulting in more efficient query plans. Throughout the video, I also share some light-hearted moments, like my new microphone pop filter, to keep things engaging and relatable.

Full Transcript

Erik Darling here, Darling Data, and recording a rather quick one to talk about, well, a couple things. One is a situation where you shouldn’t get cute with the optimizer. And two is a situation where SQL Server 2022’s parameter-sensitive plan optimization will not come to your rescue. Now, there’s a good reason for that, and we’ll talk about it. Now, if you’re a loyal subscriber to my channel, you’ll notice something brand new in today’s video. This little pop filter right here. Yeah? Apparently, this little microphone came with a set of three in case you get hungry and want to nibble because it’s right near your mouth. And I just sort of forgot about them. So now, here’s wishing you a pop-free experience listening to me drone on about SQL Server. Sorry. So we’re going to start off with the indexes. We got two of them. One called BudLight and one called CoorsLight. This is not a taste great, less filling situation. This is just two indexes that make good company. So, let’s focus in on the right window. That’s a good start to the video, right?

So we got these two indices here. By the way, if anyone says indices, I will punch you in and sue you at the same time. One of them is on the post table. And it’s got owner, user ID, and score in the key of the index. No included columns here. And the other one is on the comments table, which has user ID and score. Now, it’s a little strange to have in the stack overflow schema in the post table. It’s called owner user ID. And in the comments table and the votes table, it’s user ID. But we’re actually in the badges table, too. So that’s a little weird. But we’re just going to run with it for now. Owner user ID. Now, those two indexes are here to help our store procedure. So I’m going to talk through how they help and why they could be more helpful. And then kind of walk through what else the store procedure does.

So what we have are two potential left joins. I say potential because you’ll notice it is these things here, which we’re going to come back to. But the indexes are here to try and help us make this faster. Now, is this perfect? No. In a perfect world, I might put score first, depending. You know, the score column is not terribly selective. So range predicates can still acquire vast swaths of data.

So maybe not maybe not the greatest idea there. But, you know, one of those things you kind of have to A, B test a little bit, see where you see where it gets you. Now, coming back to the store procedure itself, we have two bit parameters. We don’t mean that they are two bit parameters. I mean, we have two parameters that are bits.

One of them is called check posts. The other one is called check comments. Now, what these do is direct SQL Server to maybe do something or maybe not do something when we get to these joins. All right. So having these parameters in here, well, they’re just like any other parameter.

They are sniffable. And just like any other parameter, SQL Server caches a plan for the first compiled value and then reuses that plan. But we can tell SQL Server whether to actually do a join with parameters like this.

So I’m going to run this. And the first iteration is going to be to check the post table. And then the second one is going to be to check the comments table.

Now, on the first run, I have query plans turned on. I don’t have to worry about that. This runs pretty quickly.

All right. 640 milliseconds. Okie dokie. No, it’s OK. It’s not great. It’s OK. But this is one of those things I see developers do quite a bit.

They try to get cute with SQL Server’s cost-based optimizer. And what happens is that they sort of end up falling flat on their faces. It’s going to come back to my copyrighted adage about SQL Server.

And that is, anything that you do that makes your life easier makes SQL Server’s life harder. And this is one of those things. So let’s slide this plans DMs over just a little bit.

So I don’t lose my finger in a tragic green screen accident. And let’s first look at this part of the query plan. And let’s nuzzle that.

Ah, it’s in my ear. Let’s nuzzle that right here. We’ve got an index scan that doesn’t actually do anything. And we’ve got a filter operator right after that. Now, usually, when you see a filter operator after some sort of index or table access, it means you did something real dumb.

In this case, SQL Server did something pretty smart. So let’s look at the details of the filter. We’ve got something called a startup expression predicate.

Meaning this filter, rather than acting as a filter after the fact, it acts as a filter before the fact. So this filter actually prevents touching that table because the startup expression predicate, right, to like move past the filter is not met, right?

So we did not check the comments table in this one. For the post table, it’s a different story, of course, because we’ve got, believe it or not, another startup expression predicate.

But this one is met, so we do actually touch the post table, right? So la-di-da there. Now, what happens if we run this in reverse?

Let’s execute this. Or not in reverse. Let’s just run this one next. That’s sensible, right? All of a sudden, this takes a little bit longer.

The last one took about 640 milliseconds. This one takes nearly four seconds to finish. 3.8 seconds as the crow flies, as they say in my country. And if we look back way…

No, not you, tooltip. Not you either. Go away. You’re not my friend. Let’s slide this back over. And you know what? I think that’s probably good enough.

Now, this time, the startup expression predicate in the filter operator is met. But guess what? We retained the cardinality estimation from the first query, right?

SQL Server is still guessing that only one row is going to come out of there, but we actually get like 4 million rows. 401, 647.

That’s a seven-digit number. Seven numbers means millions. So, the reason why the parameter-sensitive plan optimization does not work here is because the mechanic behind that is when you run a query that’s parameterized and SQL Server looks at the histogram for various equality predicates and decides that one of them has skewed data in it.

It will mark the query plan. It will give it… It will get that multiple plan operator. And SQL Server will decide to give you up to three variations of an execution plan for your query.

There’s like a small, medium, and large. There’s no extra small or extra large. I’m not sure what that says about Microsoft. It’s not very welcoming. You could use some more plan variations, maybe.

Anyway, the reason why this doesn’t work is because there is no histogram for this startup expression predicate. It is either yes or it is no.

There is no bucketized values that this thing could possibly work off of to figure out when you need different execution plans. Maybe it could in the future because this is a, you know, I think this is an addressable case with the parameter-sensitive plan optimization.

I can understand why it’s not in V1, but I do think this is something that could be addressed by it just because it is a, I think, common enough issue and it is a probably simple enough issue.

But what do I know? I’m just a bouncer. So obviously, that didn’t work out so well. Now, coming back to my copyrighted adage about SQL Server, anything that you do that makes your life easier, makes SQL Server’s job harder, we took the easy way out.

And now SQL Server’s job is harder because it has to come up with one execution plan that’s fast for both being true or either one being true. Now, I rewrote the store procedure in a way that made my life harder, but makes SQL Server’s life easier.

And I used dynamic SQL. So just a fair warning here. I did not write this to take care of the both use cases just because we’re not testing that.

So, and for the sake of brevity, I am only writing this to address one or the other, not both combined.

Now, because of that, I had to change the queries a little bit. I can’t do the isNull on p.creationDate and c.creationDate because we have no p and no c depending on which one of these executes.

So we cannot bind a value that does not exist. All right, because up here in the original query, we’re doing this. All right, and I just chose sysUTc date time because, I don’t know, it just popped up.

Honestly, it was autocomplete. Thanks, Redgate. Just got to work that way. Anyway, let’s change our procedure to use this lovely dynamic SQL that we have done all the right things for.

And, you know, it is a bit longer. All right, because now we have to check if check comment equals one. We could do this a little bit differently, I suppose.

We could make the string itself more dynamic and like throw a case expression in here. So if like check post equals one, then we’ll insert this. And if check comment equals one, then we’ll insert this.

But no, I just decided to do this because I didn’t feel like doing it. I decided to make my life a little bit easier in this case. So now let’s execute this first one and look for check posts.

And this returns again pretty quickly. It’s actually got about 50 milliseconds faster, just not dealing with all the other stuff, right?

Not dealing with the maybes of the check comments thing. And that’s cool with me. I’m into that.

I mean, I’m not going to brag to anyone about making something 50 milliseconds faster unless it was running for 51 milliseconds. But it did improve slightly. Not a very dramatic narrative there.

But now if we run this second one where we only check comments, it will be just about as fast at 610 milliseconds. But it was much, much faster than before when it took four seconds to run because we had all of these startup expression stuff in the parameters sniffed startup expressions and the sort of poor estimates that come from that.

Anyway, I have to get out of here and go do some actual work. Thanks for watching. Hope you enjoyed yourselves. Hope you learned something.

And I will see you in some video in the future. If you’re the type of person who celebrates Fridays and you’re watching this on a Friday, then happy Friday or happy Friday if you’re from a part of the country that pronounces it Friday or part of the world.

Actually, probably nowhere else in the world is going to say Friday. That’s an American thing. I don’t know. If you’re from somewhere outside of America and you call it Friday, well, you can leave a comment for me.

I’d love to hear from you. Anyway, smell you later. Stink bombs.

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 Common Table Expressions In SQL Server

Video Summary

In this video, I dive into the world of Common Table Expressions (CTEs) in SQL Server and share my perspective on why they often don’t live up to their hype. I explain that while CTEs can be useful for certain complex queries, such as cascading filters or generating row numbers, they are not a silver bullet for improving query readability or performance. Instead, I emphasize the importance of proper formatting and logical query processing in making your SQL code more readable. The video covers various scenarios, including how CTEs interact with indexes, top syntax, and multiple references within queries, ultimately concluding that while CTEs can be handy in specific situations, they should not be relied upon for significant performance gains or to make queries magically easier to read.

Full Transcript

E-Dot Darling here, it’s my cool new rap name, with Darling Data. And today we are going to talk about CTE in SQL Server. The reason we’re going to talk about CTE in SQL Server is because I am absolutely sick of CTE in SQL Server. They don’t make queries more readable. Formatting does. Formatting makes queries very readable. especially if you don’t put leading commas in your queries. And they don’t have any magical performance, impact, benefit. They are not good, great, wonderful, spectacular, superb, extraordinary. They are none of these things. There is not a… they’re just exhausting at this point. They… they… they… they tire me. If you ever wonder why I look or sound so tired, you can probably blame CTE. All right. So let’s run this query and let’s get a query plan just to set a baseline, right? So we get the estimated plan for this. And now this is what I would call a fairly typical user query where we access one index on a table. You may have seen other videos of mine recently where we talked about how multiple indexes on a single table can be used and can be used all together.

to make a query happen. All right. You can have key lookups. You can have index intersection. You can have index union. And all of those things can use multiple indexes on the same table to make… make… make it a query happen. But in this case, we’re just touching one index to get our data for this query. All right. Now, where CTE don’t completely befuddle, baffle, and… what’s another good B word? I can’t think of one. If you think of one, and leave one in the comments, where I don’t hate them is when you sort of like cascade stuff down through a CTE. So like in this case, SQL Server is smart enough to sort of optimize away the fact that we have this thing in the middle. All right. So if we run this query, or let’s just get the estimated plan, we only touch the user’s table once in here. Okay. Fair enough. SQL Server is pretty smart with that.

You can even do slightly more complicated things and still have SQL Server be fairly smart about it. So here we’re going to have one CTE where we select this, another CTE where we select this from the preceding CTE, and then a third CTE, which I just noticed I have misaligned slightly, where we select from the preceding CTE and apply another filter. So did I do that? All right. I did that wrong. Let’s see. Actually, no, that looks a little funny. And we’ll fix that later. So if we get the estimated plan for this, SQL Server is again, smart enough to sort of abstract away those useless abstractions that you have stuck into your query, and just give us one single solitary index seek, where all of the predicates that we are, come on, tooltip, you are being so good there, where all of the predicates that we care about, are applied to that one axis of the index. We have our seek predicate to the ID, right, because that’s the clustered primary key of the table, we can seek right to that. Then we have a couple residual predicates before I lost my finger in a tragic green screen accident, where we look at the date filter, where we look at the date filter, where we look at the date filter and the display name filter that we applied in the other CTE.

Where things change a little bit with CTE is when we introduce the top syntax to our CTE. Now, this one here isn’t going to force SQL Server to rerun the initial syntax multiple times, but it is going to change the query plan in sort of in a weird way. So, if I get the estimated plan for this, it looks kind of weird now, doesn’t it? Looks kind of funny, a bit unexpected. We have this series of top syntax. We have this series of filters where different predicates are applied. Now, when we touch the clustered index, we still apply that same initial seek predicate, right?

We still seek to where id equals 1. But because we introduced top and we set what’s called a row goal inside of each one of the CTE that we executed, or that we, I mean, they all execute together, kind of. We have a top and then a filter and then a top and then a filter and then another top. So, we have the three tops and two filters. The two filters are going to be on the additional predicates that we applied.

And thank you, SQL Server Management. So, I must have made Aaron Stellato a real mad SQL Server Management Studio is stabbing me in the back today. Today, we have the first filter, which hits the predicate on creation date. And then we have another filter, which applies the predicate on display name, where that equals community over there. So, introducing top into your CTE sort of introduces a bit of a fence because of the row goals that get introduced there.

A slightly more verbose example of that would be these CTE that we’re going to look at next. Now, in the first one, we are joining users to comments right there. And then in the second one, sorry, not the third one, there is only two. There is no three, there is only two. We are joining the post table and the votes table, right? And the post table gets joined both to the comments table and the users table from the first comment table expression.

And then the votes table we’re joining to the post table, right? So, if we look at the query plan for this, we’re going to see that SQL Server’s cost-based optimizer, cost-based query optimizer, was free to rearrange the joins to suit its own cost-based needs. All right. So, have this, it joins comments to votes over here. It joins the post table down over here. And then it joins the users table up over here. All right. Now, if we were to go and stick a top in our first comment table expression only, all right, because I don’t want to add a bunch of tops in, too many, too many tops, spoil the broth.

If we add a top in here, but not here, we’ll see the query plan change and the join between users and comments will be fenced off. And what I mean by that is now we have this top operator here and we have the join between users and comments behind that top over here. Now, sometimes it’s tough to get behind a top, but in this case, it worked out pretty well.

All right. So, we have our top operator and we have users and comments over here. And then SQL Server was free to join votes and posts and whatever order it chose in the second comment table expression. So, the top operator will fence off, at least in the current iteration of SQL Server’s cost-based optimizer.

The top operator will fence off things inside of a comment table expression, but neither a comment table expression nor a comment table expression with a top inside of it will materialize the query inside of the comment table expression. Where comment table expressions start running into problems is when you start referencing them multiple times in your query.

Now, I showed you where, like, if you stack them and sort of run down through them, things can turn out okay. But let’s look at what happens when you reference CTE multiple times, sort of without that stacking effect. All right. So, the first execution plan that we get here, back to baseline, where we only seek into the user’s table once.

Now, if we join that CTE to itself, we are now going to have two seeks into the user’s table, right? Because we had to rerun the expression inside of that common table twice. It’s actually not really a table at all.

It’s more like a tabular, right? It’s a common tabular expression because we are not materializing this result anywhere. And if we do that a third time, we will now see that we touched the user’s table three times.

We have the one, we have the two, we have the three. I think that a kick in the head. I think an easier way to sort of get across what happens when you do that is if we were to just union all and sort of explicitly select from the CTE twice, we would go back to seeing the two accesses of the user’s table with the concatenation operator over here, which unioned all of those two results.

So the same thing would happen if we did union. But just think of it as just like this query, union all, this query again, right? Because even though you make this reference once here, each reference out here means you have to rerun this expression.

All right? Now, where CTE or derived tables or anything like that can be useful is when you need to do something in a query that you can’t do in a single step. A pretty common thing would be to generate, like if you wanted to find duplicate results or you wanted to find like the first result, using something like row number is a really common way to do that.

The thing is you can’t filter on row number here, right? You can’t say something like where n equals zero because n is in the select list. And the select list, if you’re familiar at all with logical query processing, happens way at the end.

And the where clause, join clause, stuff like that occurs way earlier when the query is built, when the shape of the query is generated. So we can’t filter on this here, right? We’ll just get an error, an invalid column name n.

So we have to do stuff like this outside of a CTE, right? So if we run this, it is perfectly valid syntax. Now, this sort of gets into other, you know, sort of query tuning dilemmas and things that you might run across in query plans.

But this is one of those cases where when I see a filter operator, I understand why it’s there and I’m not angry at it. Because we have to generate that expression for the row number before we can filter on it. So we have to do all this work to get all the data we want.

And in this case, well, I mean, for various reasons. I’m on SQL Server 2022. I am in compat level 160. And so I’m getting the cool feature batch mode on rowstore automatically.

I mean, that’s been available since SQL Server 2019. But it’s only Enterprise Edition. I’m using Developer Edition.

So I get all the Enterprise Edition features, which is why Developer Edition is like a great gateway drug for Microsoft. Because you start doing all this development in Developer Edition. And you’re like, wow, what great performance I have.

And then you move it over to standard, like your production environments in Standard Edition. And all your queries are complete stink bombs. And Microsoft’s like, well, okay, well, just give us $5,000 more core and you’re all set. Our dreams of a feature flag for Developer Edition to remove Enterprise Edition features is long in the making.

It doesn’t exist, but I’m sure they’re working hard on it so you don’t run into that. This awful conundrum, quandary, conundrum. It doesn’t matter.

Anyway, comment table expressions can be useful when you have to do stuff like this. Because you couldn’t do this all in a single step. Just don’t expect doing this to have any magic performance benefits for you.

Comment table expressions just aren’t helpful in that way. All right. Cool.

Hopefully, I have absolved and alleviated you of all your misconceiverations about CTE. They truly do not help performance in any way just for existing. They don’t make queries any more readable just for existing.

Again, query formatting is more important for making a query readable than jamming a completely unreadable query inside of a CTE. Anyway, I hope you enjoyed yourselves. I hope you learned something.

I will see you in the next video, which temporally could happen at any moment now. It may have already happened by the time I say this, which is really crazy. It’s just impossible to tell.

Anyway, thank you for watching. And I’ll catch smell you later, stinky pants.

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

Video Summary

In this video, I delve into the fascinating world of SQL Server’s cost-based optimizer and explore where it decides to stick sorts in query plans when you might not expect them. We discuss various reasons for these sorts, such as key lookups, stream aggregates, merge joins, distinct operations, and windowing functions. Each scenario highlights how SQL Server optimizes data retrieval but also introduces the challenge of memory grants, which can lead to a tug-of-war between buffer pool space and query execution efficiency. By understanding these intricacies, you’ll be better equipped to write more efficient queries and manage your database resources effectively.

Full Transcript

E-Darling here with Darling Data on sort of a sleepy Brooklyn Friday. And today we’re going to talk about places where SQL Server sticks sorts and query plans during cost-based optimization, even if you don’t necessarily ask for data in a particular order. Now, there are all sorts of reasons for this that we’ll talk about as we go through things. Some of them are requirements. from certain query plan operators like stream aggregates and merge joins. And the reason why we care about them is because sorts require memory. That memory is often called a query memory grant. And that query memory grant has to come from somewhere. So unless you have a perfectly honed, artisanally crafted set of hardware for your SQL Server, there’s going to be a bit of a tug of war between the buffer pool and query memory grants. in order to get enough memory for both of them to do what they got to do.

We’re going to talk more about memory grants in a separate video. There isn’t going to be a deep dive into that at all. This is just to kind of talk about where you might, why you might see a sort in a query plan when you didn’t say, order by some data. Now, before we do that, I want to talk about something related but not quite on topic just because there’s not enough to talk about with the other thing. to justify an entire video. And that is when you’re ordering data. Now, there’s no such thing as guaranteed ordering from a query unless you order by a column. And not unless you have a unique column in the mix to order by. Now, by that I mean, if we look at the results of this query, you can see that the first line up there is the only other options I see, blah, blah, blah, some other stuff that probably sounds really smart.

If you read the, if you were to read the whole thing. But if we look at the execution plan, we have a parallel execution plan for this query. Alright, takes about two seconds to finish running. And if we rerun this, we’re just ordering by this score column right now. If we’re just ordering by this score column, there are a lot of duplicate values in this score column. So if we run this query again, we get the same result back that time. But if we keep going and say, ah, what sorry, regex engine doesn’t support plus?

It’s a fair question. And if we just keep running this, well, we’re back to the only other options they see being something. And now we have to clarify the cigar API does something else. And oh, well, we got the cigar API again. And let’s see if we get lucky on this last run. Nope, we got the cigar API again. So we ran this a few different several times, we got a few different results back in a few different orders. Now, you might be tempted to stick a max stop one hint on your queries thinking that you’ll get reliable results back that way. The thing is that you won’t.

That they’re not guaranteed in this one either. And on top of them not being guaranteed, you also can end up with a much slower query. That last query that we were running took about two seconds to finish. This one is just about at the seven second mark to complete. And also, you still don’t get the guaranteed ordering from this. So it’s probably a little bit more rare that you get mixed results back just because you have a single thread working rather than dot threads working on the query. And the, you know, just various timing differences will have more effect on multiple CPU threads than on a single CPU thread.

But you can also end up with a query that takes a lot longer. The only way to get consistent results back is to also order by a unique column. Most, well, actually, all of the tables in the Stack Overflow database have a clustered primary key on a column called ID. It’s also an identity column. It’s a pretty popular choice for a clustered primary key these days. But this is the only way to get back truly guaranteed results. And if we look at this, we also get different results back than we did just ordering by that score column. So we need to think very carefully about how we’re ordering things and what we’re actually looking for when we’re dealing with data from a column that is not completely distinct across.

Now, to illustrate the sorting stuff from some of these other queries, I’ve created an index on here. And I did that ahead of time, you can tell, because IntelliSense has inserted this really helpful Excite Byte track under the index name. So lucky for us, we know that that index already exists, we don’t haul off and try to recreate that index, do something foolish. But one place where you might see SQL Server decide to stick a sort in your query plan for what might appear to be no reason whatsoever, is to do a key lookup. So in this query plan, we have this big thick line of stuff coming out of our clustered index. And then you can see a key lookup. And SQL Server has decided to sort that data prior to doing a key lookup via a nested loops join back to the clustered index on this table. I recorded a video about key lookups recently. So if you need to refresh your memory, go look at the list of videos in this channel and you’ll see that one in there.

Now what are we ordering by? Well, since key lookups are essentially a join between a nonclustered index and the clustered index on the table, when we go and touch that clustered index, we need to go get the ID column, which is again the clustered primary key. All right. All on board there. And because we don’t have that column in order in our index, well, we sort of do, but we also have an inequality predicate on the creation date column. So we don’t, we’re going to cross all sorts of boundaries there. And our data is not going to be in a helpful order leaving the index seat with the ID column.

And so SQL Server sticks an order by the ID column into our query plan in order to optimize IO when we go and do our lookup back to the clustered index when we have to find all those ID columns. Now the next two are single query plan operators that expect sorted data. First one is a stream aggregate. So if we look at this query plan, we have a sort and then we have a stream aggregate operator. That’s because a stream aggregate operator requires sorted data coming in.

If this were a hash aggregate, we wouldn’t have that requirement. We could just bring data in however we wanted. But here we are with the stream aggregate and needing to sort our data. Merge join is another one. Now I’m just joining the post table to itself on this owner user ID column.

But since I don’t have an index on the owner ID, owner user ID column to support that merge join, I have two sort operators in here going into the merge join here. I just want to take a quick moment to say that parallel merge joins are one of the most offensive things that you can ever see in a query plan.

If you see a parallel merge join in your execution plan, you should question the foundations of your existence. Or just fix it. I don’t know. You can do both. I find that questioning the foundations of your existence is often a pretty good thing to do.

You can answer a lot of questions about yourself that way. Now another place where we might see a sort operator get stuck in our query plan is if we ask for a distinct set of data. So if we run this, or we just get the estimated execution plan for this, we will see a distinct sort down here.

This is how SQL Server has chosen to figure out a unique set of data for this query is via the distinct sort operator. Now you’ll notice that this query and the one below it, both are using a semi-undocumented hint called disallow batch mode. We just get a different query plan with batch mode involved.

If we run this query without the disallowed batch mode hint, we get a hash aggregate operator. That hash aggregate doesn’t expect sorted data, and so we don’t get a distinct sort. And you can see that this does happen in batch mode, and you know, wouldn’t that just ruin the whole demo?

That’s that old CAD batch mode. Now another place where you might see SQL Server stick a sort in your query plan is when you use a windowing function. Now every windowing function is going to have some kind of order by in it.

Unless you do like a count over or something that doesn’t really require, have an order by requirement in there, but a lot of them do. But the point here is more that SQL Server needs to order not only by the order by column, but also the partition by column. That’s also something that SQL Server will need to sort if you don’t have an index that handles that sorting for you.

Now again, we are disallowing batch mode here just to get a couple more query operators in the plan. Now just so you have a sense of what changes, under batch mode we still have a sort. That doesn’t change.

We still sort data here, but now we just have a window aggregate here. Right? So this window aggregate doesn’t really tell us all of the stuff that we would see, that we would like to see from the row mode execution plan. But the sort operator is still going to be, oh, let’s come back to that.

No, let’s get that tooltip back. There we are. That’s what we wanted. This order by is still going to be on owner user ID and creation date in ascending order. But if we run this query again without batch mode involved, we disallow batch mode, we are still going to have a sort operator.

All right. Come on, come on, come on, bring it on back. Zoom it quit early on me.

Started drinking. We still have the order by on owner user ID and creation date. But now we have a couple additional things here. Instead of having that single window aggregate operator, now we have these three things. And these three things are all happening in a serial zone.

Right? This parallelism gather streams here gathered all our data up, stuck it into a serial zone, a single threaded zone in the execution plan. And now we have to just cope with that somehow.

Anyway, that’s not really the point. The point is that we have, I mean, we have the initial sort on owner user ID and creation date. We already looked at that. But now we have this segment operator.

And this segment operator first does a group by on owner user ID. All right. That’s our partition by element. And then we have another segment over here. And that tool tip just did a good job of disappearing on me.

Come on, pal. You can do it. I believe in you. You’re the best SSMS and the best SSMS that has ever existed. We also have a group by on owner user ID and creation date.

And then our sequence project, which, again, sort of like the window aggregate, not really a helpful set of details there. So, again, these are places where SQL Server’s wonderful cost-based optimizer might decide that your query plan needs to sort data for various reasons, key lookups, operators that expect it, like stream aggregates and merge joins, or because you don’t have an index that supports the ordering that you’re asking for.

So these are all reasons why SQL Server will need to sort data. And, again, we care about sorts because sorts require memory grants. And memory grants can steal data from the buffer pool.

And the more data that we steal from the buffer pool, the less buffer pool space we have. Then we have to retake this back into it. Then we have to touch disk. And it’s all just a horrible, horrible cycle. Gas station sushi.

Just round and round we go. So easy in, easy come, easy go, I guess. Anyway, I’m going to go, I don’t know what I’m going to go do, actually. I guess I’m going to go work on that video about memory grants so I can record that one.

Anyway, thanks for watching. I hope you learned something. I hope you enjoyed yourselves. I hope you have a great weekend since it’s Friday here. I guess that’s a little bit non-deterministic.

Just like ordering by a non-unique column is a bit non-deterministic what day it is when you’ll see this. So happy whatever day it is to you. All right.

Thank you for watching. Thank you. Thank you. Bye.

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 Key Lookups In SQL Server

Video Summary

In this video, I delve into key lookups and indexing in SQL Server, explaining why sometimes SQL Server chooses a clustered index scan over an index seek with a key lookup. I walk through creating an index on the `last access date` column of the `users` table from Stack Overflow’s database and demonstrate how estimated costs can help us understand why SQL Server makes certain plan choices. By tweaking parameters in our queries, we see firsthand how key lookups can impact performance, especially when dealing with a large number of rows. I also touch on the limitations and potential issues associated with key lookups, such as their impact on read committed isolation level operations and the importance of covering indexes for filtering elements. If you’re curious about SQL Server indexing and query optimization, this video is packed with valuable insights that can help you make informed decisions when designing your database schema.

Full Transcript

Erik Darling here with Darling Data, Limited Liability Corporation. And if everything goes well here, you can all thank the wonderful, lovely, talented Drew Ferjuel, Ferjueli, for helping me correct both my audio and my video settings. If you need an audio, video health check, I highly recommend Drew’s services. Part of the health check was putting the microwave, microphone further away from my mouth and also adjusting some of the Streamlab settings that he was really smart about. If you like smart, funny people who tweet occasionally, I suggest you follow Drew at Pitferg on Twitter. I think he has another social media presence, but that’s the limit of my social media presence, so I’m going to leave it there. And Drew has a podcast with his friend Paul Baylor, who I think left Twitter. But you can see more details about their podcasting at doingtheirbest.com. So if everything goes well, thank Drew. Everything goes like crap. We’ll throw eggs at him. So this video is a little bit about key lookups, a little bit about indexing, stuff like that. Again, you know, sort of training wheel stuff, stuff that a lot of folks have questions about when I work with clients. And when I go to the bar and people are like, Oh, it’s Erik Darling, that SQL Server guy, let’s ask him questions about databases. So they get just get recognized in public so much.

I’m starting to get Eminem syndrome. Anyway, exhausting. So I’ve got this index probably already created. I turn off IntelliSense because I get annoyed with it more often than not. So there’s that. But I’ve got this index on the users table in our stack overflow database on the column last access date. Now SQL Server, a lot of the times, you know, a big question is like, why didn’t SQL Server choose my index? Well, a lot of it has to do with costing. And I’m going to show you what I mean here. So if we let’s get estimated plans for these two queries. The first one performs a clustered index scan. The scan is hidden. For some reason, we are left in suspense with some ellipses. SSMS doesn’t have a way to show you the full object name, apparently.

And the second query does an index seek into our nonclustered index and then a key lookup back to the clustered index to retrieve all the columns that were in the star part of the query that were not included in the nonclustered index. So the clustered index scan way up yonder and the index seek way over yonder. So all very interesting stuff.

Now, let’s look at a couple things here. One, the clustered index scan is going to have the predicate that we supplied to the query in it as a scan, right? There’s no seeking here because last access date is not the leading key column in the clustered index.

The column called ID is, and we’re not searching on ID, so SQL Server has nothing there. Other columns in a table that aren’t part of the clustered index key are, of course, sort of like included columns. They’re only on those leaf-level data pages, depending on which way you draw your binary trees.

It’s either top or bottom or side or whatever. So we can’t seek to that, but we can scan the entire table and get all of the rows there that we care about. So this query has an estimated cost, and I’m going to depart from the point of the video a little bit to talk about costs.

Costs are estimated metrics. They are always estimates. There is no actual equivalent to a cost unit in SQL Server.

You’ll never see an actual execution plan that says the estimated cost was 5, but the actual cost was 50. There’s no way to translate those unitless metrics into actual factual numbers. This is why actual execution plans, when you run them, have operator times.

We’ll look at those in a little bit. So this thing has a cost of 36.2, let’s just call it 22 query bucks. I don’t need to keep all those extra decimal places in there.

And the query down the bottom has an estimated subtree cost of 12.18 query bucks, plus some, you know, query bitcoins or something, whatever they’re worth these days. Now, the reason why SQL Server didn’t use the nonclustered index for this first query, and what I’m going to do is actually change this a little bit, just so we can see things back to back.

Let’s add an index hint in here. Index equals whatever. And let’s look at the estimated plans for both of these.

So the cost of this query is, again, going to be 36, or estimated costs would be 36.22 query bucks. And the estimated cost of this query is 250 query bucks. So this is why SQL Server didn’t choose this plan.

When SQL Server was evaluating plans, making estimates about how much effort in CPU and I.O. it might cost to execute each of these queries, SQL Server just chooses the cheapest one. It has no insight into whether it’s fast or slow.

You can have a very slow, cheap query. You can have a very fast, expensive query. Personally, I don’t think we should refer to long-running or arduous queries as expensive anymore, because cost is just kind of useless when you think about things in that way.

Now, breaking down the percentages a little bit, this clustered index scan costs 100%. And down in the second query, this key lookup equates to 98% of the total plan cost. SQL Server says, when we scan the clustered index, this is where we do all the work.

And the key lookup plan seeking to this data will be 1% of the plan cost, and the key lookup will be 98% of the plan cost. If we look at the details, look at the tooltip. You can see that 246.438 query bucks is about 98% of the 250 point whatever the entire query was set to cost.

So this is why SQL Server didn’t choose the key lookup here. SQL Server has that choice. It’s basically a little bit of a comparison to the case.

It’s basically a little bit of a lookup. It’s basically a long list of ways to do that. It’s basically a little bit of a lookup. Part of the reason why lookups have such a high estimated cost to SQL Server is because the optimizer doesn’t really have a good understanding of modern hardware. SQL Server has this weird assumption that you are on crappy rotational storage or you’re on Azure storage.

I don’t know. Maybe that’s why Azure storage is just that bad. So maybe you’re on old spinning rust.

Maybe you’re on Azure Storage. I don’t know. I don’t know where you are. But the optimizer says, doing all that random IO, doing that lookup over and over again, that nested loops join is going to be expensive.

It’s going to be hard to do. You might have to move the disk head around. You might have to talk to an HTTP request in Azure. Who knows?

Who knows what will happen? It will be a wild time. So that is why SQL Server may sometimes choose a clustered index and why SQL Server may sometimes choose a lookup, as long as the plan for the lookup is less costly than the plan for the clustered index scan.

Good. We’ve got all that figured out now. So let’s create or alter.

Let’s do something with this stored procedure. Let’s do one of those things with it. Completed successfully. Wonderful. We did it.

We made it. Ma’s going to be so proud. And now that we’ve got this stored procedure, we’re going to talk about, I think, a more interesting lookup scenario than, you know, you’re just selecting a bunch of columns.

All these queries select a bunch of columns from the user’s table. Actually, select all the columns from the user’s table. Every single last one. That’s what star means. Give me all of them.

I don’t know if that’s true in regex. Actually, I could be wrong there. Anyway, let’s look at a sort of more interesting scenario with key lookups. We’re going to run our stored procedure with this set of parameters first.

So we’re going to be looking for a last access date of 2017-1122. Now, this is actually a good time to point something out about the data in the Stack Overflow 2013 database. All of the tables were deleted where the creation date of something was after 2013-1231 at the stroke of midnight.

There are a whole bunch of tables. I actually think there’s maybe just two tables that have a last access date or a last edit date column in them. A lot of those have dates way after.

Because the Stack Overflow data dump had data from way after 2000, well, the first of the year in 2014. But that’s not where the cutoff, that wasn’t the column used to decide the cutoff date. So fun things there, right?

So if we run our stored procedure, which is going to look for just a start date greater than 2017-1122. It’s going to add one day to that start date because we are looking for one day. And we just want anyone with a reputation that’s greater than one, which is going to be everybody.

Because you can’t have a reputation under one in the Stack Overflow database. There is a rule against that. And, well, I don’t know.

I mean, I guess you can try if you’re feeling particularly wily. So let’s go ahead and execute this and look at the query plan. Because the query plan is going to return data rather quickly.

So quickly, in fact, I’m so impressed with it. We finished this in three milliseconds. Good for us.

That’s a great, great, great query time there. We did it. We’re master query tuners. So we have the index seek. The index seek successfully evaluates our predicate looking for one day of data.

You know, we did pretty good when it comes to the actual number of rows. That’s 786 versus the estimated number of rows at 643. That’s a pretty good job there, right?

Especially for a range predicate, not an equality predicate. It’s a pretty good guess. Spot on there, SQL Server. But then we have this additional predicate on reputation. Where did that go?

Well, key lookups can do more than just evaluate select list columns and bring those back for the query. We can also evaluate additional predicates when we do a key lookup. Down here, we’re going to see four parts of the tooltip that are sort of interesting.

We have down at the bottom, we have a seek predicate. You notice that this is on the ID column. The ID column is the clustered primary key for the table.

So this is the relationship between the non-clustered and clustered indexes. I talked a lot about this in another video called, like, how SQL Server indexes communicate with each other or something. You should watch that one if you have any questions about that relationship, why that relationship exists.

Then after that, we have this whole output list, which is all the columns that we selected out of the users table. That’s purely output columns. Then one step above that, we see the object that we touched to get all of those columns.

And then above that, we have a predicate. Now, coming back to something I said earlier, the reputation column is just hanging out along the leaf data pages of the clustered index because it’s not part of the clustered index key in a way that we can seek to it when we do this evaluation.

All right. Cool. Glad we got that figured out. So we have four things that we’re doing in the lookup.

We’re joining the two indexes together on the ID column. We’re outputting every column in the table. We’re getting that from the clustered primary key. And we’re also evaluating a predicate on reputation.

Now, I’m going to change the way this query executes a little bit. It’s not going to run for too, too long. But it is going to return a similar number of rows. It’s going to return 517 rows.

The next one should return neighborhood of 500 rows as well. But it’s going to look for data in a slightly different way. We’re going to have the same start date of 2017-11-22. But this time, we’re going to go back a full year.

Or we’re going to add 365 days to the start date and look for data after that. But this time, rather than just looking for a reputation over one, we’re going to look for Stack Overflow users who have a reputation over 110,000. There are a lot of them.

A lot of people with a lot of time on their hands. Or they had one semi-good answer in 2009. And it’s just been collecting upvote royalties ever since. Like thousands of.

It’s every day. I can’t believe they knew that. I can’t believe they knew that answer. That’s amazing. Amazing. Anyway. If we execute this, it’s going to run for a little bit longer.

Not a lot longer. But long enough to notice. Again, this one returns 505 rows. And to get to those 505 rows, we now spent a lot more time in the lookup.

Rather than executing in 3 milliseconds, we get to about 1 point. Well, about a second and a half here, let’s call it. And that’s obviously not a very good scenario.

Now, part of why this happens is because even though we find a lot more rows up here, which, you know, for better or for worse. Like, honestly, that many key lookups isn’t going to bug me too much.

It’s not going to be terribly slow as long as you’re not on Azure Storage. But when you look at how much time we spent doing that lookup, that was a lot more time than before.

The seek to 668,000 rows didn’t take very long. It was 178 milliseconds. Where we spent the majority of the time was going between the lookup and the nested loops join. That’s where the most of the time was spent.

Now, a lot of the advice that gets given about key lookups, and there are very good reasons to avoid lookups, especially for parameter-sensitive queries like this one. This one isn’t like bad, bad, but it’s noticeable.

There’s also issues with key lookups under the default read committed isolation level, where during that lookup, modification queries will get blocked, because SQL Server may take an object-level shared lock on the clustered index, and other queries won’t be able to modify that index when they go to modify data in the table and other indexes that have those columns involved in the update, or the modification, rather.

It also may lead to read queries and modification queries deadlocking with each other under the read committed isolation level for almost the same reason. Almost the same set of reasons there.

I have blog posts about that. I will put those in the show notes. It’s not a show. It’s just a video. Get over yourself. So anyway, a lot of the advice that people may give you when it comes to avoiding lookups is to cover the whole entire query.

But in the case of select star queries, or even just sort of long select list queries, this isn’t terribly good advice. This is actually pretty terribly bad advice.

Having a lot of super wide indexes on your tables to cover all these queries, not really all that great. So what you can do in situations where you have an index that requires additional columns for filtering is make sure that SQL Server can at minimum apply all the filtering elements of a query to one index access.

You can still get the output columns from another index, but make sure that at minimum your where clause is all covered by one index. There are, of course, situations where you might want to have a couple nonclustered indexes to do that.

Again, we talked about that in the how indexes talk to each other video where we had an index union plan, if I’m remembering correctly, where that was a pretty good plan for using two nonclustered indexes and bringing those together. But let’s change our index up a little bit.

The order of columns here isn’t going to matter too much, but we have last access date and then reputation. So if we remake this index with reputation in it and we run this first query, this first query is still going to be very fast.

It’s still going to be just about at the three millisecond mark. We’re not going to see a big difference here. All right. But when we execute this query using the other set of parameters, this one executes much quicker. It’s no longer a second and a half.

It’s lingering just under the half-second mark. So we’re able to shave just about a second off this query by allowing SQL Server to do all of its filtering from one index. And I’ll show you what this looks like now.

If we go to the lookup, we no longer have that predicate up here where reputation was. That predicate on reputation now is, well, not only a seek predicate here, but we also have an additional residual predicate here.

We can seek to like the start of the range, but then that residual predicate up top evaluates the remainder of the range. Anyway, that was just a little bit about lookups, you know, kind of what they are, how they work. Why SQL Server sometimes hates them, how bad Azure storage can be, and how to fix lookups if they are sort of parameter sensitive and if they are causing performance issues.

Mainly that you want to pay extra attention to when you have lookups that involve predicates on the clustered index side there. Because that means that you have sort of an incomplete index for the starting filter and you need to adjust that.

Anyway, thanks for watching. Hope you learned something. Hope you enjoyed yourselves. And I will see you in another video. Again, I’ll remind you to thank my good friend, Drew, who has a podcast, if this turns out well. If not, I don’t know.

I don’t know. I don’t know. Just go on with your lives. Just be happy. That’s all I want. I want everyone to be happy. Thanks for watching.

Going Further

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

A Little About Filtered Indexes In SQL Server

Video Summary

In this video, I delve into the intricacies of filtered indexes and how they can sometimes lead to unexpected behavior when used in conjunction with parameterized queries. I share a series of examples where I tweak a query to demonstrate how SQL Server handles these situations, highlighting both the benefits and limitations of using filtered indexes. With a touch of humor, I also sprinkle in some personal anecdotes about setting up my recording environment—complete with light-induced distortions and the meticulous alignment required for a perfect shot. This video is packed with practical insights that should help you navigate the complexities of SQL Server indexing more effectively.

Full Transcript

Blank Darling here with Darling Data. And I’ve made another gain adjustment that I think will solve all of my problems. I’m recording one more video today because I have a fetish for making my wife nervous about being late for things. That worked on too many levels. But anyway, hopefully the audio is completely kink-free this time. The only thing around here that would be kink-free. But, you know, a lot of work does go into figuring out how to get things exactly right for these recordings. And these are a lot of test runs and I appreciate all the feedback and stuff that I got. Just to give you an idea of sort of how complicated sometimes things can get, I’m going to bring up a Windows Explorer window on my desktop. Y’all can’t see it, but if I bring it up on the screen, all of a sudden I start to get a bunch of distortion down here because of the extra light from having another white window up. But as soon as I minimize it, the distortion clears up. So a lot goes into this that, I don’t know, maybe not everyone appreciates the amount, just how much tinkering and weirdness you have to deal with. But anyway, we talked about computed columns a little bit. So now I want to talk about filtered indexes a little bit because the two things often solve a lot of problems.

Either traditional columns are not having a column at all and not filtering data in your indexes and whatever. I forgot where I was going with that. I lost my train of thought because I thought of one other thing. My bathroom, you obviously can’t see it because it’s not that kind of show. But if I leave my bathroom light on, which is across the way, it also creates a bunch of distortion behind me. So very delicate setup over here. Everything has to be just right. Moon has to be in Pisces. Mars has to be in Pluto. I’m going to leave other planets out of this one. Anyway, let’s create an index because that’s what we do. We create indexes. This is a filtered index. This index has a filter where reputation is greater than 100,000. And that will give us some magical superpowers to find all the really important users in the user’s database. Stack overflow helpfully tracks our reputation, among other things, depending on which cookies you allow them to set.

And let’s run this query, which looks like a pretty simple query. And it looks like a good candidate to use our filtered index. And it is. So as we go over and look at the execution plan, we actually do use our index over here. But because this plan is simply parameterized, SQL Server throws us an erroneous error. And it tells us that we have, there’s a warning involved here. Red skies at night. We have an unmatched index. This warning typically shows up when you have a query that uses a parameter.

It’s not simple parameterized, like a stored procedure or dynamic SQL that is parameterized. And this warning will show up if you have a parameter where you could have used a filtered index because queries that use parameters can’t use filtered indexes because the parameterized execution plan has to be safe for any value that gets passed into that parameter. And depending on the filter definition, you may pass in a value for one iteration of the query that’s perfectly safe to use a filtered index. And then another time you may use a parameter value that is unsafe to use for the filtered index. A good example is a bit column. So let’s say you have an is deleted column. You typically search for values in that column that are not deleted. So is deleted equals zero. And then you have a stored procedure that runs.

The search for is deleted is a parameter. And you might look for deleted. You might look for undeleted or not deleted. Not undeleted. Undeleted would be three and that would break your bit completely. And so you might not be able to use a filtered index for every outcome of that parameter value. So that sucks. And this is another reason why I end up using this one equals select one thing over here. This is just enough gusto for SQL Server to not simple parameterize the query.

The reason why this query gets or is attempted to be simple parameterized is, well, stupid. So let’s not get into that. It’s a bad idea. So if we run this query, and it wouldn’t matter much if we did this for 10000000000 or 1000000001. Now we no longer have that warning over here and our index gets used magnificently, wonderfully, all is well in the world.

But if we change that back to 100001 or we search for a range of values within that reputation column, SQL Server will only sort of use our filtered index. In this query where we just searched for that whole greater than, equal to predicate up here. Oh, I lost my finger again.

If we search for this or we search for this range of values that are hopefully hidden by my giant head, SQL Server scans the clustered index for the first query and uses our clustered index a little bit for the second query. But in the second query plan, we actually have a lookup back to the clustered index.

And that lookup is figuring out which reputation values are less than 100999. The reason for that is because when we created our filtered index way up here, the only thing we did was tell SQL Server that all the values in this reputation field are greater than or equal to 100000.

We did not put the reputation column into the index so that SQL Server had all of the literal values that are stored in this column available to it in the index. So what happens is SQL Server knows that it can locate however many rows for this query that qualify for 100000 by scanning the filtered index that we created. And then doing a lookup back to the clustered index where the reputation column is stored to evaluate the second half of our predicate, the second half of our where clause to figure out where reputation is greater than 100999.

So let’s recreate our index a little bit. And let’s see what happens when we run some of these queries again. So all tree of these…

Did I change that down here? I didn’t. Let’s fix that in the demo. 1, 2, 3, 9, 9, 9. There we go.

I knew I forgot to do something earlier. Alright, let’s do that again. All three of these… We… Scan…

Our filtered index here, which is fine. Because… Every row in here is going to qualify… For it. SQL Server knows that every single row… In this index…

Is greater than or equal to 100000. Up here. So it doesn’t have… There’s nothing to seek to. There’s no point in seeking to these 600-something rows… Because it knows every single row in here…

Is going to qualify… For… That predicate. Right? There’s not even a predicate here. If you look at… If you look at the details of the tooltip… There’s not even a predicate applied…

Because the predicate is sort of implicit in the fact that… Every… Like our filter definition… Is our exact where clause here. So SQL Server is just like… Nope. Just scan it. 600 rows.

Who cares? For the second query… Where we look for greater than or equal to 100001… We do indeed… Get an index seek. And we find… Every single row in there.

These both return… 613 rows… But… The slight tweak to the predicate up here. So SQL Server says… Ooh! I can seek now. Which…

I don’t know. Take it or leave it. It is what it is for 600 rows. If you have… If you have performance problems with a 600 row scan… You have much, much bigger problems in your life. I promise you that.

And then of course for the final query… We do get another… Index seek. And this evaluates… Just one seek predicate.

And since our starting predicate… Would qualify the entire… Contents of the index… For the starting point of the where clause…

The only predicate we have to look for… Is where… This one is less than 100,999… Of course if we change this… To…

Look like… Our query above… Where we look for 100,001… SQL Server would… Seek completely… To all of those values… And we would apply… Both sides of the predicate. Because the first half of the predicate…

Because the first half of the predicate… Now doesn’t exactly match… The where clause… Of our filtered index. This still leaves us with… Kind of a… What I think is sort of a weird… Downside to filtered indexes.

And that is… If I’m looking for… The max reputation value… Is going to be… Over 100,000…

Right? It’s going to be… Included in that… Like… That like… That little water… That watermark… In the… In the data… But… We go and run these… SQL Server…

Says no… Not really… Can’t figure that out… Got to have a clustered index… Scan up here… We scan the entire thing… But of course… If we ask for…

The max… Value… Where it’s greater than… Or equal to 100,000… We go back to using… Our filtered index… Then I sort of get it… You know… Like…

You know… If we deleted a bunch of stuff… From our table… Or… You know… We artificially reduced… Everyone’s reputation… To some… Super low value… Or something… Cut everyone in half… Something like that…

Then that… There might be no data… In that filtered index… That plan might not be safe… To reuse… But… You would think that… If we made enough changes… Like that to the table… Then the stats update… Or the…

The statistics… The statistics… The statistics… Modification… Counter… Would… Get a new plan… Created… But… Anyway… I don’t know… It’s complicated…

It seems like… One of those things… That would just… Happen for you… Like… The max values… Have to be in this chunk… But… You know… Always and never… Anyway…

Thanks for watching… Hopefully… Again… All the kinks are out of the audio… This time around… If not… I give up… I quit… Throwing away all my computers… I’m gonna go collect rocks… Live in the mountains…

Eat varmint… Stuff like that… Regrow my… Full beard… Anyway… Thank you for watching… I’ll see you in… Another video…

Not today though… Cause… Now… Now I might actually be late…

Going Further

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

A Little About Computed Columns In SQL Server

I’m working on ramping up my recording and streaming setup now that life has settled down a little bit, and publishing the results to YouTube while I work out the wrinkles. Enjoy some free video content!

Video Summary

In this video, I delve into the intricacies of using expressions within SQL Server queries and how they can impact query performance. I explore why SQL Server cannot use expressions to seek data in indexes and demonstrate a practical example by querying users from the Stack Overflow database based on their combined upvotes and downvotes. After running several queries and analyzing the resulting execution plans, I explain that creating an index directly on the expression did not significantly improve performance. However, introducing a computed column allowed SQL Server to provide better estimates and potentially seek into the index more efficiently. The video also highlights the importance of expression matching for leveraging indexes effectively and suggests improvements for SQL Server Management Studio to enhance usability with regional number formatting settings.

Full Transcript

Erikaphor now, Darling here with Darling Data. And I’d like to thank the lovely and talented Randolph West for giving me some very good, specific suggestions on my audio settings last night, especially for bearing with me while I was in the midst of several red wines and was a little bit unaware of my surroundings. Randolph ran me through a vocal analyzer. I’m not sure if that’s a fetish yet or not, but it’s on the table now. And anyway, the suggestions were to reduce the gain a little bit and adjust the compressor setting a little bit so that I got peaks instead of tables. So, there we go. Thank you, Randolph. These a hell of a them. Today’s video, we’re going to talk a little bit about how… well, so… in a video recorded for the last 24 to 48 hours, I hesitate to use terms like yesterday and today because due to time zones and other temporal dilemmas, we never know how correct those terms ever were. If I was in New Zealand, it could be two days ago. But in a video recorded for the last 24 to 48 hours, I talked a little bit about how…

if you need to calculate an expression, right? Like run a function on a column, add two columns together, like tack something onto a column. SQL Server can’t use those expressions to seek to data in an index because those expressions happen at a later point in a query running than when you first touch an index or when you touch a table, when you touch those 8KB pages that make up your… …tables and indexes and basically everything in SQL Server. So, what I’m going to do is run this query and the goal of this query is to look for users in the Stack Overflow database who have more than 1 million combined upvotes and downvotes. My meteorologist skills are increasing with every video. Watch out. So, I’m going to be all powerful and control the weather like a chainy. So, if we run this query and we look at the query plan, we’re going to take about 444 milliseconds to scan the entire clustered index and return one single row.

So, if we look at the details of the clustered index scan, we’re going to have this big predicate over here. Upvotes plus downvotes greater than or equal to $1 million. My meteorologist skills really took a steep fall on that one. So, of course, job interview red meat right there, a clustered index scan. Users are complaining, dear lord. And so, let’s create an index on the users table. On the upvotes and downvotes column. Let’s see what kind of…let’s see if we’re going to get the job or not.

We’re going to run this and look at the query plan because that’s what we do here. We look at query plans all day long. We stare at them. And this sort of depressingly didn’t change execution time all that much. This still took right around 430 milliseconds. The last one was like 440 milliseconds. That could have just be…like Windows Update could have been running in the background and looking for something. And that could explain like the 10 millisecond change in CPU time there.

So, let’s not get too hung up. Let’s not pretend that we won. Alright? So, the main problem is that we still have this expression that needs to get calculated for SQL Server to find what we need. And of course, if we…the way that, you know, non…the way that indexes in general are stored, we don’t have any metadata about… We know what values are in upvotes and we know what values are in downvotes, but we don’t have any information about what, like, a calculation on those two columns would produce.

Right? SQL Server doesn’t track that. Same thing with, like, date columns or date time, any time-related column, really. If you have two of them in an index, SQL Server’s like… And SQL Server’s like, oh, well, there’s an hour between them and it was like 21 days, 16 hours, 7 days, whatever that… Nothing compares to you, Leric, is…like, SQL Server doesn’t track the amount of, like, time between two date columns, right?

So, whenever we need to calculate something based on two date columns, two columns added together, things like that, we need to pre-compute that so SQL Server can find that data. Alright? So, what a lot of folks out there might want to do is create a filtered index, maybe to figure out where upvotes plus downvotes are already greater than a million. Unfortunately, that is…you can tell by the red squiggle in IntelliSense that’s throwing right there, that we are not going to be able to create this.

So, if we try to, say, where upvotes plus downvotes is greater than or equal to one million, we’re not going to be able to do that. Even if we used advanced parentheses technology, we would not be able to create a filtered index based on that. So, disappointing there. I think you can do that in Oracle, so maybe you should just use Oracle instead. Sorry, Microsoft.

But, what we can do is we can do the old Microsoft SQL Server two-step, and we can create a computed column that gives us the results of upvotes plus downvotes. And note that we don’t even have to persist this in order to create an index on it. Now, just creating that computed column, even not persisting it, will give us, if I remember correctly, a slightly better estimate than before.

Not great, not awesome, but better. We still don’t, we’re still not able to seek to the data we care about. But, now SQL Server is at least like, hey, hey, maybe, maybe an index would help. So, SQL Server will give you a missing index request in some cases, but SQL Server won’t say, oh, if you create a computed column and then create an index, everything will be better.

But, SQL Server before was just like, nah, man, you’re screwed. So, there’s that. So, let’s follow SQL Server’s now very helpful missing index advice. And let’s create a nonclustered index on our computed column.

Notice how that was pretty quick, right? So, that was SQL Server actually writing all those values down in the index. Before, we just had a virtual column where SQL Server would still have to compute that every time. But, now that we have it all written down, we can, hopefully, stealthily, much more quickly than before, seek into our index.

Now, instead of taking about 430 to 440 milliseconds, depending on what Windows Update is doing, we can, oh, my finger went way over weird. I tilted, that was my fault. We get, my finger, I’m losing my finger again.

We get one roll back in zero seconds and a slightly better estimate of 293. Before it was 1570, before it was something else, I forget. I’ll have to rewatch the video and maybe edit that in.

Just kidding. Not doing that for a 10-minute YouTube video. Ha ha. If you want me to do that, you’ll have to pay me. One thing, though, that is disappointing about computed columns is that what helps SQL Server use them is something called expression matching.

Now, in this query up here, notice we didn’t touch the up-down computed column directly, but SQL Server was still able to use that index. It was able to match the expression in our where clause to the computed column and use the index based on that, which is great. But, it doesn’t work if we tinker with that expression at all.

So, let’s run these two queries back to back. We get one row back from both of them. This one, again, seeks to the data we care about.

Zero seconds, and the one down here, unfortunately, scans the whole indexes back to around 440-something milliseconds with a really awful estimate of 73,739,714. If anyone from Microsoft is watching, and you happen to know anyone who works on SQL Server Management Studio, can I get a comma?

Can we get some commas and numbers? Can it be a setting to add whatever regionally appropriate terminator to thousands is in our numbers? I know that some countries use dots, other countries use commas.

Here in the US of A, we are fully committed to the comma. And it will be very useful if we can get some commas in our numbers in SQL Server Management Studio. Please, I beg of you.

I beg of you. You have access to all sorts of functions in C-sharp like format and whatnot. They can produce values, numerical values that have commas or whatever regionally appropriate. Thousands terminator you have, or hundreds terminator.

I don’t know. Math is beyond me. Anyway, this is another example of how if you need to calculate an expression in a join or where clause, you might not be able to do it cleanly even with a good index on the columns that you care about because the expression that those columns or whatever else produces, a function like left, right, r-term, l-term, substring, replace, all that stuff.

SQL Server doesn’t store that, right? SQL Server doesn’t have access to that. SQL Server runs those expressions at a different point in the query than when it touches the index and is able to seek the values.

So be very careful with how you express things like that in your queries. If you find yourselves in a performance quandary where an expression like that is causing a scan of an index, it’s taking too long, then you might need a computed column and an index to help you locate that data quickly.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I hope that my petition to not have BeerGut Magazine permanently ban me from using the name Eric. We’ll get enough signatures to pass Congress or whatever happens. I don’t know.

If not, I’m going to start a contest to rename me. That’d be fun. Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in another video. Have a good one.

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 Indexes Talk To Each Other In SQL Server

Video Summary

In this video, I delve into the intricate world of indexes in SQL Server, specifically focusing on how they communicate and collaborate with each other. Starting off by addressing some feedback from recent recordings—such as the audio quality improvements and the mysterious “modulated” sound—I explain that while these issues are noted, viewers can now enjoy both high-quality video and audio versions of my content. The main topic centers around how indexes on the same table interact, particularly between clustered and nonclustered indexes. I demonstrate this through creating two indexes on a users table—one unique and one non-unique—and running queries to illustrate their behavior in query plans. By adding more columns to these indexes, I further clarify the dependencies and seek patterns that arise when querying such structures. The video concludes with an exploration of how SQL Server uses multiple nonclustered indexes together, showcasing techniques like index intersection and union, and contrasting them with key lookups.

Full Transcript

Erik Darling here with Darling Data. Unfortunately, Birgut Magazine has purchased the rights to the name Erik with a K, so I’m not sure how much longer I have using that. I’ll keep you updated on what it ends up getting changed to, is legal proceedings proceed. Anyway, I’m here on this rainy, dreary, dismal Friday to talk about indexes in SQL Server. But first, as part of my ongoing effort to produce high quality content for you lovely consumers out there, I solicited feedback about the audio and video quality of recent recordings. Apparently, the video quality is fine aside from the fact that I’m kinda ugly. And from the audio front, I got a couple pieces of feedback. One is, audio is better, not perfect, but much closer. No details on what got better, and what needs improving, but thank you anyway. And the other is, sounds a bit modulated. No details on what sounded a bit modulated, what modulation is, or how to fix that, but again, duly noted there. But anyway, this is the recorded video of a post that’s already written and scheduled. When, I forget, but it’s over on that computer, not on this computer, so it’s not in front of me. But you have that, you have that, you have that, you have that, you have that.

You have both versions to look forward to now, you lucky dogs. But this is all about how indexes talk to each other in SQL Server. I don’t mean like, like, you know, join two tables together and the indexes are like, hey, we can find the data. I mean like, indexes that live on the same table need to be able to talk to each other. Clustered indexes need to be able to talk to nonclustered indexes. And nonclustered indexes need to be able to talk to each other so that we can make our queries better and faster, and we get all the all the seeks in the world. We never see a scan because, Lord knows, we’d get calls from customers if they knew there were scans in our query plans. So I created two indexes on the users table. One of them is unique. You can tell that they are created by the helpful red underlining squiggles from IntelliSense. Thank you IntelliSense for your IntelliSense.

IntelliSense. But we have a unique index, which is noted both by the unique syntax in the definition and also by the underscore uc in the index name. Good there. That means that every value in the account ID column is unique. One before, one after, to the end of the table. Every value is unique. And now SQL Server knows it. And the non-unique index, well, that one just ends in NUC. No special syntax needed to note that it is not unique, thankfully.

So we’re going to run these two queries, and we’re going to talk about the query plans, and then we’re going to talk about why we got the query plans that we did. So the first one, which interrogates the uc unique index, is going to look a bit like so, where we have a predicate on the ID column, and then a seek predicate on the account ID column. Right? So we have a residual predicate to ID and a seek predicate to account ID. My meteorologist skills are slowly improving.

Strom Cods over Chickigo. If you remember that reference, you win one million dollars from beer gut magazine. Now the reason we got these two query plans, because when you have a table in SQL Server with a clustered index on it, it doesn’t have to be the primary key too, though usually that’s how it works out.

But when you have a table in SQL Server with a clustered index on it, every single time you create a nonclustered index, those nonclustered indexes will inherit, as part of their physical being, their structure, the clustered index key column, or columns, if you’re that kind of person. Where those columns end up depends on the definition of the index. By that I mean a unique index will carry the clustered index key column, or columns, in the includes.

I mean, the key of the index, which is an option for you. And then a non-unique nonclustered index will inherit the clustered index key column, or columns, in the key of the index. That’s why in the non-unique index query, we’re able to seek to both ID and account ID. And in the unique query, unique index query and query, we’re able to seek to account ID, but then residual predicate to ID.

Because ID is not in a part of the index that is seekable. It’s in the included section. In the bleacher seats, in the cheap seats. We can kind of expand on it, make it a little bit more clear what I mean there by adding another column to the index. So these index, I’m going to change both indexes to have account ID, then creation date, and then inherit the clustered index key column.

In this case, there’s only one. I don’t need to make everyone, I don’t need to make people with two clustered index key columns feel included on that, because I only have the one here. But you might have a different scenario at home, or at work, wherever you lay your head. So we’ve got these two indexes now, I think anyway, at least it seems like we do.

Didn’t get any errors, so I assume that’s right. But if I run both of those same queries again, the first one is going to be interrogating the uck, and the second one is going to be interrogating the nuk. The uck query plan is going to have the exact same details.

We have the predicate on ID, and the seek predicate on account ID. Nothing changed there. Boop. Boop. Why did that change? Well, because nonclustered index, sorry, rowstore indexes, not even non-clustered or cluster, rowstore indexes in SQL Server, have introduced sort of dependencies between their key columns.

So in this case, we’re able to seek to account ID, but then because we have creation date in the middle, right, you can kind of think of the index definition looking like this. I’m sorry about typing in demos, I’m sure someone will be deeply offended.

But if we changed our index definition to look like this, it becomes a little more clear why we had a seek to account ID, and then a residual predicate on ID, it’s because creation date is in the middle of that ID sandwich. Right? If we wanted to seek to ID, we would also need to search on creation date so that we wouldn’t have to, like, make that hop over creation date to find IDs.

That hop is a residual predicate or a range predicate. We have to hop over that thing and seek within that whole range of values. I’m going to get rid of that now so it doesn’t ruin future demos.

But we can kind of see how things work a little bit better if we do search all three columns, right? So we’re going to bang these two queries. And the first one is going to look about how we would expect it to look.

We have a predicate on ID still because in the unique clustered index, I’m sorry, in the unique nonclustered index, ID is still stuck in the includes, right? So ID is up there.

And then down at the bottom, we have account ID, and we also have creation date. And my meteorologist finger is going to get cut off while I try to point to creation date, but that’s okay. I didn’t need that finger anyway.

Probably all beer gut magazine money, I’m going to lose some more fingers in the process. So we can seek to account ID, seek to creation date, and then residual predicate to ID. All right?

But in the non-clustered, the non-unique nonclustered index, we have a triple seek. We seek to account ID, creation date, and ID. You can see account ID, creation date, and losing my finger there, but you can see ID a little bit over to the right, my disappeared finger.

Another way that indexes, non-clustered, well this time, nonclustered indexes can talk to each other in SQL Server. So if we create these two, both non-unique indexes. One of them is going to be on the last access date column, and one of them is going to be on the creation date column.

All right? So if we have those two indexes on our table, and we have a query that’s searching on those indexes, on those columns in those indexes, and we look at the query plan, we’re going to see something kind of interesting, where we have a seek into the creation date index, and then a seek into the last access date index.

And then those two indexes get joined together. What do they get joined on? You ready for a wild time?

The ID column. So SQL Server takes one index, joins it to the other index, and produces a result. Those are nonclustered indexes.

We use two nonclustered indexes together. There are different ways SQL Server can do that. There’s index intersection, index union, there’s all sorts of stuff SQL Server can do to bring two nonclustered indexes together. You’re probably more used to seeing a key lookup in a query plan.

I think if I quote out this, we should see that. And a key lookup is just a nonclustered index talking to the clustered index. All right?

So a key lookup is a little different from the index union or index intersection stuff. A key lookup is just saying, hey, nonclustered index, what’s in the clustered index? And then the clustered index says everything.

And then we have a key lookup. Anyway, that’s how indexes in SQL Server talk to, cooperate, communicate with each other, and how SQL Server’s really smart query optimizer is able to use indexes to do that, make all your queries super-duper fast, and yeah, I think that’s about it.

Anyway, it’s coming up on noon, which means it is lunchtime, and by lunch I mean red wine, and by red wine I mean I’m not even going to be using a cup today. So anyway, thanks for watching. Like and subscribe.

Smash that like button, as the kids say. Thanks for watching. Hope you learned something, and see you next time after the red wine wears off. Or maybe if it wears on a little too long. Heh heh.

Like, what a little bit. Bye. See you next time.

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.