Tag: sql temp table

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.

YouTube Days: Predicates In SQL Server Query Plans

Video Summary

In this video, I dive into the world of predicates and how they impact query performance in SQL Server execution plans. Starting off with a lighthearted anecdote about adjusting my microphone settings after receiving feedback on audio volume, I transition to a more technical discussion. I explore different types of predicates, such as seeking directly to primary key values for efficient index usage, versus scanning entire tables when indexes are absent or not well-suited. The video delves into the nuances of equality and inequality predicates, demonstrating how SQL Server handles seek and residual operations in various scenarios. By walking through practical examples and execution plans, I aim to provide a clear understanding of why certain queries perform better than others and offer insights on designing effective indexes that align with query patterns.

Full Transcript

Erik Darling here with Erik Darling Data, fresh off reading some Streamlabs OBS tutorials about how to make microphones sound better in Streamlabs. As someone who spends much of his time wondering why no one does basic Google searches before writing a SQL query to figure out what might be a good, better, or best way to write the query, it didn’t occur to me until today to see if there were any nice, helpful blog posts about how to make Streamlabs microphones sound better and all that. Not that it sounded bad, it was just low. I got some complaints yesterday aside from people who were wrong about commas going first in lists of columns in select query, well any query really.

I got a slight neg from probably some spy at Beargut magazine saying that my audio volume was a little low. So I’ve cranked the gain up, I apologize if anyone goes deaf listening to me. I’ve done my best to screen out breathing noises and other uncomfortable mouth sounds that drive people with misophonia up walls, so hopefully that works out too.

So we’ll find out. Anyway, I am here today live in the flesh, in the present, to talk about different sorts of predicates that you might see evaluated in queries like seeks and selects and residuals and other things like that. When you look at your SQL Server execution plans, the majority of the queries that you’re going to see today are all going to have this little one equals select one tacked to the end of them like so.

And I’ve got a, what is hopefully a helpful blog post about why that’s there here, that will be in the show notes and all that good stuff. So anyway, let’s get going. So first, probably the easiest thing to start with is seeking to a value in your primary key slash clustered index.

If you’re one of those people who has a clustered index and a non-clustered primary key, you might see different results. I’m not saying that’s a bad thing. This is not quite commas first in the realm of bad ideas, but it’s certainly not a typical pattern that you see explored in databases, at least in SQL Server databases, perhaps on my learning path with other database platforms.

I’ll find out differently. But anyway, if we run this query and we look at the execution plan, we are going to see what is, at least I hope, this late day and age in our lives, a clustered index seek.

And we have our seek predicate down the bottom there. I’m working on my weather meteorologist pointing skills here. That’s a little funny because everything is backwards.

This is my left hand even though it appears on my right. Magic, how the magic happens, how the sausage gets made. But you can see that we seek directly to where id equals one, which is exactly what our query asked for.

No bugs in SQL Server at all for that. What happens if we have a where clause where we don’t have an index on the column that we’re searching, in this case this reputation column right here, is we are going to scan, in this case, the clustered primary key of the table and evaluate that predicate.

We don’t have an index that helps us find data. SQL Server is asking us, begging us for an index that would help that. But we don’t have one at the moment.

We’ll get around to that in a moment, I promise. But if we look at the, come on back tool tip. If you look at the tool tip for this now, we have a predicate right up there. Point D weather man.

Where we scan the entire table and as we’re reading data pages, we are filtering the reputation column to find where anyone has a reputation of 56106. So we do have to scan the whole table. That doesn’t take very long in the context of this query.

It’s not a very big table and my computer is pretty good. So we get through all those rows in about 258 milliseconds there, give or take some timing there, drawing up query plans and whatnot. But then if we search an indexed column, right, like the ID column again, it’s a clustered primary key, and also search the reputation table, we get back to doing pretty okay as far as performance and, you know, being able to seek to certain values goes.

This takes absolutely zero time whatsoever because we seek to like one data page pretty quickly. And if we look at the tool tip for this, we have a seek predicate at the bottom that evaluates the filter on ID, and then a residual predicate. It just says predicate up at the top there, but most people will call it a residual predicate because it’s a predicate that you evaluate after the main predicate.

So it’s kind of stuck on the back end. I think that’s Hollywood talk, right? Get some residuals in there. But we seek to where the ID column equals 77, and once we find any IDs, and this, I mean, it’s a primary key, so it’s unique, so there’s only going to be one of those.

But we seek to where we find 77, and then we evaluate to see if the row or ID 77 also has a reputation of 56106. And in this case, because I have very carefully engineered all of these demos, we do find one row of Mr. Darren Kopp. I don’t know if that’s supposed to be a funny name or not.

It might be a really great joke in German or something. And we can do that with multiple different kinds of searches on the ID column, right? Because it’s the leading column of the index.

It’s, again, the clustered primary key. We can do all sorts of searches against that column and still seek to those values, right? How efficient the seek is is going to depend on how many rows get located. Right now, the users table in the Stack Overflow 2013 database has around 2.4 million rows in it.

So searching for anyone with an ID under 78 is pretty quick. If we were to search for anyone with an ID greater than zero, we would seek, but we would seek to everything in the table. We would read all that stuff, and that’s not a very helpful seek, though it is a seek, and most people would fail job interviews for that.

We would say that seek is good. At least if I was giving the job interview. Watch out!

Anyway, we can get this. It’s pretty efficient. Darren Copp is still the only person who shows up. And we have a pretty close result to the last query.

Our seek predicate way down the bottom. We’re just looking for anything less than 78. And again, our residual predicate, beep, beep, beep, tickle, tickle, tickle that little one.

So we’re going to go to the actual equals sign. Searching for where reputation equals 56106. Now, for some reason, when I’m answering questions about indexes, I get a lot of questions about index design, index theory, like what’s the best index for this?

How’s the best index for that? And really, the best index is one that kind of closely matches the queries that you’re running. It’s not like you can just design an index out of nowhere and be like, good index.

Like you can put the most selective column first, always and forever. But if you’re not searching on that column, it just gets in the way. So to kind of demonstrate that, I’m going to create this index. And this index, which I’m going to talk about once I finish hitting the control and E stuff over there to execute it, has account ID first.

Account ID almost matches the ID column. It’s almost a mirror image of it. There are a few discrepancies, but that’s okay. It’s still a unique column, right? There’s still no duplicate values in there.

So we put a very selective column first, and we put reputation, which is mostly not terribly selective, second. The thing is, this index doesn’t help us run our query. I’m going to run a blast from the past, and we’re going to look at the execution plan, where we’re going to use the unhelper index in this case.

And we’re going to try to search for where reputation equals 56106. And we’re going to use that nonclustered index, but we still scan it, right? We look at this.

We still have to scan the entire index and apply that predicate to reputation. We can’t seek to any values in reputation because that account ID column is in the way. So like we could search for account ID equals something and reputation equals something, and that would get us where we’re going.

But just searching on reputation when we have an index that does not lead with reputation doesn’t really help us out all that much. So I’m going to create an index that is going to be helpful for several queries, and we’re going to look at how useful it is.

So if we force SQL Server to use the primary key, the cluster primary key of the table, and then we also force SQL Server to use the nonclustered index, and I just do that to make it very clear which index is going to be used where and what you should be looking for in them.

If we run these two queries, they’re both going to run pretty quickly in all the zeros of seconds. That’s fast. That’s fast. We don’t have any problems here. Great. Fixed it all.

We’ve already looked at that plan before, but let’s look at the plan now for the index with two key columns, and it has both reputation and ID. Now we actually have two seek predicates that get evaluated.

All right, we have a seek to reputation. We have a seek to ID. So SQL Server can evaluate multiple seek predicates within the same query as long as they are the key of the index. Right? Like we have an index on reputation and an index on ID.

We can seek to all the values and reputation and ID really easily. We can find those very quickly with our where clause. Things change a little bit when you get away from equality predicates and start getting into inequality predicates or range predicates.

So like greater than, less than, greater than, equal to, less than, equal to, not equal to, is not null, is also one of them. And if we look at this query, we get a few more rows back. We don’t just get Darren Kopp back.

We also get the lovely and talented Bob and the hopefully also lovely and talented Rex M. Maybe also funny jokes in German. I’m not entirely sure. But if we look at our index seek now, we have a seek to a reputation is greater than or equal to 56.

And then we don’t have the double seek. Then we’re back to having a residual predicate up there to find where anything is less than or equal to 77. The reason for that is because when you create an index and it starts putting values in order, even if you have duplicate values in that index, if you’re searching for greater than or equal to 56106, what happens is Segal Server might cross multiple boundaries once you find anything after 56106.

So we need a residual predicate on less than or equal to 77 to evaluate everything in that range. We don’t have that result in that perfect equality order. So we have to evaluate a range of values in order to find 77.

Things get a little bit weirder if we throw some more inequality predicates at it. So at this time we are looking for where ID is less than or greater than or equal to 78, 77 and less than 78.

And then where reputation is greater than or equal to 56106 and less than 56107, which by weatherman my way over here we can see that’s 7. All right.

So let’s look at the query plan for this. And this is going to throw you off a little bit maybe if you’re not used to seeing these sort of things. We’re going to go back to seeing two seek predicates down here.

We can see where SQL Server is evaluating the predicate on reputation with the greater than or equal to and then with the ID column looking for anything that is over 77. But then up at the top we have a residual predicate that almost searches for the same thing, but that’s where we throw in the less than 78.

So again, crossing those ranges, crossing those boundaries of values means we have to evaluate residual predicates sort of by scanning data after we seek to data. Things get more challenging when you throw or at SQL Server, especially in join clauses.

Or is very difficult for the optimizer to come up with a good query plan with. Right now the optimizer doesn’t have a way to turn that into like a union or union all type thing where you say, you know, where, you know, this predicate matches or this predicate matches with a union all between them to unify the results.

So we end up with a query plan that looks roundabout like this, which is very strange looking. We have all these filters and constant scans that are producing rows. And SQL Server has to join all those results to a scan of our nonclustered index.

And actually has to scan through it twice because we have to join to this and join to this. Well, I technically, you know, but if we look at this, we have just a predicate now. We don’t seek to anything.

Right? No predicates in there. That’s less than or equal to 77. Greater than or equal to 56106. And if we look at the join, we have outer references listed here, which means it was apply nested loops.

And we took all of the rows that came out of this and we joined it to these conditions down here. Each one of these constant scans is going to represent one side of the OR clause. So you can’t really see it in the query plan, but one of them is going to represent the predicate on ID and one of them is going to represent the predicate on reputation.

So that’s fun. We can apply multiple OR predicates combined with AND predicates as well. And if we do that, we get a pretty similar execution plan.

Right? And it’s going to be kind of the same story here. SQL Server is going to evaluate the range of greater than 77, greater than or equal to 77, less than 78. And then over here, it’s going to evaluate reputation greater than 56106 and less than 56107.

Where we start to run into problems with SQL Server queries is when we start applying functions to columns. Now, if we didn’t have these functions here, we would be able to seek pretty normally to all the values that we want. But because we do have functions wrapped around those columns, we have an index scan again.

And if we look at the details of the index scan, we can see these predicates here on ID and reputation. This comes back to the concept of sargability or being able to search cleanly into an index defined rows. The way that I like to conceptualize this, and this is not like, you know, God’s honest down to the scientific detail truth about how databases work.

But when you apply predicates to an index that can be matched cleanly, SQL Server storage engine can read data pages and apply those predicates pretty easily as it reads them. As soon as you wrap a column in a function like this, you sort of change the layer at which that can happen. The storage engine is way down here and running functions through like the expression service happens way up here.

So SQL Server has to apply the function to the column, which happens up here. It can’t happen down here with the storage engine. It would be cool if it could, but at this point in time, SQL Server doesn’t offer that.

Good stuff, right? This is also sort of true of string columns. One thing I should mention up here is that sometimes if you write predicates that are not sargable enough, like SQL Server won’t even be able to apply them when it scans an index.

You’ll have a separate filter operator that goes and filters out rows after you’ve done a bunch of work on an index. So if we create this index, which this one here leads with, well only has display name as a key column. If we create that and we run these three queries, only one of them is going to be pretty reasonably fast.

The other two are going to be slow because we have to match across a whole bunch of bytes in a row rather than just matching the leading bytes in a row. If we look at this execution plan, or these three execution plans rather, we have an index seek up here, an index scan down here, and then just a little bit cut off another index scan down here.

This index seek has a seek predicate where we look for greater than or equal to Jeff at Wood, little d, and then less than Jeff at Wooie with an uppercase E. The SQL Server forms a range where you can find like the little d and then the big E, right? So like if it ended with a, if Jeff at Wood ended with a big D, and no people out there are going to be laughing at me talking about little d’s and big d’s.

But if we ended with a big D, we would still find Jeff at Wood, and Jeff at Wood, but we don’t find Jeff at Wooie. For the other two queries, we don’t have that seek ability because the other two queries have leading wild cards in the searches. So this one here and this one here, our scans look a little bit different.

We just have regular predicates to look for all the Jeff at Wood, and then we have one down here where we just look for trailing Jeff at Wood. And both of these result in an index scan that takes about two seconds. So when you’re designing queries for indexes, there are a lot of important things to think about.

But mainly, you want to tune indexes for queries. And then you also want to tune queries for indexes. So indexes, great things to, you know, key are, you know, where clause columns, join columns, things like that.

Not if you’re doing, you know, these kind of searches, they’re kind of useless for all that stuff. And then you want to design queries that can use indexes, and you want to avoid things like this because it’s not that you can’t use indexes when you do this. It’s just that you can’t use them as efficiently because you can’t hit them where you store them.

You have to run this function before you can apply those predicates. Anyway, this ended up being a little bit longer than I planned on. I don’t know if I talked slow or something.

And also I got some weird green screen creep happening down here under my arm for some reason. I’m not sure why. Gremlins, ghosts, goblins, ghouls, all sorts of things in there. Anyway, thanks for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope you don’t put commas first in your select list. And I will see you in the very next video. Have a good one. 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.

Join Simplification In SQL Server

Video Summary

In this video, I delve into the fascinating world of joint simplification within SQL Server execution plans. I explore scenarios where joins cannot be simplified due to potential null values and unique key constraints, as well as cases where they can be pruned out entirely when no additional rows are projected from an outer-joined table. The discussion is complemented by detailed walkthroughs of various query examples, demonstrating how the SQL Server optimizer identifies opportunities for simplification based on the query’s structure and requirements. I also share some personal insights about setting up a green screen for video recording, navigating through technical challenges like allergies that can disrupt even the best-laid plans, and the importance of maintaining clear communication with viewers despite any minor distractions.

Full Transcript

Hello! Welcome back! Erik Darling here with Darling Data. I know that you probably can’t tell by looking behind me, but I have a freshly steamed green screen with no weird artifacts, at least at the moment. We’ll see what happens. We’ll see if the lighting in here cooperates. Right now I have basically the equivalent of a full tanning bed blasting light at this thing. So hopefully, aside from some weird artifacts, where my tattoo colors match the color of the green screen chroma key stuff, we can avoid any unpleasantness. Sorry if that’s distracting. Anyway, I wanted to talk a little bit today about joint simplification in SQL Server. I was going to record this yesterday, but I got terribly, horribly sidelined by allergies, which you can probably still hear some remnants of from this absurdly large nose of mine. So, there are of course rules to when things can be simplified. There are a number of other simplification rules and steps outside of joins, but I think joins are probably a pretty interesting one to start with, at least for now. I’m not going to fix that in post. You can just deal with it.

So, one time when joins can’t be simplified, and I’m going to explain simplified as we sort of look at execution plans, is when we, I mean we can just get an estimated plan for this one. Right? So, what happens here is because this is a right outer join, and this could happen in a situation where the optimizer rewrites a query to use a right join rather than a left join. It can reorder and reorganize all the things that it wants. But because we do this, and the results that we get here add nulls to what comes out of the users table, right? This u.id column that we’re selecting.

We get this query plan over here, where we have to touch both the votes table and the users table. SQL Server couldn’t simplify that away because we were going to get additional results back from the users table. So, it added nulls to the output where nulls didn’t exist before. The column that we’re selecting here and the column that we’re joining to here, that’s the primary key. It’s a clustered index of the users table. So, no nulls are allowed to exist in there. Another place where SQL Server is allowed to simplify joins is when we don’t select, we’re not projecting any columns from a table that is outer join to, and we wouldn’t get any duplicated rows.

Now, this is a fake join because this isn’t actually the relationship between posts and users. I’m joining on two unique columns, the id column in both tables, again, primary key clustered index. So, SQL Server knows that there’s not going to be a many-to-many relationship here. If we get the estimated plan for this query, you’ll notice that unlike the query plan for the last query that we looked at, sorry, that jumped around a little bit. And this one we only touch the users table, right? So, that join to the post table is completely taken out of the query optimization steps.

It’s pruned out, as smart people might say. Now, if we look at this query, which is an inner join from the users table to the users table, which I know looks a little weird, but what we’re going to do is run this and get the estimated plan. And SQL Server is actually not free to simplify this one. We touch both tables. The reason why is because we used an inner join, and an inner join might actually eliminate rows. This one’s a little funny because, you know, we’re joining the table to itself on its own primary key.

So, maybe simplification could be a little smarter. I don’t know. I don’t think I like this one very much. But in this example, where we wouldn’t actually eliminate any rows because we have an additional condition here, or rather we’re doing a left outer join here, what we’re going to see is SQL Server only hit the users table once on that one. SQL Server can also apply that to much larger queries.

So, I’m going to run this whole thing. And the only part of this that really, really matters is way down at the bottom. And I do apologize to the greater SQL Server community for having that column up there has a leading comma, but I only do that to make life easier when I need to quote it out. So, you can deal with it just for this one query. And if we get the estimated plan for this one, we get this whole gigantic query plan back.

If we say zoom to fit, you can sort of start to grasp the absolute magnitude of this thing. Every single one of those derived left joins that we do throughout the entire query is part of the query plan. SQL Server hits all of these things. If we change this query just a touch, and again, apologize for the leading comma.

Hopefully no one beats me up at the next conference. We quote that out and we rerun this whole entire thing just like before. And we get the, well, we’re not going to run it, but we are going to get the estimated plan for it. The estimated plan this time around is much simplificationed, simplified, because we only touch the users table this time.

We don’t do all the other joints. We don’t do all the other work. So, SQL Server’s query optimizer, when it’s looking at the query that you send into it, one thing that it’s going to do is try to find things that it doesn’t have to do. The optimizer is lazy, just like me. And if it doesn’t have to do some work because it just doesn’t need to, it’s going to find a way to not do that work.

Query simplification can apply in a lot of places. Part of it would be like contradiction detection, right? So if you have a query that’s like where ID equals one and ID equals two, SQL Server is going to say, well, ID can’t be one and two at the same time. Even if you’re one of those nudge nicks that does like a comma separated list in an ID count, it can’t be one and two simultaneously.

So it would just give you a constant scan and say, guess what? Your query didn’t give me anything. So that’s fun. And I don’t know. I think that’s it. This is sort of another little test run video with the new setup. Make sure audio comes through well.

Make sure video comes through well. Make sure that the green screen is still mostly functioning, except where it looks like I have holes in my arm over here, which is interesting anyway. Well, I don’t know. That’s it. I’m going to go blow my nose. These allergies are awful. Anyway, thank you for watching. I hope you learned something.

I hope you enjoyed yourselves and I will see you soon, soonly in another video, hopefully with my simplified recording setup. Thank you for watching.

Going Further

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

Improving The Parallel Query Processing Documentation For SQL Server

Video Summary

In this video, I delve into the parallel query processing documentation in SQL Server, pointing out where it’s incorrect or misleading, and highlighting some dead-end links that don’t provide additional information. I walk through several examples to demonstrate how certain constructs, such as recursive common table expressions (CTEs), scalar user-defined functions (UDFs), multi-statement table-valued functions, and the `TOP` keyword, can actually support parallel execution in ways not fully captured by the documentation. By creating detailed demos using the Stack Overflow 2013 database, I show that some parts of these queries can indeed run in a parallel zone despite what the docs suggest, while other parts remain single-threaded. This video aims to provide clarity and practical insights into how SQL Server handles parallelism for these constructs, helping developers make more informed decisions about query optimization.

Full Transcript

Erik Darling here with Darling Data. And today’s episode is going to focus on the parallel query processing documentation, some places where it’s wrong and some places where it’s misleading, and some places where the links to get more information sort of lead to a dead end. There isn’t actually any more information at some of these links, which is kind of funny.

Now, the docs start by talking about constructs that inhibit parallelism, which is this list here. You have scalar UDFs, which is mostly right. You have remote queries, which is like half right.

You have dynamic cursors, which is right but incomplete. You have recursive queries, which is half right. You have multi-statement table-valued functions, which are also half right.

You have the top keyword, which I’m actually going to call half wrong, because it is, again, not really accurate. And what’s funny is that for a lot of these things, there’s all these things that say for more information, you go over to whatever link. And for a lot of them, there’s not actually any more information about parallelism at the other docs.

And that’s fine. Maybe it’s not the right place for it. But it’s also not very helpful to folks who are trying to find things if there is nothing additional at those other links about parallelism. Now, if you scroll down a little bit further, there’s this really exciting table of non-parallel plan reasons.

The non-parallel plan reasons are, they’re right for the most part. But the thing that kind of gets me is that there’s actually better detail in this table than there is up there at the top, or up there in the list of things that might inhibit parallelism. And for some of them, it’s kind of funny because you’ll see reasons that queries can’t go parallel here that don’t actually show up in here.

Like there’s nothing about top in here, there’s nothing about multi-statement table valued functions in here. There is, of course, something about table variables not supporting parallelism. But there’s nothing in the documentation up here that says the problem with the multi-statement table valued function is the table variable, not the function itself.

So I’ve written a series of demos that go through and either complete or debunk some of the information in the docs. So let’s start over in the lovely Stack Overflow 2013 database. And the first thing that we’re going to look at is recursive CTE.

Now, what I’m going to do is not actually run this whole thing because we don’t actually need to run this whole thing. And for a lot of these queries, I have some hints on there to force parallelism because it wasn’t naturally occurring. But what I want to show is that some part of the plan can indeed go parallel if need be.

So we’re just going to get an estimated plan for this recursive query. And just to make sure everyone understands a recursive CTE, a CTE is when you start off with a CTE. And then inside of that CTE, you execute some recursive doodads.

I love the doodads. So here we’re just selecting from post where the ID is equal to some post value. And then you union all and you join the recursive portion of the CTE.

You join that sort of back to itself, right? Because the name of the CTE up here is P. And we’re selecting from that part of the CTE.

This is the anchor. And this is the recursive portion. So if we look at the plan for this, now, if we remember what the docs said, one thing that would happen if we had a recursive query query is that we would have parallelism inhibited for that query.

And we can clearly see the execution plan that a large chunk of the query plan, after the recursive portion, does execute in parallel. We have a parallelism distribute streams.

And that’s where we join off to the user’s table outside of the recursive portion. So after we select from the recursive CTE, we join to the user’s table.

That whole portion of the query is free to run in a parallel zone in the plan. But the recursive CTEdoes have a restriction where this part has to run in a serial zone in the plan.

So the recursive CTEdocumentation, about half right. Just about half right. And have like the recursive CTEportion, can’t go parallel, but other parts can. The scalar UDF version is, again, right, but incomplete.

So I’m going to create this UDF, scalar UDF here. And this UDF, even though I’m on SQL Server 2022, and I am in compat level 160, 160, one restrict, so like some functions could potentially be inline, scalar UDF inlining, pretty cool feature.

Started with, you know, SQL Server 2019. But there are a lot of limitations to it. And one of the limitations to it is that if your scalar UDF has a CTE in it, then it is disallowed from scalar UDF inlining for some reason.

Why? I don’t know. It just is. So if we run a query that calls that function, we’re going to run the whole thing once. And then we’re going to run, actually, you know what?

We’re not going to run this whole thing because I didn’t create indexes to support this, and it will run for four hours. That’s a different demo. But what I do want to show here is why the documentation is only half right. So the initial call-in query up here is forced to run single-threaded.

If we grab the properties of the select operator and we look at the details in the properties pane, we are going to see the infamous non-parallel plan reason. And since we are on SQL Server 2022, we actually have the filled-in reason here, the t-SQL user-defined functions not parallelizable, which is right.

They’re not when they can’t be inlined. Inline functions might not go parallel even. But what you do see in the part of the plan that calls the scalar UDF, right, in here, where the UDF is executed, we have a parallel plan.

If we look over here, we can see that the body of the function was indeed able to engage a parallel execution plan. And part of the reason why this occurs naturally is because I didn’t create indexes.

So good on me for remembering that at the last minute and not sitting here for four hours waiting for results to come back. And the scalar UDF thing can be expanded out to two places where I see UDFs pop up quite a bit, where I really wish they wouldn’t.

One is in computed columns and the other is in check constraints. So if we create this function, actually I already created this, so doing this is really just for show. Create this function, oh, what did I do?

Oh, oh yeah, okay, I can’t alter it because it’s already referenced by the table. Good, good, good. So we don’t actually have to do that for show. But what I have here is a table created called serial. And the definition of the table has a computed, persisted even computed column in it that calls that function.

I’ve already stuck some data in the table and I’m just going to run this and get the estimated plan. And this will, because we have a scalar UDF in the computed column on this table, helpfully called serial, we are going to have our non-parallel plan reason yet again.

All right, so even SQL Server, SQL Server’s scalar UDF inlining abilities cannot defeat a scalar UDF in a computed column even if it’s persisted. It would be the same deal even if we indexed it as a parallelizable thing.

Check constraints are a little bit more complicated in the restriction. So I’ve got another table where I have this as a check constraint and where I already have some data in there. And the main difference in what’s parallelizable when you have a check constraint is in which columns are referenced.

So in the first query, I’m just, I’m just like basically cross-joining these tables together. But when I do a cross-join, the plan’s a little weird. So I’m just doing a left join on 1 equals 1, 2, I’m self-joining this table to itself basically.

And I’ve got the query trace on 8649 here to force a parallel execution plan. And when you look at these two plans, the first one, actually let’s go back to the queries real quick. The first one just does a count big.

We’re just doing a count of everything everywhere. Which means that the check constraint column doesn’t have to be projected out anywhere. It’s not involved in the query.

So the first query plan actually does, is fully parallel. This is all parallel up until the very end of the execution plan. And the second one, where we select the column B, which is what the check constraint is on, and where we also group by B, because we have to group by B if we’re getting a count, that query plan is forced to run fully single threaded.

And if we look at the non-parallel plan reason, again, we’re going to have the non-parallelizable function in the details there in the properties. So a little bit more complicated of a story with the check constraints.

And so it really is the column that’s involved in the check constraint that causes the problem. So if I created this table differently, and I put the function on the ID column, then doing anything with the ID column would, if I had to join with it, selected any of that stuff, then that would cause the plan to run single threaded.

Now, multi-statement table value functions are also somewhat restricted in parallelism, but not fully restricted in parallelism the way that the documentation kind of indicates. So if we create this function here, it returns a table variable, we do all this work in here.

The part of the query plan that is initially forced to run single threaded is going to be the modification to the table variable. Modifying table variables can’t run in a parallel zone.

The other part of the query plan that’s going to be single threaded is going to be reading from the table variable that the multi-statement table valued function returns. There’s all sorts of intricacies here where if you had table variables, like since multi-statement table valued functions report, like basically support, you like multiple steps, if branching, all that crazy stuff in there that uh, inline table valued functions don’t.

So if you had table variables declared, inserted to read from, and internally inside of the function, uh, table variables could theoretically be read from in a parallel zone, but the return table variable, the table variable that you return data from, cannot do that.

So if we run this query, or just get the estimated plan from it probably, uh, we’re going to see the estimated plan both for the query that calls the multi-statement table valued function, and for the multi-statement table valued function itself.

So when we looked at the scalar UDF, the body of the scalar UDF was allowed to run in a parallel zone. It’s not the case here. If we look at the properties of the table insert for, uh, the, uh, multi-statement table valued function, we are going to have, uh, that’s sorry, this is, this is a long, this, this one’s a mouthful.

Uh, we’re going to have a non-parallel plan reason that table variable transactions do not support parallel nested transaction. I’m not sure if that should be transactions in there. Uh, it seems a little weird that that’s singular, but whatever.

Um, I’m not the grammar police. Uh, don’t, don’t engage in that sort of fascism. Uh, but, uh, up here in the, uh, the query that calls the, um, calls the multi-statement table valued function, we do indeed have an entire parallel zone in that query plan.

The part of the query that is not allowed to run, uh, in parallel is reading from the multi-statement table valued function, returning that table variable. That is, that is a restriction, uh, across the board.

Uh, another place where the documentation is, uh, quite short on detail, and where, you know, if we remember, like, come back, coming back over here and looking, we see the top keyword mentioned here, right?

Trying to give tops a bad name. Big mistake. Uh, and then we look down in the non-parallel plan reasons. There’s no mention of top anywhere in here, right?

And top isn’t mentioned as a non-parallel plan reason. Uh, and this is where it’s a little misleading, because it says top will restrict parallelism, but then if you look in the details down here, it doesn’t actually do that. Uh, there’s no, there’s like no mention of it here.

And it’s, it’s only even partially true that it, because it only happens sometimes. So if we look at this query, where we have, uh, two select top ones, one here and one here, and we just sort of join those together.

Uh, and we look at the estimated execution plan for this. We are going to see, uh, two branches of the parallel query plan, right? Because we have the two top ones.

Uh, they can’t share whatever they were doing. Uh, and we do have SQL Server coming into a serial zone after the initial parallel zones, right? So like we have the gather streams operators here, which is clinching our parallel zones together.

Uh, these two things happen completely in parallel. But then as we get into the top operator, this is where the serial zone is, right? So everything basically from here on over happens single threaded, going into the top.

Where that diverges from being the God’s honest truth is when we use apply. So, uh, when either when we use the cross apply, uh, language element here, or if SQL Server optimizes a nested loops join to use, uh, apply nested loops rather than regular nested loops where the apply pushes predicates down to when we, we talk to the table on the inner side of the join.

Uh, then, then we can get a fully parallel zone there. I’m just going to get an estimated plan for this. And you can see that in this case, we have a fully parallel zone.

Well, let’s sorry. Let’s frame that a little bit better. Uh, there we go. We have a, uh, the end of the parallel zone gathering streams going into the final top, but we do not have, but we, we don’t have any restriction on this part of the query.

If you can even see this top end sort is, uh, fully inside of a parallel zone. There’s no clenching or cinching of the parallel threads there. Uh, the other thing or another thing that can, uh, cause a parallel, or sorry, a serial zone in a query, uh, is a global aggregate.

So, uh, I’ve got quite a bit of fancy, um, query stuff, query hints in here to get exactly the plan that I want. Uh, sometimes it’s kind of tough to get exactly the plan you want, just writing the query, uh, with, you know, some of the tables that you have available.

Uh, but if we look at this estimated execution plan, we have, uh, two parallel branches of the query. Uh, we have this part, which is fully parallel. And then going into both of those, uh, uh, I have to remind myself what those were.

There are two counts in this case. If we look at this going into each one of those counts produces a, um, produces a serial zone in the plan. We can see the gather streams happen there.

And the stream aggregates that do the count here. Uh, and then the global sum, right? The getting the sum of the two counts, adding those two counts together is also in a serial zone. This is would also qualify as a serial zone in the plan.

If we did some, if we, if our query looked a little bit different, uh, you can kind of, uh, see how that works. If you, well, actually, that doesn’t even go parallel. So forget it.

Um, and that’s not true of, uh, queries that have a grouping element, right? So global, global aggregate is just like select count, select sum, select average, min, max, all that stuff. Uh, for queries that, uh, have a, uh, grouping, uh, element to them, those queries, uh, those aggregates are allowed to happen fully inside of a parallel zone.

All right. So this one, this whole query runs in parallel. That’s fine. Uh, another thing that the documentation is only half right about is linked servers. So, uh, and I, I realize that this is cheating a little bit, but it’s the best that I’m going to do, be able to do quickly.

Uh, so I have a linked server here that I create. It’s a loopback linked server, meaning the server basically points to itself. And what I’m going to do is, uh, clear out, uh, wait stats and latch stats.

Uh, I’m going to query, uh, wait stats for the server. Uh, and then I’m going to run, I’m going to query the linked server here using open query. And then I’m going to select, uh, from the wait stats DMV here.

So if I clear everything out and I’m the only person on the server, it’s just me, nothing else is going on. And this happens quickly enough that I’m confident that it’s my query doing the parallelism. Uh, the first run through of wait stats after I clear everything out is all zeros.

And then the second run after I execute my query and re query the wait stats, I do have some parallel query weights and that’s pretty consistent across like every execution of this seems to end up with just about the same amount of CX sync port, CX sync packet, all that stuff.

If I keep executing this, the, the, these weights stay close enough that I’m, you know, I can confidently say that’s my query doing it, right? It stays pretty, pretty close to, uh, what it is.

Uh, if we kind of go back up a little bit here to where it’s quoted out and the reason I have this is just to show that one query, like the query that I’m executing does go parallel is, uh, well, actually, you know what, we’re not even gonna, we’re not gonna bother running, uh, oh, actually, I finished.

What, what the hell? So if we look at these two, uh, query plans, the version of the query that just touches the base table on the, on the server itself without using the remote query stuff, uh, that does go fully parallel.

So, um, this is about 238 milliseconds of, uh, execution time. So I do believe that the 300 or so milliseconds of parallel query time is close enough to this, that that would, that would indicate that it was my query that was responsible for it.

But if we look at the query plan for the, uh, remote server query, uh, we can, we do see that locally, the query that calls the remote server can’t go parallel. That restriction is local to where you call the linked server, but on the linked server, the query over there can, can run in parallel.

So again, only about half right on that one. Now, uh, I think the final thing that I want to talk about are fast forward cursors. Now, this is one part where, again, the table is more helpful than the documentation source, because we have, uh, where is it?

No parallel fast forward cursor is a reason in the documentation here, but it’s a little weird that the documentation up here only mentions dynamic cursors. All right. So that’s a, that’s a little strange, especially because when you go to that, uh, go to the documentation about cursors, there’s no mention if a fast forward cursor is a dynamic cursor, which might be something helpful to mention. So if I run this and everyone can be impressed by how blazing fast my query, my, my cursor was look at, look at that fast cursor execution. Uh, and we look at the properties here. Uh, we will see the, the, the, uh, warning that I highlighted before the no parallel fast forward cursor that does indeed restrict a parallel plan, but, uh, that is not really, uh, that is not really documented well over on the, uh, the learn site. So, uh, that that’s pretty much the end of it there. Um, you know, uh, there’s a written version of this post with all the demo scripts in it coming out, uh, around the same time that this will, this, this will be coming out. So, uh, if you want to dig in further on any of the scripts, see how things work there. You can, um, you can, uh, you can follow along that blog post. This is just the video version of it, which, uh, walks through stuff.

I don’t know. Some people like videos better. Uh, me, I’m, I’m just mostly trying to get the, uh, the video set up exactly how I want it here and also, uh, get, get back into getting used to recording things because, uh, I let that slide for a really long time because, uh, various reasons that I’ll hopefully get to talk about it past some. But anyway, uh, that’s it for today. Uh, I’m going to, um, go drink some water. Parched. Dry in here. Hear it. Anyway, thanks for watching. Hope you learned something. Hope you enjoyed yourselves. Uh, and I will see you in another video. Uh, I don’t know.

Maybe, maybe tomorrow, maybe later today. We’ll see what happens. See how, we’ll see how, uh, see how motivated I’m feeling after some nice, nice New York tap water. All right. 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.

Cardinality Estimation For Local Variables And Parameters

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 nuances of local variables in SQL Server stored procedures, offering a fresh perspective on their usage and potential pitfalls. Typically, my discussions focus on how local variables can affect cardinality estimation when used instead of parameters or literal values for filtering queries. However, this time around, I explore scenarios where local variables are mixed with parameters, highlighting the impact on query plans and performance. By running through several examples using parameterized dynamic SQL, I demonstrate how local variables can lead to poor cardinality estimates, especially when dealing with large result sets. The video also touches on a new optimizer feature in SQL Server 2022 called parameter-sensitive plan optimization, explaining why local variables can break this functionality and suggesting that they should not be used as a workaround for parameter sniffing issues.

Full Transcript

Erik Darling here with Darling Data. I’ve been busy the last few months fending off various hostile takeover attempts by BeerGut Magazine. Despite their best efforts, I don’t think that a six-pack is quite enough compensation for the glorious Darling Data brand, despite what GoDaddy tells me my domain is worth, which may be about the price of the six-pack, depending on local factors. Erik Darling here with me, I’m recording this video today to talk a little bit, well, talk about local variables in a slightly different way than I usually talk about them. Usually when I talk about them, it’s in the context of, like, there just being one local variable, how SQL Server deals with cardinality estimation when local variables are being used in place of parameters or literal values for filtering queries. Erik Darling here with me, I see this a lot when helping clients tune store procedures where, and again, the clients are nice people who keep my lawyers afloat so that I can fend off BeerGut Magazine hostile takeover attempts. But I see them a lot in store procedures to do something like this, like, where someone just didn’t feel like typing data at day minus one over and over again because they’re going to use it in a bunch of different queries to filter stuff out. So the query, having a good time. The query is end up looking something like this, where on one side you have a local variable like this being used and on the other side you have a parameter that gets passed into the store procedure being used. So I’m going to use a slightly different example to demonstrate what happens sometimes when you mix local variables and parameters in your where clause. Now, for these queries, I’m going to use the nice, safe, happy parameterized dynamic SQL that will keep your SQL injection attempts at bay. So everyone can rest safely and soundly knowing that. And I’m going to start off using values for my filtering that only get one row back from the user’s table. I’ve got two parameters that I’m going to pass into my dynamic SQL here. I’ve got one for upvotes and one for downvotes. And I’ve got a few different iterations of the dynamic SQL that I’m going to run to sort of use as examples here. Now every time I run this, the code in this, I’m going to clear out the plan cache. So they’re going to get a brand new query plan for each time this group of queries executes. But we’ve got this first one here. And this one is going to use parameters for both filtering on upvotes and downvotes. So the values that get passed in from here are what get get get get uses parameters in here.

And then I’m going to switch off having one use upvotes as a local variable as a filtering parameter. And then another one where I do the same thing with downvotes. So I get that there and we can see the local variable being used there. And then I’ve got a final one where both of them use local variables. So these both get the local variable doodad cardinality estimation used. Doodad. Professional, right? So I’m going to run this. And we’re going to look at the query plans together. And what did I do? Oh, did I have something highlighted? I did probably. There we go. Second time’s a charm. Much like me.

So let’s look at these query plans. And hopefully my zoom it skills are still fairly sharp. So we’ve got not too bad. So we’ve got this first query up here that uses parameters for both upvotes and downvotes. And we see that we get a guess of one for both of these. We also get a guess of one for both of the two, both of the queries where one of the parameters was substituted with a local variable. So we’ve got one here and we’ve got one here, but we’ve also got a regular parameter passed in on the flip side. So like there’s downvotes as a parameter there and there’s upvotes as a parameter there.

Only the ones that end in underscore v are the locally declared variables to the batch. Cool. So these both also get a correct guess of one because both of, because SQL Server is able to use the upvotes and downvotes. So it’s a cardinality estimation for the parameters to kind of guess one row and everything goes okay because of that. There’s not too much room for error when you have a single row that gets evaluated. But the last one, all the way down at the bottom where local variables get used for both upvotes and downvotes predicates here, you get a guess of 45.

So SQL Server where it no longer uses like the good part of the histogram to make a cardinality estimate. It uses the density vector stuff that the density vector math for both local variables. Things get thrown off a little bit. In this case, it’s just not the end of the world. Getting one row back when you expect 45 rows, it’s not that big a deal. Go away, zoom it. I’ve got a couple posts that talk about some of these things. They’re in this script file, but they’re here to remind me to put them in the show notes.

So those will be links in the YouTube video blog post wherever this ends up. We’ll see. See how wild I feel later. But those will be available there. Anyway, let’s change this a little bit because when you get when you’re just getting one row back, pretty rare for everything to like fly really badly off the rails. But what I’m going to do is I’m going to replace both of these with zero. And the reason I’m going to do that is because there are a lot more rows where upvotes and downvotes are zero for users because a lot of folks join stack, stack overflow, stack exchange, ask a question, get downvoted into oblivion, never come back, never cast votes, never do anything.

So there’s a whole lot of people qualify for these zero filters here. And this is where things get a little bit more dangerous, right? So like for low numbers of rows, almost any plan is going to be okay. For a big number of rows, it’s less okay. And I see people use this a lot and try to say, well, I did it to fix a parameter sniffing problem. Like, well, if all you’re ever getting back is a small number of rows, you probably don’t have much of a parameter sniffing problem anyway, because the parameter sniffing problem comes from when you have giant variations and how many rows might come back depending on the search argument.

So if you’re just always working with a relatively small number of rows, like you can’t just say, blanket, I have a parameter sniffing problem, because most of the time parameter sniffing is a good and fine thing. You get plan reuse, SQL Server uses the same plan, doesn’t set your CPUs on fire, generating execution plans over and over again, much like a recompile hint would or something like that. So let’s look at these queries now when we are dealing with more rows than just one.

Now, we’re going to get all four results back. And since I don’t have an order by on any of these queries, like we don’t like we don’t, we’re not going to get results back in the same order for any of them. This is actually also kind of a good lesson, like aside from, you know, the local variable thing is this one, the reputation is all scrambled here.

And this one reputation is in fairly decent order. This one reputation is a bit more scrambled here. And in this final one reputation is back to sort of being in order.

And we’ll talk about why when we look at the query plans. But let’s go look at how these performed. Now, the top query makes a startlingly good cardinality estimate only off by 10%. Not bad SQL Server, right?

Good for you SQL Server. But this all finishes pretty quickly. Since I’m using SQL Server 2022, I’m in compat level 160. I get and I’m using developer edition, which is the equivalent of enterprise edition.

I get batch mode on rowstore for some of these queries here. You can see the actual and estimated execution modes are both batch. So batch mode for rowstore kicks in and SQL is like, oh, I have to do some extra work.

Let’s batch mode this baby up. Talking more about batch modes a little bit further than I want to get in this thing. But, you know, whatever.

We can talk more about that later. But what happens is for the other queries SQL Server makes less and less of a good guess for what’s going to come out of these operations that do the filtering. So the second query, the second query plan rather, this is an astoundingly bad guess, right?

SQL Server kind of only made an okay guess for one of the parameters, not for both. And when you combine the cardinality estimates that it came back with and SQL Server was like, well, there’s two of them. I carry the two.

450. But 450 is an astoundingly bad guess. And because of this, we get a less than ideal query plan that takes round about three seconds to finish. Now, there is no mix of batch mode and rowstore.

All of this thing runs entirely in rowstore. I believe one of the seeks might be batch mode, but it’s really neither here nor there whether that happened. But the important thing is that the reason why this second query result set, which is this one.

Well, it’s identical to this one, but we’ll see they have identical query plans. The reason why this comes back in order is, of course, because SQL Server performs a sort by reputation to satisfy the stream aggregate that happens next. The hash match aggregate returns rows back whatever.

There’s no ordering, nothing like that. So you just get rows splatted back to you and you can sort them in the application. Right? That’s what all the smart people say to do.

This third query where, again, result sets were a bit scrambled. We also get a pretty lousy cardinality estimate on this one of 852 rows. But we can see very clearly from the results that we got 1782285.

So 1.78 million back from that. It’s a seven digit number, right? Yes.

That many. Seven. 10 minus three. Got nervous there. I thought maybe I was missing a finger or something. Anyway. So again, we get the hash match aggregate back. We get the hash match operator in this query plan.

So this one, again, was another one that came back with the sort of scrambled results because there’s no ordering to what comes out of a hash match. The third query, which uses both, or sorry, the fourth query plan, which uses a local variable for both upvotes and downvotes, gets, again, a pretty astoundingly bad cardinality estimate of 45 rows out of 1.78 million. Things fall apart a little bit more here.

Sorry, I don’t know why this thing keeps jumping around like that. I’m going to have to highlight. Just the magic of highlighting for context. The sort spills a little bit here, and this thing ends up taking around about three seconds. So even when we use both local variables, that’s what happens in this fourth one.

Things are obviously at their worst because we get this sort of lousy cardinality estimate for both of the local variables. But even for when we mix and match or mismatch or whatever, however you want to talk about, we still get pretty bad cardinality estimates. We don’t get very good query plans.

The top plan, even though it’s a clustered index scan, makes the most sense here because we don’t have to do a bunch of lookups to get a bunch of other rows. And especially because the cardinality estimates for all of these lookup plans are so low. We also have relatively low subtree costs.

So this one, SQL Server thinks this one’s going to cost about $1.50. SQL Server thinks this one is going to cost about $2.8 query bucks. And we get like just low costs on all of these.

So SQL Server is not even choosing like a parallel lookup plan, which probably would have helped here. But again, is that really what you want all the time? Maybe, maybe not. Probably a little bit more than we can talk about here.

Anyway. Yeah. So using local variables often results in pretty bad cardinality estimates. Sometimes you will not notice them because other parameters or other arguments might filter, either filter out enough rows so that a bad plan choice might not matter.

Or you might just get like you might just be working with a generally small set, small number of rows anyway. So you don’t really have all the problems that you run into when you start working with a larger number of rows. One small thing that I want to point out here before I sign off is that when you use local variables, you also break a new optimizer feature in SQL Server 2022 called the parameter sensitive plan optimization.

And I’ll show you real quick what I mean by that. All three of the queries that have at least one local variable involved just sort of end where the query text ends. This top query plan, return control ever comes back to me, has a little bit more stuff in it, right?

We can see all this plan per value object ID, blah, blah, query variant ID. If we click on this little ellipsis over here and we get all this stuff back, we’ll see what SQL, this is what SQL Server injects into the query text to enable multiple plans for the same query so that we get, we get a better optimized plan for different sets of parameters that have different cardinality estimates involved with them. But using a local variable, even just one of them does break the parameter sensitive plan optimization.

So just something to be aware of there. If you’re using SQL Server 2022 and you expect this magical parameter sensitive thing to kick in and fix all of your bad parameter sniffing queries, using local variables will mess that up. So please don’t do that.

Please don’t fix parameter sniffing by using local variables and then expect the parameter sensitive plan optimization to kick in and do any more work. For you, it just won’t do that. It does not, does not work that way. Anyway, I think that’s about good for today.

It’s about 15 minutes on this thing, which is about five minutes more than I was aiming for. So, yeah, I don’t know. I’m just gonna, I’m gonna go, I’m gonna go now and think about my life. Think about why I talked for five minutes more than I should have.

Hopefully you didn’t find it to be a waste of time. But anyway, thank you for watching. I hope you learned something. And I will see you in the next video. I’ve got a bunch of stuff lined up to record. So, yeehaw.

Hopefully that’s… H

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.