Blog

SQL Server Performance Office Hours Episode 68

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:00:38 – Video Description Link
• 00:02:18 – Data Saturday Croatia
• 00:03:14 – First Up
• 00:04:54 – Merge Considerations
• 00:06:06 – Delayed Durability
• 00:07:35 – Adding Information
• 00:09:44 – Parameter Sensitivity
• 00:10:28 – Tire Pressure
• 00:11:11 – Plan Analysis
• 00:12:49 – Hash Join Comparison

Full Transcript

Erik Darling here, Darling Data, and it is, once again, one of the most miraculous days of the week, Tuesday. Wow. Whoever invented Tuesday, you deserve some kind of prize, medal, award. I hope you get the historical commendations that you deserve, so richly deserve. But that means it is time for Office Hours, where I answer five of the most important questions that you, the SQL Server community, have submitted to me to answer, for free.

Down in the video description, if you want to ask your own question, the link to do that is right down below, there. Just keep looking down. It’s not a trick, I promise. I’m wearing pants. You can also do other things, like hire me for consulting, buy my training, become a supporting member of the channel if you want. I hope you enjoy my endeavors and efforts here. And, of course, you can also, for free, like, subscribe, tell a friend.

I guess there’s some effort on your part still involved there, but it’s quite minimal. It’s some clicking, right? Some clicks here and there. Just a random click. If you just love free stuff, you cheapskate you, you can download my free SQL Server performance monitoring tool. Absolutely free, open source, no email, no form.

It’s not a phone home. It’s not telling me anything weird about your server. It’s just grabbing all the important performance monitoring metrics that you would ever want, right? Weight stats, blocking, deadlocks, query performance, CPU, disk, memory, all the stuff that you care about.

When you’re like, why is this SQL Server being such an incredible jerk right now? You can figure out why using my free performance monitoring tool. And if you can’t figure out why looking at pretty charts and graphs, then you can have your robot companion do it for you.

Have your robot companion friends use the built-in MCP tools to look at your performance data and give you a helping hand. Perhaps that is what you need. I don’t know. Data Saturday Croatia.

Swiftly, swiftly coming towards us. Actually, as I record this, it’ll be coming out on Tuesday the… Let’s just look at a calendar here. Let’s make sure.

Tuesday the 9th. So I will already be overseas. And then it will be this Friday coming up. So after, I guess, I record the next couple of videos, I’m going to have to edit this slide again.

This slide used to be so full of life and travel and possibilities in the world. And now it’s two, just going to be one soon, I guess. I don’t know.

Maybe I’ll just stop talking about where I’m going to be since I won’t be in Seattle until November. That seems a little silly to talk about that for five months. Ha ha.

Maybe when it gets closer. Marketing. Some crazy stuff. But for now, we are in the throes of June. Be a good band name. And we’re going to do office hours.

All right. First up, let me surround you correctly. I know lots of good reasons to avoid merge.

Okay. Should performance be one of them? Have you ever saved the day by removing merge? Yeah.

In fact, I have saved the day by removing merge. So, you know, performance, it’s always something you got to watch with merge. You know, depending on maybe the amount of data you’re merging, performance would be more of a thing.

If you are just doing a pretty stock and standard upsert statement with, like, a set of values. I don’t think performance is going to be hampered all that much. That’s just where all the other reasons kick in.

If you are merging large amounts of data, yeah, I’d be pretty opposed to doing, like, a big, like, even just, like, leaving it at, like, the upsert thing. Not even deleting stuff. Let’s just say it’s a million rows.

Right? Or rather, let’s say it’s two million rows. One million rows will get updated. One million rows will get inserted. Like, you’re still doing two million. You’re still doing two million modifications in one query.

Right? It could really blow up on you. So, is merge performance usually the first thing I think of? No.

But I will say that in most practical circumstances, I have never run into a situation where separating out the insert and the update performed worse than the merge. Right? So, on top of all the sort of, you know, getting to sleep soundly at night stuff.

First, by removing the merge statement, you may also see a performance benefit. So, let’s see. This is a weird one.

I’m using SQL Server 2025 for running integration tests. No important data is being stored. And reliability doesn’t need to be 100% guaranteed. Are there configuration options on the instance or database level that can improve performance for this scenario?

Well, so, I’m going to attempt to read this signal. Read the signal that you are sending here. And that signal is you don’t care much about your data.

I believe that’s a fair statement based on what you’re saying here. And depending on the nature of the integration tests, one setting that might help you. But it’s not a 2025 setting.

It is a 2014 setting. It might be setting delayed durability to forced at the database level. If you have multiple databases, that might be something to consider. But it really does depend on what your integration tests are running.

If they’re testing like data changes, like inserts, updates, deletes, even merge. Removing merge. Not a database or system level setting, though.

If you’re writing a lot of data, delayed durability could be helpful for you here. It essentially allows SQL Server to hold off on writing to the transaction log before. Like in this, say, I’m just going to hang on to this.

I’ll make this data durable later. That might be one. But it really does depend a bit on the rest of what you’re integrating. If it’s a bunch of select queries, it would really depend on what sort of performance issues your select queries are currently hitting.

So, one important piece of this puzzle is, aside from the strong signal you’re sending. You don’t care about your data. Is what problem you are trying to solve, right?

Like, are you having a specific performance problem with your integration tests that I just need to guess? Like, what could it be? I don’t know.

Drop MSDB. Maybe. I don’t know. But, I mean, you know. I’m going to give you a chance to ask this question with a little more detail. That’s what I’m going to do.

I’m a kind and forgiving person. I’m a merciful office hours professor. I’m going to give you a chance to add a little bit more information to this question. If you’d like.

And maybe tell me if there is a specific performance problem that you are having with your integration tests. And if there is a setting that might help solve those particular problems. Otherwise, I will be here all day talking through every single potential database and server level configuration option.

And speculating on when they might help solve it. Which I do not have the energy to do. I’m sorry.

What usually causes a query to suddenly start spilling when it never used to before? Well, this sounds. Road on a limb here.

This sounds like a fairly common parameter sensitivity issue. Or. Or.

Could also be that your query. Because the data. The data in your database has changed. To some degree or another. And you have hit one of the very famous tipping points in cardinality estimation.

Perhaps. SQL Server has started choosing a different query plan. Right? Crazy things have happened.

I think one example that I can think of off the top of my head where I saw with this. Which would fit either the parameter sensitivity or the I’m suddenly just choosing a new plan all the time motif. Would be.

Let’s say your query was always using a nested loops plan with no sort operator in it. Or maybe there was a sort operator. But. I don’t know. Maybe. Maybe things were just working out well for that sort operator.

And now you’re doing a merge join. And maybe. SQL Server is choosing that merge join stupidly. Where it has to sort data from one or both inputs. To make the merge join happen.

That would be. That would be one thing. That could. It could certainly. One illustration of the problem that you were describing here. So. My.

My guess. Parameter sensitivity. Or. SQL Server just choosing a new plan. Right? Two. Two possibilities there. Do. Do.

I see memory grant feedback kicking in. But performance still stinks. What gives? Well. Maybe it’s not the memory grant my friend. There are so many other things that can make performance. It’s like.

I painted my car blue. But it still stinks. It’s a little slow. All right. Maybe it’s not the paint job. Put it.

Put some air in the tires. So. Memory grant feedback. It’s a cool feature. Mostly. You know. It’s gotten some nice revisions over the years. But.

But perhaps the problem is not the memory grant. Perhaps you need to look elsewhere. Perhaps something else in the query plan. May. May give you a fair indicator. Or a fair warning. Of what is wrong here.

But. It doesn’t sound like it’s the memory grant. It’s like. When. When people. Don’t have a single parameter. Or local variable in a query. But they’re like.

Option recompile. I’m like. Go on. Then you go. What. What. What do you. What do you. What do you think is going to happen?

Ah. Oh. Ah. Well. There we go. Why does SQL Server sometimes pick merge joins that look absolutely terrible? Ah.

You know. I’ll be honest. It’s terrible to me. I mean. Maybe not everyone. I mean. I guess. There’s. There’s some benefit to. To orderly data flowing through your plan.

But. Man. I. I. I. I hate a merge join most of the time. Ah. But. You know. It all comes down to costing.

And perhaps the. Other requirements within the plan. Um. You know. It’s like. Sometimes SQL Server will choose a merge join to keep data in order. So it doesn’t have to sort data later. And you’re like. Oh. Okay.

But. Man. God. God help you. If that’s a many to many merge join. There. There are all sorts of strange things that go on with that. Um. Yeah. Man.

I. I fail you on this one. Um. Most of the time. When I see SQL Server pick a merge join. I’m like. Some. Something went wrong. Like. Something.

Something wrong is happening in your life. SQL. Today’s SQL Server. You. Ah. Yeah. Yeah. Ought to not do that. But. You know.

It’s a lot of testing. Right. Like. You know. Just like. Or. Like. Coming back to the orderly data thing. Right. Like.

Uh. Let’s say. You have a rather large result set. And. Uh. If you did a hash join. That large result set would become disordered. And. Then you have to like. You know.

Sort that data. And now. But if you did a merge join. You wouldn’t have to sort that data again. Or. Perhaps to support a stream aggregate. Without having to sort data. Again. That would be another reason why SQL Server might. Choose a merge join.

That’s usually what I see. It’s usually wrong. About that being the best possible idea. But. That is usually. The thought process. That at least I can identify. So. Ah. Man.

I. If you. If you were. If you were here in the room with me. I would. I would hug five you. Man. I would. Um.

Um. I’ve. I feel this one deeply. Alright. Well. Before my feelings get too intense. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video.

We’ll see you then. Where we will learn. Some more T-SQL. With Eric. That’s me. Alright. 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.

Learn T-SQL With Erik: Aligning Queries and Indexes Part 4

Chapters

• 00:00:00 – Introduction
• 00:00:21 – Consulting Services
• 00:01:10 – Free Tools Mention
• 00:02:18 – Upcoming Event
• 00:03:16 – Tipping Point Queries
• 00:04:00 – Query Execution Time
• 00:05:35 – Query Plan Analysis
• 00:07:01 – Optimizing the Query
• 00:08:20 – EXISTS vs IN Subquery
• 00:09:10 – Adaptive Join
• 00:10:12 – Nested Loops Join
• 00:11:00 – Original Query Performance
• 00:12:24 – Conclusion

Full Transcript

Erik Darling here with Darling Data and today’s video we are going to carry on in our task which is learning how to better align our queries and our indexes. If you need help aligning your queries and your indexes, boy do I have options for you. You can hire me for, aside from watching these videos, you can hire me for consulting, do this stuff all day.

You can also purchase my training. The videos that you’re watching here are just tiny little snippets from the full course material in the Learn T-SQL with Erik course. The link to buy that for a hundred bucks off is down in the video description if you feel like doing that sort of thing and watching more videos of me. It’s crazy. You can also become a supporting member of the channel, ask me office hours questions, and I guess outside of the downstairs links you can also do other things that would make me think of you as a more useful human being.

Such as liking this video, subscribing to this channel, and forcing all of your friends. Hijack their browsers and force them to love me as well. If you need SQL Server performance monitoring, I got you covered.

There’s nothing Erik Darling won’t do for you. Maybe a couple of things. But this thing I’ll do for you. I would do anything for you but I won’t do that.

Anyway, I don’t like that song. Totally free, open source. You can see everything it’s doing. It’s free. It’s right out there on GitHub. It’s a bunch of T-SQL collectors.

They all run on a schedule. They collect important performance information from your SQL Server, put it into pretty charts and graphs, and allow you to talk via your robot companions using MCP servers to do that analysis on your performance data. The MCP stuff is all opt-in.

It is not on by default if you don’t want it broadcasting that it’s there. But it’s just, you know, gives you a different way of… figuring out what’s up with your SQL Server aside from just looking at the pretty charts and graphs and doing your own form of analysis.

So, all that good stuff. If you want to see me live out in the world and you happen to be in the Croatian area, I also got you covered. June 12th and 13th.

I will be at Data Saturday Croatia. I have an advanced T-SQL pre-con. It’ll be the material that you’re seeing here and more. If you come to the class, you get all of the T-SQL stuff. All of the T-SQL videos that I publish as part of the full course.

So you show up, you hang out with me for a day, and then you get like 100 hours of videos to go watch at home. But until then, let’s continue our maddening descent into heat brain leaking hell. I guess that’s what this is.

Maybe it’s just allergies. I get those too. The databases are just allergic as hell to everything. Especially users and developers. Just like me.

Anyway. We’re going to look at some interesting sort of tipping point queries. And this video is going to explore both rewriting queries to get better performance and tweaking an index to get better performance. So you get a twofer on this one.

Don’t say I never did nothing for you aside from all this stuff I already do for you. Anyway. We’re going to start by running this query. And we are going to use drop clean buffers.

Not because this one ends up terribly. Because the next one will end up terribly. So we’re just like this worst case scenario. This has a little go to after it.

So it executes twice. Even if you look in the messages tab, you will see this handy little message here. Beginning execution loop. Batch execution completed two times. Thank you. But the first query, it is a little bit slower. It does take about 1.2 seconds to run. And the second query takes about half that time.

And this is just the effective cache data. Right? And what’s kind of funny is it’s like when you look at these things, it’s like almost hard to spot where they really go astray. Like sure, this takes 60 milliseconds.

This takes 237 milliseconds. Somehow we end up at 922 milliseconds in the nested loops join. So the nested loops join did spend some extra time in there. I’ll talk about why in a minute.

But if you look down here, really the big difference in time. It’s not this, right? That’s about like 12 seconds different. That’s actually 60 milliseconds slower somehow, right? 237 to 295.

But this is at 460 milliseconds. Now part of that is because the nested loops join is responsible for a little bit more work than it lets on if you are just looking at the graphical execution plan. If you right click and you go into the properties, you will see this prefetch attribute assigned to your nested loops join.

This one just happens to be unordered. The same thing would happen if it were ordered. But this is just essentially telling SQL Server to go out and read a bunch of data ahead of time and get some extra stuff that we might need to make this query run and return stuff.

So the nested loops join here doing a little bit more work than in this one. We’ll forgive it though. But this isn’t really like the crappy one.

The crappy one comes. So this is looking through 2013-03-18. This is looking through 2013-03-19. And if we run this one, this is where things get demonstrably worse, right?

Because we have hit a tipping point when SQL Server is no longer willing to give us the query plan that we had before. It is no longer willing to do that key lookup. It just goes ahead and scans the clustered index.

Scanning the clustered index on the POST table for me takes about 8 seconds when I’m reading from disk. When I’m not reading from disk, it takes about 10 seconds. When I’m not reading from disk, it takes about 618 milliseconds.

I know which one I prefer. I also know that I’m pretty sure that I would prefer if SQL Server chose that lookup plan a little bit more reliably. How can we do that?

Great question. If we wanted to influence the optimizer to avoid the clustered index, we might rewrite the query like this, right? So what we’ll say is, again, sort of almost doing the same sort of self-join technique.

But we can just use an answer. We’ll say, just give me the top 1000 rows that would qualify for our original query. And just say where the ID from the outer POST table is in this list of IDs.

And this will influence SQL Server to use that same fast query. Use our nonclustered index instead of the clustered index, right? We’re going to go seek right into that bad boy over here.

Find the rows that we care about. And narrow it down to just the 1000 that we need to satisfy our query. And then go get the columns from the POST table via the self-join here.

And we return all that out. And that’s even a bit faster than either of the ones that we did before at 147 milliseconds. Now, IN and EXISTS often behave as far as the execution plan goes identically.

Often, right? But not in this case. When you have a top 1000 in an IN subquery, you look at this.

Again, the query plan, it looks like this. You see a top operator in it, right? SQL Server is like, oh, I need to limit this to a top 1000. If you do that with EXISTS, though, and I’m just going to get the estimated plan here.

Because if I run this query, things will not go as maybe they look here. The top 1000 is not, there is no top operator present in this. SQL Server will go and find all of the top 1000.

The rows and figure out which ones exist. The top is just ignored inside of EXISTS. SQL Server just throws that away.

It’s not valid to use top in there. So this does not turn out probably as you might expect or as you might have planned on it turning out. This would run for a long time and return a lot of rows.

Because we’re just essentially asking for everything from the POST table where the IDs exist. Even the top 1000 here and all of the rows that this would match. So we could do this, right?

But even this won’t turn out so great. What we’ll do is, no, I’m in the right place. There we go. We’ll say, we’ll put the top 1000 on the outer part of the query where SQL Server can no longer just dispose of it and throw it away and say, you’re not valid.

But if we run this, it’s still a little bit clunky, right? We’re back up to like a second on this. We had this tuned nicely with that in sub query.

If we’re not in a place where SQL Server might use, I should probably stop here for a moment. We get a batch mode adaptive join for this query, right? So good for us, right?

We’re on developer edition. So we’re getting that enterprise edition class for free. That’s cool. But we get a batch mode adaptive join here. SQL Server has chosen batch mode for the query.

And it said, well, I’m going to figure out. The best join strategy based on, at runtime, how many rows come out of one thing or the other. And then I will choose the correct join type based on how many rows leave here.

Great. You may not always get that. If you don’t always get that, you will most likely end up with a hash join here. And the hash join takes, on its own, just about the same amount of time.

Most of the stuff in here does still run in batch mode on rowstore. So you’re still getting just about the same improvement. Just without the join choice at runtime.

The join choice at runtime doesn’t add anything bad here. But it doesn’t add anything good here either. Batch mode makes this thing, like, still okay, but not where it was before. We did a much better job.

We could also force a nested loops join here if we wanted. And we could get down to an okay amount of time. But still 678 milliseconds.

That’s not really what we had before. If you recall. It was several queries ago with our beautiful in clause query with the top 1000 in it. This all ran in 135 milliseconds.

So that’s really more the time to beat. Everything is 600, 800 milliseconds. That’s a regression. It’s not a huge one. But, you know, it’s not really one.

We don’t tune queries to make them regress, do we? We tune queries to make them faster. It’s a crazy concept, I know.

Now, one thing that I want to point out is kind of funny about the array. The original query is… And all of the other ones are ordered by elements. Yeah.

Mouthful of marbles. Are creation date and then score descending. If we just run this query ordered by creation date and score, no longer descending on the score column, our original query still runs really quickly.

Actually, it runs faster than ever. Interesting. Well, we spent a lot of time rewriting this query to sort of have it suit the index that we had available better. But sometimes, every once in a while, you might be able to change an index.

And if we change our index definition, or rather we’re going to create a new index, I guess, to creation date and then score descending, so this fits the query that we were writing, better suits the query that we had originally, then we get the same fast execution as we did when we changed our query.

So, sometimes there are ways to rewrite your query to better suit the indexes that you have. Other times, if you have options and choices, you might choose to change your indexes up a little bit so that they better suit the queries that you have.

All right. I reached the end of the file. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you next week on Tuesday for Office Hours.

All right. Have a great weekend, everybody.

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.

Learn T-SQL With Erik: Aligning Queries and Indexes Part 3

Chapters

• 00:00:00 – Introduction
• 00:02:15 – Index Alignment and Design
• 00:04:33 – Query Tuning Magic
• 00:06:54 – Parameter Sniffing Issue
• 00:09:40 – Execution Plan Variations
• 00:11:55 – Relational Columns and Aliases
• 00:14:28 – Optimizing Queries
• 00:17:14 – Memory Grants and Indexes

Full Transcript

Erik Darling here with Darling Data, and we’re going to continue our endeavors to learn T-SQL, and in our T-SQL learning endeavors, we are going to continue looking at how we can better align our queries and our indexes. This video is actually one of my favorite demos. It’s a fun one, and it’s a good sort of mental exercise when you’re tuning your own queries and perhaps wondering why SQL Server sometimes uses your indexes and sometimes it doesn’t. So, down in the video description, you will see all sorts of helpful links. Of course, because I am a helpful human being, arguably one of the most helpful human beings to have ever walked the face of the planet.

And humble to boot. And down in the links, you will find a way to buy the full course content. All of the material that you’re seeing around the index align and design stuff is part of my Learn T-SQL with Erik course. There’s a link down in the video description where you can save a hundred bucks off the course if you buy it from there.

You can also do other things that would make me happy, like you could hire me for consulting. I can bring that up. I can bring the same query tuning magic, magic energy to your SQL Servers.

You can choose to support this YouTube channel if you feel like the content is helping you in some way that is worth money. A few is four smackaroos a month. It’s USD.

That’s American for dollars. You can do that. And you can, of course, ask me office hours questions. And if you happen to just like what I’m doing, like what I get up to over here, please do like, subscribe.

And tell every single one of your friends, whether they use SQL Server or not. Perhaps they will find other reasons to come to this channel and watch me. Maybe they’ll just be casually entertained by the shape of my head.

You never can tell. If you are in the market for free SQL Server performance monitoring, my free monitoring tool, totally open source, no sign up, no weird telemetry. Just all the stuff that you would care about monitoring if you want to figure out why SQL Server performance maybe is good, bad, ugly, somewhere in between.

Got a cool knock style dashboard. If you want to just make like just do a little sanity check, make sure all your servers are up and running and all that good stuff. And if you’re a fan of today’s robot companions, I don’t know, maybe before the prices go up, you can also use those things to do some built in MCP stuff.

And you can do some MCP server analysis of your performance data. And it’s isolated to just your performance data. It’s not going out and running weird queries on your SQL Server to look at stuff.

So, coming up in, wow, that is like, it’s like, I mean like 10 days away. I will be at Data Saturday Croatia, June 12th and 13th. And I have an advanced T-SQL pre-con there.

You can, you know, jump in there. Learn about that. Learn about T-SQL for me live and in person. And if you show up to that, you get free access to the Learn T-SQL material that we’re covering today in this video.

I will also be at PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So, you know, not quite on my birthday, but pretty close there. And, but for now, it is June and we are Juning about going crazy from the heat.

I mean, I’m not going crazy from the heat. It was like 60 degrees in New York today or something. I don’t know, whatever.

Anyway, let’s go learn something about SQL over here. Ah, that’s Management Studio. Now we got it. So, like I said, this is one of my favorite demos. I’ve got a few different things that I build off of this one-store procedure.

But right now, we have got this computed column that I’ve added to the post table. And we’ve got this index that I’ve added, which I know there’s a little red squiggle here, but I promise you this index has been created and the active period column is there.

So, anyway, I don’t know why that was highlighted. We’ve got this store procedure called top lookup. And this store procedure is doing something.

I mean, okay, so like, look, we’re doing select star here. I’ve talked in other videos about how select star is just a convenient shortcut. For a lot of this stuff.

You could list out all the columns if you wanted, and it would still be the same thing. Or you could even just put a few columns that aren’t in your index in here. And you would run into the same potential plan switcheroo.

Because no matter how many columns your nonclustered index is missing, SQL Server has to do lookups to find those columns from the clustered index. And it costs one column the same way that it costs 150.

1,024 columns. It doesn’t matter how many columns are outside of your index. The key lookup costs the same.

Regardless of the number of columns or the data types of those columns. So, I’ve got a hint on this query. And that hint is to set it to compat level 140. It’s not because, I’ll explain why.

The parameter sensitive plan optimization that came around in compat level 150 just makes this demo really confusing. And I’ll talk about why in a moment.

But for now, just understand that this procedure takes two parameters. One of them is post type ID and the other one is gap. The gap parameter works off the computed column.

This date diff year, creation date, last activity date. That works off the computed column and index that I created up here. So, let’s turn on actual execution plans.

Let’s feel fully actualized here. And the reason why I have the compat level set is because it sort of makes this demo it just muddies it up a bunch.

What happens is the parameter sensitive plan optimization kicks in. And you get one plan for the most common value. One plan for the least common value.

And then all of the post type IDs in the middle share the same plan. And it’s just really a bad situation. And it just makes it harder to explain the point of what I’m doing here.

So, to just sort of visualize that breakout if I run this query and we get these Oh, I scrolled the wrong way. We get these results back. Go away SQL prompt.

Come on. Be a pal here. We look in here. So, just to sort of explain a little bit. You get the three plan variants. Post type ID 2 at 11 million rows gets plan variant 3.

Post type ID 8 at 2 rows gets plan variant 1. And all of the post type IDs in the middle ranging from 4 rows to 6 million rows get the second plan variant.

This is not a good situation. And looking at these numbers you may start to understand why that particular feature muddies this demo up quite a bit. So, we’re not going to do that.

Now, the first thing we’re going to look at is the 9-year gap. So, the 9-year gap is very uncommon. Gap ID 9, post type ID 1. And we run this.

This returns very quickly. We get this plan here. It is a parallel nested loops plan with a key lookup to fetch all of the columns that we care about from the post table after we seek both to the post type ID that we care about and the gap years parameter that we care about.

The key lookup down here, there’s no predicate on that. If I move my big head out of the way, there’s no predicate on that up here. It is just outputting columns, right? But it’s outputting all the columns in the post table, which maybe isn’t a lot of fun.

But this takes 28 milliseconds to run and it gets 500 rows and everyone’s pretty happy. If we run this for a gap of 0 years, most posts do not have 9 years of time between when they were first posted and when they were last edited.

A gap of 0 years is much, much more common. And this query runs for about 4 1⁄2 seconds here. Excuse me.

Very dry. 4 1⁄2 seconds. And a lot of the time is spent in this sort that spills because the memory grant that we assign to the initial execution of this procedure maintains for this execution.

So that takes some time. Now, if we recompile this thing and we run this in reverse and we ask for a gap of 0 years first, this does a bit better, right?

About twice as fast plus another 500 milliseconds faster for a gap of 0 years. The plan changes quite substantially, though.

I mean, we still have a plan for a parallel nested loops join plan. But notice that we fully scan the clustered index over here.

And then we have this strange filter operator over here. And the filter operator is on both the post type ID and the gap parameter. Part of the reason for this is because the computed column that I added to the post table was not persisted.

SQL Server expands the definition of the persisted computed column. And if I were to change that to a persisted computed column, we could avoid this part.

But we would still get the same basic plan shape. Now, I think probably the biggest downside of this late filter operator, and if I’ve said it once, I’ve said it a million times, always be suspicious of a filter operator in a query plan, is that we have to fully scan all 17 million rows from the post table, drag them across the couple compute scalars, and then filter stuff out over here.

Okay? What really sucks is that reusing that plan for the gap of nine years, in other words, the very uncommon one, this used to take 28 milliseconds, and now this thing takes almost a second.

Right? And it’s obviously the same plan gets reused. Whenever I’m talking about parameter sensitivity problems, a lot of people get this big idea in their head. It’s like, well, why not just use the big plan for everything?

The big plan for small amounts of data is often somewhat, I mean, not surprising to me, but surprising, not surprising to other people who see this stuff, not a good sort of trade-off there.

It’s not a good fit. So we have, you know, essentially two execution plans. Neither one is a particularly good fit for the amount of data that we’re dealing with. So the mental exercise that I like to put people through when we’re doing this is to mentally in your head separate informational columns from relational columns.

Right? And what we’re going to do in the query below is we’re going to write a self join between the post table and itself. Right?

One alias will be responsible for the relational activities in our query, the join, the where clause, the order by, stuff like that. And the other alias will just be responsible for providing the select list.

Right? And if you can start mentally separating the duties of your queries and the columns in your queries between purely informational stuff that’s only in the select list and columns that are used for relational activities in your queries, you can do a lot of cool query tuning stuff.

This is just one of them. Right? So let me create this and then we will talk through the code just a little bit up here.

So I have the post table joined to itself. ID is the clustered primary key in the post table so we can get away with this. It is a unique column so doing this is very, very easy.

And from P1, right, that’s this one, this is all of the relational stuff. Right? P1 is there.

P1 is there. P1 is the where clause. P1 is further down here in the where clause and in the order by clause down here. The only place we reference P2 is up here in the select list.

Right? This is our star. Right? So if we run this query now, both 9 and 0 will be fast doing this. Right?

The 1 for 9 got even faster. The 1 for 0, instead of taking 4.5 seconds, takes 1.2 seconds. Right? So we’ve kind of come to a little bit better of a situation here.

Now, we still have this sort over here and this sort still spills. Right? So, you know, it does slow this query down a bit but we’re not at 4.5 seconds of sort of crappy spilling.

We’re at one point, actually it’s about 900 milliseconds of crappy spilling there. So this is a lot more tolerable. If we reverse things, just like we did before and we do 0 first and then 9, the plan actually gets a little bit better.

So in this case, the first query, not only does the sort spill a bit less because we get the memory grant for the larger amount of data that comes out of the POST table, but we didn’t finish in about 200 milliseconds.

Now we also get this parallel nested loops plan. And the same thing goes for this gap down here. So this is a pretty reasonable rewrite to get better performance out of both queries.

You might start playing some other tricks with this query if you really were getting, if you really wanted to like optimize, optimize this, you could even say like option use hint optimize for gap equals 0.

So you keep getting that plan for the 0 value and the gap parameter. And talk through some of the important differences between the original and the rewrite.

I’m going to put both of them into the same store procedure. And then I’m going to run them for the gap of nine years. And the thing that I just want to talk about here a little bit is the plan shape.

So what happens in the original query where we do the lookup is we find 4,500 rows here and we do 4,500 nested loops to do the key lookup here.

Key lookup plan, key lookups and query plans are very, very tightly coupled operations. SQL Server cannot move these around, right? SQL Server has to do this stuff all in one place at the same time.

It can put a sort before the nested loops join, it just doesn’t here. And then after we find all that data, then we do our sort. And this is where we start to sort of narrow stuff down for the top, right?

Down here in the one that we rewrote in order to, what do you call it, do the self join.

We still have the same seek, but notice that the post table immediately joins to the users table here. And what’s nice is that this sort cuts down the results to just about all the ones that we need for the top very much earlier on, right?

And then after we figure out relationally what rows we care about maintaining for this query to get the top 500, we do our nested loop.

We do our nested loops for only those 500 rows back to the post table here. And again, there is about a 20 millisecond difference between these. This isn’t big night and day performance tuning stuff, but you do see a general improvement.

And what I think is nice too is you see that general 20 millisecond improvement with the serial execution plan. In other words, you don’t need to get a parallel plan here in order for this to be competitively fast, right?

This plan up here, it runs, goes parallel, gets a DOP eight query plan. And this is one of those like, oh, well, you’re using a bunch of extra resources, but your query’s not getting faster, right?

It’s kind of a weird thing. Anyway, when you’re working with queries, especially parameter sensitive ones, one of the biggest differences that you will see in those queries aside from stuff like the type of join and the size of the memory grants and all that other stuff is going to be the sort of like index usage, right?

And if you can’t get SQL Server to reliably use your narrower nonclustered indexes because you are selecting columns that are outside of them and SQL Server now has this clustered index for its key lookup choice, it might just totally be worth rewriting the query to do a self join so that you can get all of your relational work done that narrows the rows down to just the ones you care about.

And then do the self join back to get the stuff later because the key lookup, again, very tightly coupled. When you write a self join, that tight coupling is sort of removed a bit.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk some more about very similar techniques. All right, 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.

SQL Server Performance Office Hours Episode 67

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:04:08 – Memory Grants and Sorts
• 00:07:59 – Suspicious Plan Operators
• 00:10:26 – Batch Mode vs Row Mode
• 00:12:19 – Conclusion

Full Transcript

Your best friend, Erik Darling, here with Darling Data, and we have another absolutely thrill-filled episode of Office Hours For You. This is where I answer five SQL Server user-submitted questions about anything you want to ask about. It doesn’t have to just be SQL Server. If you want to know anything about me, my life, aside from SQLs, I mean, I know that, like, you know, it’s cool to get free advice from someone who charges for advice about SQL Server, but, you know, if you ever just want to get to know me better, the human Erik Darling, you can ask other questions.

Anyway, my hair looks weird today. I don’t know why. It looks lumpy for some reason. I can’t quite figure that out. Ah, I’m not going to get over that. Anyway, we’re going to keep rolling here. I’m going to try to contain my embarrassment at the shape of my head, and we’re going to soldier on here.

If you would like to hire me for consulting, buy my training, become a supporting member of the channel, or continue to ask me Office Hours questions, all of the links to do that are down below in the video description. Every single link you possibly need to achieve any of these goals is down below.

And of course, if you would like to help my channel gain a larger audience, then please do like, subscribe, and tell every single one of your friends to do the same. If you would like free SQL Server monitoring, boy, howdy, I got it.

I’ve been doing a lot of internal work on that thing lately, not a lot of user-facing stuff, but stuff to make my life easier and maintaining the project, hopefully. And I am working on some exciting new stuff, which will all be revealed in short order.

But for now, if you want to download the current implementation of things, you can go to my GitHub repo. Again, this link is all down in the video notes. Video description, rather.

Totally free, open source, no strings attached. You download it, you point it at a server, you start getting useful performance information. I recently hit the 10,000 download mark, which I am very happy about. The emails about people divorcing their paid monitoring tools and using this instead have been steadily coming in, which of course brings my heart great joy and glee, because there is no reason that people should be paying for such…

much junk these days. Anyway, out in the world, Data Saturday Croatia, steadily getting closer. Boy, oh boy, just a couple of weeks away at this point.

And then I guess I’ll be doing nothing for a little while unless something interesting comes up. And then, of course, there is PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So we will have a joyous time there.

And since this will be publishing… in June, we have switched to the June image in which our database is including this faceless wonder over here. I don’t know where your face went, database, I’m sorry.

I tried to… I forget what the prompt was, but the end result just made it look like a bunch of databases were, like, in that movie Midsommar having, like, a real bad time in a field somewhere.

Yeah, I think they’re all going heat crazy or something. Let’s just leave it at that. Sort of some strange leg situations over here.

I’m not sure what happened with this one. It’s like some sort of appendage there and then over here gets a little dicier. I don’t know.

Anyway, let’s answer some questions before I get too involved in the analysis of that picture. Because, you know, try not to judge art too harshly around here. Anyway, let’s see.

First question here. How do you decide between a store procedure versus a function? Versus a view for things like WhatsApp blocks and SP Quickie store? Well, the store procedure one is easy. If I need to have a variety of parameters and variables and temp tables and loops and all sorts of…

And error handling, dynamic SQL and stuff, then I need a store procedure. I think for most other things, I would prefer to write an inline table-valued function. But I guess the design choice there is maybe…

Again, I know I have, like, WhatsApp blocks, which is a table-valued function. And I think the reason that I chose that there is because there were a very common set of parameters that I would usually pass into that.

And it was easier to sort of just remind myself of them in the definition there. I do have one that is a view called WhatsApp memory. And since I didn’t find myself often filtering on things in there, usually just, like, show me everything and sort it, I just sort of, I just left that as a view.

But there wasn’t, like, I wish that more people would use inline table-valued functions instead of views because they offer sort of better placement of parameters and stuff. So most of the time, I would prefer to not use a view because, I don’t know, then it’d be one of those view people that everyone yells at.

Anyway, when there are multiple seeks on an index, does that suggest that the index gets seeked once per seek key? Yes, I have a couple of videos about that.

It is called multi-seeks or dynamic seeks. And that does indeed mean that the index gets seeked through once per seek key, which can hide a lot of work that you may otherwise avoid with just a single seek through the index.

So careful on that. How much do sorts actually hurt? And when should I worry about?

How much do sorts actually hurt? And when should I worry about? Well, they can hurt quite a bit. Sorts are what’s referred to often as a size of data operation because you must write down all of the columns that you are selecting in order of the columns that you are ordering by.

And over a large enough result set, that can get pretty… turn out to require quite a lot of memory to do. You sort of don’t want to end up in a situation where you have very, very large memory grants stealing lots of memory away.

So that’s why you want to keep your memory away from your buffer pool, because you know, like I said in many videos, most servers that I look at are undersized from a memory perspective. And you know, they need a lot of help to sort of balance the data to memory ratio out.

But as far as when sorts start hurting, well, you know… So let me go through this in a little bit of mental detail here. So obviously large sorts are the obvious one that you would want to track down and sort of look at, right?

you can look at the plan cache or a query store by the size of a memory grant that a query is getting. The plan cache, for some reason, offers more details about how much of the memory grant the query actually used, but you can still look at the size of the memory grants in both. You would look for anything using a large grant and see if there’s a sort operator in there.

That being said, in versions of SQL Server older than 2019, where you don’t have the in-memory TempDB stuff available to you, even small sorts could hurt and cause TempDB contention. I saw this on servers where there was a little execution plan, and the sorts would sometimes get an undersized memory grant and just spill a little bit, but if this query ran a ton altogether concurrently, then all of those sorts would cause TempDB contention. If you’re seeing that, then…

You could address that either via indexing, maybe if you’re on 2019 plus the in-memory TempDB metadata feature helps quite a bit with that, but large sorts are a bit easier to pin the tail on that donkey. The small sort TempDB contention thing, that’s a very high concurrency problem that you might have to deal with. I think in general, I’d probably just look for the big sorts first because those are the ones that are most likely to cause issues with the workload because, A, they’re going to be stealing memory from the buffer pool, and B, refilling that buffer pool space, knocking a bunch of data pages out of the buffer pool, bringing them back in later.

That’s going to cause a lot of variance in your query performance. What plan operators instantly make you suspicious something is wrong? Well, in select query…

I would say spools of any variety make me think, I could do something better here. But very specifically, the eager index spool or the lazy table spool, aka the performance spool, those are two things that give me pause on every single occasion when I see them. Again, not because the spool is evil. The spool is just trying to help. It’s Microsoft that’s evil because they haven’t touched the code in spools, and since SQL Server 7, not 2007, SQL Server 7, not 2007, SQL Server 7, that is the 90s, and so spools have not benefited from any of the sort of improvements that like other temporary objects that when in tempdb have had like temp tables and table variables and stuff.

So spools are one of them. In a modification query of any variety, spools are usually there for Halloween protection, so they don’t catch my eye as much. There’s those. I think the sort of constant scan, like merge concat, like sort into a seek thing, that’s one that always catches my eye because that usually, that either means that you have a join with an or clause, or maybe you have like a mismatch data type, like you’re comparing dates and date times. Like maybe you have like a date time column or a date column, and you’re comparing it to a non-precise match to a date time column.

Or a temporal data type, like a date or a date, right? So I guess I would go for date time too as well. That’s another pattern that I look for. Parallel merges, parallel merge joins, that’s another big one, stuff like that. So top above a scan is another pattern. So it’s not often a single operator outside of spools. Often it’s a pattern of operators that I see that make me suspicious.

Is batch mode always better, or are there workloads where row mode wins? For me, batch mode is really only better when you have a lot of rows that you need to do something with. For me, batch mode is not necessarily better if I’m doing very tiny OLTP-ish things, little seek point lookups. There’s just not a lot of benefit to batch mode in there.

You know, batch mode used to unlock a lot of extra optimizer stuff that row mode has slowly… been inheriting. So for me, no, batch mode is not always better. Batch mode is a very specific optimization for large scans, aggregations, hashes, hash joins, hash aggregates, stuff like that.

You know, I guess the adaptive join thing is nice, but you know, if you’re just working with like sort of reliable OLTP data where there are not large skew-sensitive portions of the data, then batch mode doesn’t really offer all that much as far as, you know, benefit goes. Anyway, that is five questions. One, two, three, four, five. I did it. Counted right this time. Good for me. Five fingers, five questions. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we’ll talk a bit more about aligning and designing indexes and queries. All right. 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.

Learn T-SQL With Erik: Aligning Queries and Indexes Part 2

Chapters

• 00:00:00 – Introduction
• 00:02:45 – Problematic Query Analysis
• 00:06:38 – Index and Predicate Issues
• 00:09:21 – Optimizing the Query with Outer Apply
• 00:11:24 – Improved Performance Explanation
• 00:12:55 – Conclusion

Full Transcript

Ahem, Erik Darling here, Darling Data. In today’s video, we’re going to continue talking about query and index alignment. This one’s kind of in the same vein as yesterday’s video, but with a little bit of a twist to it. You know how I love keeping you emotionally hostage? Just kidding. So, again, more sort of aligning queries and indexes. It’ll be fun. Trust me. This is, of course, all material from the Learn T-SQL with Erik course. This is just little dribs and drabs of it to get you excited and entice you and force you to buy things from me, because in this consumerist society, that’s the way the world spins, I guess.

But down in the video description, you’ll find all sorts of helpful information. You’ll find all sorts of helpful links, including a link to purchase the full course material. It’s a great course. You should check out the full thing sometime with money. That’d be nice.

You can also find other ways to spend money on me. You can hire me for consulting. Maybe you think, oh, wow, he sure seems to know what he’s talking about. I wonder if he could come know what he’s talking about on our SQL Server. The answer is yes. Yes, I can.

You can become a supporting member of the channel, too. If you think that the things that I do and say and talk about here are helpful to you, and talk about here are just so outstanding and wonderful that you want to give me four bucks a month, and you want to take part in the glory and the greatness of this YouTube channel, maybe finally outdo that Amiga repair channel, you can do that.

Other stuff that there are links for, asking me office hours questions to do every Tuesday, answering five of them. And of course, if you feel strongly about the content, the content here, but perhaps you’re irresponsible with money in many other ways, and you can’t afford any of the paid stuff, you could always like, subscribe, and tell a friend, which has value of its own.

If part of your financial and your fiscal irresponsibility is spending too much on SQL Server monitoring tools, well, golly, I can help you out there. Boy, can I save you a pretty penny.

I’ve got a free monitoring tool. It’s up on GitHub. Again, the link for all this stuff is down in the video description. Totally free, totally open source performance monitoring. It does all the stuff you would expect a monitoring tool to do, except it’s written by someone who actually looks at SQL Server performance for a living, not someone who has never done that in their life, which is a problem a lot of other monitoring tool companies have.

So, you ought to check that out, shouldn’t you? Getting real close to 10,000 downloads, so I’m feeling like credibility is in the… I’m out in the world a little bit.

June 12th and 13th, I will be at Data Saturday Croatia. And November 9th through 11th, I will be at PaaS Data Summit in Seattle, Washington. At Data Saturday Croatia, I have a pre-con on Advanced T-SQL.

You might even find some of this material that we’re learning about today is in that course. And you might even find that if you show up to Data Saturday Croatia, you will get free access to the full course material if you come into my pre-con.

PaaS Data Summit, a lot of unknowns there so far. Who knows what’s going to happen? It’s going to be crazy. But anyway, we will continue making our way through May somehow, some way.

So, I’ve got these indexes, right? I’ve got one on the badges table on user ID and date. And these will all make a little bit more sense when you see the query.

And I’ve got this one down here on the comments table on user ID and post ID. And then I’ve got this index in here. And if you remember yesterday’s video, we almost had the same index except post ID and owner user ID were kind of swapped around there.

Or rather, owner user ID was at the beginning of the index, post type ID was second. We’re going to deal with a very similar query, but now we’re going to have to figure out a way to take better, to rewrite our query to take better advantage of this index.

I think Joe Sack had a great blog post some years ago. It was called like the gatekeeper problem. And we have a gatekeeper in here because when you create a roadmap, you have to set up a post store indexes.

We had like the ordering of the columns in those indexes is of course, like in like the way that queries can access data in those indexes. This is of course defined by the order of the key.

So like if we want to like do a search on post type ID something, we have like the immediate access to that data ordered in this index here. And if we wanted to search on post type ID and score, well we would have post type ID in order.

in order, and then we would have score in order for any duplicates in post type ID. So this would line up those two things pretty well. But as soon as you get to like wanting to do things like just search on score or search on score and owner user ID, the ordering of the index no longer benefits those searches as well, because we’re not first accessing queries by post type ID in order to sort of maintain the B-tree traversal that you get when you use those types of indexes.

So this query used to be a lot worse when I first wrote it. It was like 2000, well, it was on SQL Server 2017, and it was on a much worse laptop. So I need to play a few tricks here to maintain the nostalgic feelings that I have about this demo, because I truly love this demo.

So we’re going to hint things back in time a little bit. We’re going to tell SQL Server to use compat level 140. That was the 2017 compat level. And we are going to tell SQL Server, you can only run at max.4, because that was what my old laptop sort of permitted, right? So all that out of the way, let’s look at the query plan for this.

And it runs for about 4.2 seconds total. And the majority of that time is spent in one branch over here, right? So 4.2 seconds total, and 4.1 of those 4.2 seconds is spent in this branch, right? We can see 4.1 seconds ending up there.

The branch that this is hitting is, of course, the one where we are trying to find, let me go back up to the query and make a little bit more sense to do that. This thing right here. Now, there are going to be lots of times in your query tuning life where using a temp table is going to be beneficial, right? And so when I was writing this demo, one of the things I experimented with was using a temp table. So what I did was I took all the query that were already fast, and I was like, well, I’m going to put all you into a temp table, right? And that happens pretty quickly, right? That’s 47 milliseconds, right? So no complaints there. But then I was like, now, of course, with those 740 rows, right, in a temp table, right, 740 rows materialized, stabilized into a temp table, SQL Server will have to, this has to be faster. SQL Server will have to do something better or smarter here, make better choices, do something.

Do something helpful, but no, it actually slows down a little bit, 4.7 seconds. I guess if I ran this a few times, it might alleviate, but we’re not going to mess with all that. But again, that whole, like the whole problematic branch is over here, right? So all this stuff going on in this chunk. And again, it’s the same thing over here. So the problem that we really have is that our query, or rather the index that we have is on post type ID, score, and then owner user ID.

Right? So we can’t sort direct, rather, we can’t seek directly to post type ID and owner user ID because that score column is in the way. So in yesterday’s, yesterday’s video, I showed you a rewrite using top one and max and stuff. None of those rewrites here are terribly effective. In the full video, I go into all the ins and outs of why, but rather than the full course material, which is available for purchase, I go into all the ins and outs of why, but here, if we get this estimated plan, the, like the, the, the reason why kind of becomes a little bit more obvious, right? And if let’s move this over here, so my giant head isn’t in the way.

And if we look at the tool tip for this, you’ll see that like, and one of these branches where we’re seeking to post type ID one, but, but then we have this residual predicate over here on owner user ID, right? So because that score column is in the way, we can’t get directly to owner user ID.

We can get to the post type ID. We don’t care about, but then the score column is like, well, no, I don’t think so. I was like a sassy little thing and saying, no, you can’t seek directly to post type ID and owner user ID here.

You are forced to go through me, right? And pry that owner user owner user ID out of my cold, dead data. So one thing that you can do in order to take better advantage of this index is just considering what we want from this query, right?

So it’s a little bit. Yeah. Yeah. More clear. If we go up here a little bit, we want the top score, uh, from the population of post type IDs one and two, right?

So it doesn’t matter if it’s post type ID one or two, we want you to, we just want their highest score, right? Question or answer. It can be the highest score.

What we can do is we can, instead of writing the query with a single outer apply, where we union all both of these things together, what we can do is we can change this query. A little bit so that we use two outer applies and we find the, the, the top, the top, uh, score, uh, first for post type ID one, and then we use that score to act as an additional filter for post type ID two, right? So, uh, what we’re going to do is say, Hey, you know what?

We just found this top question score. Let’s go find the top, uh, sorry, the top. Yeah. The top question score, let’s go find that top answer score, but we’re going to use. The score that we found for questions to filter out and say, you know what?

Maybe we don’t need all of the, we can use this, like to sort of seek to some scores that we care about because we know what the top, the top question score is for this user. We can pass score down a little bit and allow it to act as an additional filter. So this is how we’re going to rewrite this query.

We have the first outer apply right here. Uh, that’s going to find the top one, uh, score for questions, right? Ordered by score descending. Right.

Correlated to owner user ID, just like before, but then down here, we’re going to add in this new thing and we’re going to say only go find me, uh, answer scores for that person for when the answer score is higher than the question score, right? So we’re using this, we’re giving it, we’re adding this additional predicate down here so that SQL Server can better traverse that B tree index from post type ID to score to owner user ID. And if we do this.

Uh, this finishes just about instantly. Now in this first branch, and I, again, I go into this far more and far more detail in the full material, but we have this first branch up here, right? This takes 217 milliseconds.

This one still has the same problem, but this one isn’t really the, the, where we get the big performance at the big performance hit that we get, uh, is on the, the one for post type ID two, right? Cause post type ID one, there’s about 6 million rows for that post type ID two. There’s like almost 12.

million rows for that, but down here in the second branch, right? So like this, this index seek here, you can see this is where we’re finding, uh, post type ID one, right? And then for this second branch, now this looks a lot different.

We have two seek predicates here, right? And this is not the same as having multiple seek keys. I know in another video I talked about like multi seeks and dynamic seeks.

This is not the same thing. Uh, we only have seek keys one here, but now you can see that we are correlating. On, uh, uh, post type ID two here, and we have this additional filter to say where score is greater than expression 1 0 0 3 expression 1 0 0 3 is of course the score column that we found from this first top one query out here.

So finding the top one question score, and then using that as an additional filter in this outer apply to say only give me answer scores that are higher than the top question score. We give SQL Server a better way. Use the index that we already had.

All right, cool. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you next Tuesday for office hours. 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.

Learn T-SQL With Erik: Aligning Queries and Indexes Part 1

Chapters

• 00:00:00 – Introduction
• 00:01:01 – Helpful Links and Services
• 00:02:01 – Free SQL Server Performance Tool
• 00:03:43 – Current Indexes Overview
• 00:05:04 – Query Analysis and Challenges
• 00:06:12 – Index Seek Improvements
• 00:07:22 – Cross Apply for Better Performance
• 00:08:13 – Execution Plan Breakdown
• 00:10:04 – Final Optimization Steps

Full Transcript

Erik Darling here with Darling Data and today’s video we are going to continue our learning T-SQL journey here and we are going to spend we’re gonna spend a few videos talking about how you can align your queries and your indexes for better performance some videos will change indexes some videos will change queries but at the end of the day we will have a fucking good time I think and we’ll all learn a lot and I’ll convince all of you to buy the full Learn T-SQL with Erik course that’s the plan anyway right I’m just gonna fight you off in droves all the all the sales we’re gonna make here down in the video description is where you can find a link to purchase the very course that this this material is a very small portion of a very tiny little itty-bitty part of the world you can also find other really helpful links in there to help you achieve all of your goals and dreams in life you can hire me for consulting you can buy this training and other training too I’ve got other stuff if you don’t like you know you don’t like these more whatever you can also become a supporting member of the channel if you would if you would feel that generous and you would like to share four dollars a month with me you can ask me office hours questions that is absolutely free every Tuesday I answer five of them from whoever sends him in and of course I do appreciate this channel’s stunning massive unprecedented growth someday I’m gonna have twice as many members as that amiga repair channel I think but if you would be so kind as to Like subscribe and of course tell a friend about the wonders and glories that take place here on the darling data channel that would be cool too I’m If you’re in the market for something free, and that free something happens to be a SQL Server performance monitoring tool, I’ve got one. Isn’t that crazy?

Totally free, totally open source, no email to sign up, no phoning home with any weird data about what it’s doing, what’s going on in your servers. It’s just a bunch of T-SQL collectors that run, grab the right stuff, all this important stuff for monitoring SQL Server, all this stuff that I care about and dig deeply into when I’m doing a performance analysis on a SQL Server.

And it’s got built-in MCP tooling as well. So if you choose to enable the MCP servers and the MCP tooling, you can have your robot companion friends look at your monitoring data, and just your monitoring data, in order to see how things, how things look, and give you some analysis of what has been collected.

Coming up in the very near future, I will be at Data Saturday Croatia, June 12th and 13th. And then, unless something magnificent happens, I will be home making these videos for the masses until November 9th through 11th, when I will be at PASS Data Community Summit in Seattle, Washington.

But with that out of the way, let us continue our romp through the month, the month of May here, and let’s talk about how we can change queries to better fit the indexes that we have in place.

And how that will, of course, make them go faster. Ah, crazy! Look at that. You can do all sorts of fun stuff. So these are the two indexes that we currently have.

This index is less important. The index that we are going to focus on aligning our query better with is this index right here. It’s on the left side.

on the post table and it is keyed on owner user ID, post type ID, and then the score column in descending order. So we can pretend that these indexes exist for any of the number, any variety of reasons that indexes often exist in databases.

Perhaps they were there for some other query and a new query that we wrote just happened to be picking up on them. So what we’re gonna do first is run this query. And the part of this query that we wanna focus on is kind of what’s going on here first.

So this is a derived join. And one of the limitations of derived joins is that they can’t, they are not aware of anything going on outside of them.

Just as an example, if I tried to tell this thing anything about the users table, like if I tried to say p.ownerUserId equals u.id, SQL Server rather, we would get this little squiggle over here and this thing would say, I have no idea where this ID column is.

I don’t know what you’re talking about. Stranger danger. That thing doesn’t exist. We have to wait until we get out here for that to get picked up. So what happens quite often when we do this is that, well, SQL Server, like, you know, even though we have this wonderful index leaning on ownerUserId, SQL Server does not find a way to seek into the ownerUserId column when the query is written like this.

And so we end up with a query. And this is the compatibility level hint here. In the full training, of course, there are some comparisons that are not, did not make the cut for this video, but that’s what that’s there for.

But also to just make sure that we get something relatively batch modey. And because this query executes in batch mode, it did not run for one millisecond. That is just how long the sort ran for.

If we come all the way over here, we will see that we spent three seconds scanning the post table with a single thread, right? There’s no parallelism going on in this plan, right? 3.051 seconds.

I can move that over a little bit so that my big head isn’t in the way so much. So obviously, you know, this is a query. We want it to go faster. How are we gonna make it go faster?

What are our chances? What are our options here? Well, one way to make this query go faster might be to change from using a derived join to using a cross supply.

Since we are, since the original query was written using an inner join, this will be using cross supply. Cross supply is like an inner join. Outer apply is more like an outer join, left outer join for being very specific.

But because cross supply is sort of like a wonderful mix of a for each loop and kind of like a sub query, inside the cross supply, we can actually reference that ID column from outside, right?

And when we do that, SQL Server is able to make better use of the index that we have. So I didn’t run this yet.

I just want to come back to this. So here notice like this, we are scanning this index, right? When we hit this index, the predicates that we’re applying are on post type ID. And over here we are filtering out to where, well, we have the row number function filtering out to where row number equals one.

But we also have a bitmap that got pushed down a little bit. Not all the way down here because it’s a mixed, you know, it’s batch mode on rowstore. so we can’t do all the nice stuff that we do with normal bitmaps.

But here we have a bitmap that’s also filtering out owner user IDs. Now, coming back to our cross-apply query, one thing that’s kind of nice and one thing that kind of shows us how SQL Server treats these differently is that we don’t need to have the partition by owner user ID here because it’s implied in here on the correlation, right?

Up here, we needed to partition by owner user ID and stuff, right? Down here, we don’t need to do that, right? So if we do this, we go from three seconds to about 1.5 seconds, right?

This query does not lie to us so much at the end. So and over here, we have a seek into our index, right? So over here, we’re able to apply that seek predicate not only to owner user ID but also to the post type ID stuff.

But this does bring up something that I kind of don’t like and this happens quite a bit with nested loops plans is we are getting a sort of a double seek in here.

We have one seek keys, one up here and two seek keys, one down here. So we have two seek predicates sort of separately going into this index and doing their seeks, which I don’t love.

We’re going to talk about what ways we can address that in a moment. But another way of rewriting this query, so like before with the derived, we were essentially ginning up a row number over this entire result set.

Down here in this one, we’re essentially generating a row number over only every owner user ID that comes in, right? So we generate a row number over these.

So instead of doing one big sort, we do lots of smaller sorts. That’s a pretty good start, right? We got a parallel plan out of it. We no longer had the single threaded plan. We improved our time by two.

But this isn’t always the best way to figure out like what the… what the high score is for something. A lot of times, you’ll want to test different ways of getting the same data because under different circumstances, different techniques might produce better performance results as long as they also give you correct logical results.

That’s a nice plus, right? But here, instead of generating a row number for every user ID and then filtering on it, we’re just going to ask for the max here.

Remember, we are at about 1.5 seconds with continuing with the row number technique. And if we run this one with the max, we get down to about 667 milliseconds.

So we improved there by like another half, right? We got twice as fast using the max technique, which is fine.

But still over here, we have sort of the same kind of thing that I was talking about with the one seat keys one and the two seat keys two. So we’re still doing this like multi-seq or dynamic seek into the index, which I still don’t like very much.

So what we’re going to do is change our query a little bit and we are going to run it like this instead. And I’m just going to execute that and then come back up here and show you that we got the max score for this here and the max score for this here.

One is for post type ID equals one. Well, the other is for post type ID equals one. So we’re doing two separate hits of the post table. And now this query finishes in about 15 milliseconds, right? Because we’re no longer doing that sort of that multi-seq within one seek operator.

We have broken this out and we’ve given SQL Server two very clean predicates to seek into and figure out what we want to get out of each one. We could even do that with top one.

And top one, of course, would give us the same result here. So we run this. And this gets down to, well, it’s just about the same execution plan. It also finishes in about 15 milliseconds.

So this is just another example of how we can better align queries to the indexes that we have. Whenever you’re query tuning, whenever you’re out there looking at execution plans, if you see something in there you don’t love, like it started off, we had an index scan on an index and we knew that that index led with the column that we’re, one of the columns that we’re correlating on owner user ID.

We just knew something wasn’t right. Something was amiss with that execution plan. And by rewriting the query, we got SQL Server to take better advantage of an index that we already had kicking around.

All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about something, I guess, rather similar to this, because I did promise we would spend some time talking about aligning queries and indexes.

Anyway, thank you for watching.

Going Further

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

SQL Server Performance Office Hours Episode 66

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction to Performance Troubleshooting
• 00:02:34 – Why Does a Big Machine Suddenly Start Choosing Serial Plans?
• 00:06:27 – Troubleshooting Weekly Performance Issues
• 00:10:27 – When Does Dynamic SQL Become Worse Than Static SQL?
• 00:11:28 – Indexing Foreign Keys and Delete Performance

Full Transcript

There it is. There’s the microphone. Erik Darling here with Darling Data. Having a grand old time. Not really. Anyway. Just kidding.

Monitoring Tool Mogul of the Year. Be on the front page of Monitoring Tool Mogul magazine smoking a cigar. It’s gonna be great. Anyway, it is time for office hours because it is, as they say in the Bible, Tuesday. I don’t think the word Tuesday is in the Bible.

Does that mean Tuesday doesn’t exist? I don’t know. Like Tuesday dinosaurs, the color blue? Where is any of that stuff? Where did it come from? Anyway, down in the video description you will find all sorts of helpful links to help you attain all of your life’s dreams and goals.

You can hire me for consulting. If you would like me to show up physically, virtually, romantically to fix your SQL Server problems, I can do that. You just have to hire me to do it. There’s a link that will help you hire me to do that down below, underneath me somewhere. If that’s too much for you, if you’re like, you know, I really just like this guy on video, maybe in person is a little too much, you can just buy my training.

It’s very reasonably priced, as my consulting rates are also reasonably priced, and you can just learn everything I know with this one weird trick. You can also support the channel. Either by becoming a subscriber. And we’ll answer five of those every time I do office hours. Also down in the video description, oh, look at that.

This is why I’m going to be on Monitoring Tool Mogul magazine. I’m not cheating on Beer Gut magazine. They’re actually, Monitoring Tool magazine is actually a subsidiary of Beer Gut magazine. So I’m not cheating on Beer Gut magazine with Monitoring Tool Mogul magazine.

It’s actually Monitoring Tool Mogul monthly. Uh, so I don’t know who, I don’t know who’s going to be next month. Maybe they’ll go out of business.

Couldn’t find anyone else. It’s one page. Uh, anyway, there’s also a link down in the video description. If you want to get my totally free, totally open source SQL Server Monitoring Tool, a bunch of good stuff gets collected in there, help you figure out performance problems.

Um, you know, the normal spade of stuff that you would get, uh, you know, resources, CPU, disk memory, all that crazy. Stuff.

And also if, if you, if you want to let your robot companions talk to your monitoring tool data, well, boy, how do you can get them chit chatting to the monitoring tools and there, and you can, uh, you can let them do some read-only analysis of your monitoring data.

Uh, coming up this year, I will be at data Saturday, Croatia, June 12th and 13th with an advanced T-SQL pre-con. I will also be at pass data community summit in the West in Seattle.

Washington, November 9th through 11th. And, uh, once we’re there, who knows, who knows what’s going to happen? Cause it’s going to be a crazy times, but anyway, we will continue our March through May.

It’s funny, right? We will continue our March through May and I will answer some questions. Now I’m going to drop this whole stick where I make jokes about stuff.

All right. So let’s see here. First up. I have been asked to look at adding created.

And last updated date, time columns to my main transactional OLTP tables. My, my, my, uh, that’s going to be fun for you. Uh, created can be auto-maintained using a default populated on insert, but how would you advise maintaining last updated a trigger?

Well, you could certainly use a trigger. I have used a trigger in many times to do that. Uh, I have a few blog posts where I outline, uh, some good ways to write triggers. Uh, you’ll, you’ll definitely want to do, uh, you’ll.

Check the row count immediately. And if it’s zero bailout, um, since this is, uh, a last updated trigger, you will also want to use the trigger nest level function to make sure that you are not updating your last updated based on the updated last updated column, getting updated.

That could be bad. Um, there’s also another thing that you can do. So, you know, we have temporal tables in SQL Server, and I’m not saying that you should use those, but you can add temporal table time tracking.

Columns to a normal user tables. Uh, I have a blog post about that. Uh, it’s called tracking row changes with temporal columns or something like that. Um, you, you, if you, if you, if you, I would say Google that cause Bing, man, that that’s like, like searching by asking someone who’s not really listening for advice or a question.

It’s, it’s bad. Uh, so, uh, check, check that out. If you need to, um, there are some imperfections with that.

Like, like when you, when, like, if you just, if you were to like, add the last updated column to your table, um, it would be null for things that have been updated. Uh, whereas w but with the, um, the temporal table thing, uh, the, the last modified date by default has to be something.

It’s not nullable. I think at least, at least that’s what I recall when I’m last messed with it. So it’ll look like when you add the column, it’ll look like everything was last modified.

Then, uh, you might be able to. I don’t know, maybe I didn’t try very hard. It’s anything that’s possible, but those are the two things that I would try there. Um, you know, you gotta be real careful with that stuff on a transactional tables because, uh, doing another update, doing an update on top of all the other stuff you can, you can run into a lot of deadlocks doing that.

You, you know, it’s, it’s a, it’s a perilous, perilous circumstance. Why would a big machine suddenly start choosing serial plans? For large queries?

Well, uh, for the same reason, the small machine would start choosing serial plans. It’s all costing all the way down, uh, the optimizer being the cheapskate that it is, uh, if your plan all of a sudden, you know, uh, or rather it looks at a query that comes in and, uh, it does all the costing stuff. And you’re either now your query doesn’t break the cost threshold for parallelism, or maybe it does.

But the, the cost. The parallel plan was more expensive than the serial plan. Then you’re going to get a serial plan, buddy.

That’s just the, the answer to these questions is always costing, right? It’s always optimizer costing and never anything else. I mean, sure.

There’s like settings that someone, some dummy could have changed. Like, you know, you could have changed cost threshold to like whatever the high value for like 32,600 and whatever. Uh, or you could, you know, someone could change the server or database level.

Max. Stop to one or, um, apparently a traffic jam outside, uh, you know, um, someone could turn off like scalar UDF in lining and, or at have added a scalar, a scalar UDF to a query or something, but there’s all sorts of reasons why, why, like parallel serial plans sometimes get forced into the equation. But if, if nothing like that has changed, then it’s all about costing.

Perhaps, I don’t know, you could always, uh, you know, and now I assume that the, um, the. Enable parallel plan preference use hint is now officially supported by Microsoft since they were using it where it’s still are using it in some of their code within SQL Server. So it must be okay for everyone else to use that.

But, uh, I added, uh, oh my Lord. Oh my goodness. Yes. You’re playing my song here.

How do you troubleshoot performance issues that only happen once a week for 10 minutes? I monitor them with a monitoring tool. Like, I don’t know, maybe this free one that I have. If I go.

Way down here. And if we, let’s just say we look at SQL Server 2025, uh, look at all this monitoring data that we have that might tell us what a performance problem might be like, you know, maybe let’s go look at weight stats. Look at all those weight stats.

Look at, Hey, look at there’s our 10 minute problem. Woo. We found it. Um, I, I don’t know. That would probably be my, my first inclination. If I was, if I only had a performance problem once a week for 10 minutes, I would probably just monitor the server and I would, I might even.

Be astounded to find that I have other performance problems that I didn’t know about maybe outside of that once a week, maybe outside of that 10 minutes. So you could, you could always get always download and use this tool for free. The link to do so is down in the video description.

Anyway, back to our Excel file. When does dynamic SQL become worse than static SQL from a performance perspective? And, uh, in, in general, as long as, uh, the conversion from static to dynamic is a one-to-one, I would say like almost never, but of course, like, you know, if you’re, if you’re going from, uh, like static SQL, where you’re passing in literal values to like parameterize dynamic SQL, you could always hit a parameter sensitivity issue, but that’s not dynamic SQL’s fault.

That’s, that’s parameters is fault. Um, so I think this one, I would. I would say almost never, um, I, I can’t think of, I can’t think of anything off the top of my head that, uh, that there would be good for, that there would be a good answer for this.

I, I can’t really just, I’m, I’m, I am stumped on that one. All right. Uh, why does indexing foreign keys help delete so dramatically?

Well, man, it’s like when you have foreign keys, like this, like, like actual foreign keys, not just like ones that you imagined in your head. Right. Not just like, ah, that’s a.

Foreign key to that table. I’m, I’m not gonna tell SQL Server about that. I know that, but I’m not gonna, you know, add anything to do that. Um, you know, of course, in order to validate those foreign keys or even to cascade actions from one foreign key to another, uh, SQL Server has to join those tables together. And, uh, for the same reason that indexes would, would, would potentially make joins more efficient, they might potentially make foreign key deletes more efficient because they have to figure.

If the, the SQL Server has to ensure the referential integrity of that foreign key is maintained, then a delete, uh, an index would help that delete quite a bit. All right. So that is our five questions.

Uh, this is a monitoring tool. I hammer DB running. That’s why that thing is so big. Uh, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. Uh, we’ll talk about something that I. Haven’t figured out yet.

I mean, I have some ideas, but I’m, I’m not quite sure what we’re gonna do with them yet. Anyway, thank you for watching.

Going Further

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

Learn T-SQL With Erik: Controlling Memory Grants

Chapters

• 00:00:00 – Introduction to Controlling Memory Grants
• 00:03:45 – Example Query and Indexing for Order
• 00:07:24 – Exploring Query Plans and Sort Operators
• 00:10:00 – Separating Informational from Relational Columns
• 00:11:47 – Using Cross-Apply for Smaller Sorts
• 00:12:09 – Summary of Key Points

Full Transcript

Erik, monitoring tool mogul darling here, home of, coming to you from Darling Data, home of the best SQL Server performance monitoring consultancy in the world, with the addition of a couple words I can make the lawyers happy and not have to say outside of New Zealand, because I don’t think, I’m pretty sure Paul White doesn’t build a monitoring tool, so if he ever starts we’re in trouble. So that’s what we’re going to do this week, we’re going to carry on with that, and we’re going to talk in this video about controlling memory grants, something that becomes important, not only if you’re tuning one specific query, but also if you need to make your queries as a workload, sort of get on with each other and be happy and all that stuff. So that’s what we’ll do today.

Down in the video description, everything in life that you would ever need to achieve has a link available in the description of this video. You can hire me for consulting, I can come work on your SQL Servers, either virtually or live and in person, you can purchase my training, which is probably one of the best SQL Server training deals on the internet, even in New Zealand. You can become a subscribing channel of the member, if you feel like forking over four bucks a month just to say thanks for all this stuff, you can do that.

You can also ask me office hours questions, those remain free on the house, right? On the Darling Data. I have an account for those.

And you can also like, subscribe, you can tell a friend, you can tell a foe, you can tell a frenemy, whoever you want to feel like telling about this channel, please do, because I like when my subscriber counts go up, that’s always nice. If you want the best SQL Server performance monitoring tool on the internet, you can download mine, also totally free, right? Open source, no email required to sign up.

No phoning home with weird messages. No weird telemetry. Not trying to figure, not asking what your indexes look like. It’s just all the important T-SQL data collectors you would expect. You know, we grab, you know, CPU, memory, we can tell you about memory grants, CPU, memory, disk, the works, weight stats, blocking, deadlocks, long running queries, all this stuff that a growing SQL Server should have looked at over time.

And if you want your robot companions to help you make sense of your performance monitoring data. I have a bunch of read-only built-in tools that know what that data should look like, the general shape of it, and can help you do that. I will be out in the world.

I have two things left this year, unless something else cool comes up. I will be at Data Saturday Croatia with an advanced T-SQL pre-con. You can go to that link there. That’s a hell of a link.

You might even just be better off Googling Data Saturday Croatia as, you know, I don’t know if you’d want to type that. But then I will also be at Pavel. I will also be at Past Data Community Summit in Seattle, Washington.

My use and utility there is a mystery as of yet, but I’m sure all will be revealed as we get closer to November 9th through 11th. So we have that to look forward to. But now it is May.

I have a chisel on my desk for some reason. And, well, let’s talk about memory grants. Let’s chisel away at some memory grants. So I’ve got a couple indexes created here. These will become important later.

Gaze at their majesty. One is keyed on user experience. One is keyed on user ID and score. The other one is keyed on user ID with scores and include. The reason for that will become obvious later.

So we’ve got this query here that I have pre-run because it takes a little bit to run. And I don’t want to stand here in the hot, light heat while I wait for all this. This query will ask for quite a big memory grant, both because it is written in a way with this derived join, which will force us to run this query and produce a result.

And two, because we are selecting all of the columns from the comments table, one of them being a column called text, which is in InvarCar 700. So just to sort of get ahead of things a little bit, this query asks for an 11 gig memory grant. If you want to fix a big memory grant, you have three basic things you can do for any given query.

One, you can add indexes that support the ordering sequence. That’s the stuff that you need to do. Two, you can separate your columns between the informational set and the relational set, generally so that you don’t need to sort as many columns when you select things and order by things.

You know, the indexing is useful because with an index putting data in order, you might be able to avoid a hash aggregate or a hash join, which, you know, if you have data already in order, SQL Server might find a nested list. It might find a nested list of loops or merge join more palatable.

And the third is you can write your query in a way that allows it to do more small sorts rather than one big sort. In this query, we do one big sort and we select a bunch of columns. And we, I mean, we obviously do not use either of the indexes that we had created because we just scanned the clustered index of the comments table and zoom it.

Once again, betrays me. So there is that. A lot of things can be solved with batch mode, right, as we’ll see here.

If we run this query, it will run much, much faster. It will, the CPU and IO boundness will go away. However, it will still ask for an 11 gig memory grant because we are still sorting all of those rows for all of those columns.

So batch mode, great for many things, right? And if this memory grant were inappropriate, the memory grant might even adjust that. It might adjust between executions.

But in this case, it uses the whole thing. But anyway, with that index that is keyed on user ID and score, if we force SQL Server to use this, not only will we get a query that is faster by a little bit, even compared to the batch mode one. We are down to 1.388 seconds from 2.5, 2.6 seconds.

But this query really only has 1.388 seconds. It has one sort operator in it here. And the total memory grant for this is 5,888 KB.

Wow, that’s a lot of 88s in this thing. 1.388, 5.888. Geez, a lot of 8s.

Anyway, this is an example of having our data in order since we are partitioning by user ID and we are, sorry, and we are ordering by score. And, you know, also C.ID down here. But you crud.

Also C.ID down here. Because the ID is the clustered primary key on the comments table, that ID column is immediately part of the non-clustered, both nonclustered indexes that we created as a key column because they are not defined as unique. Anyway, with our data presented to SQL Server in order, we don’t have to sort stuff.

This next query plan is a little bit weird. So usually when I, so I’m in the process of redoing all my queries for SQL Server 2025. And one thing that happens with this query is that we, by forcing it to use this index and changing the reputation to 10,000, we actually do end up with another sort in this query plan.

At least it did the last time I ran it. It also runs a bit longer, right? So, and we do end up with a sort down here again.

I’m not entirely sure why right now. I mean, because we put the data in order here to make the next query. We put the data in order here to make the nested loops join more efficient. But I’m just not sure why this sort shows up here just yet.

I’m going to have to figure that one out a little bit. So bear with me while I do some surgery on that later. But both of these queries are written in a way where we are able to do many small sorts rather than one big sort.

And we’re using cross-apply to do that. Cross-apply is sort of like a for-each loop, right? So for each user with a reputation over 10,000 in the users table, we go and run this query.

Technically, we don’t even need this partition by element here because it is sort of automatically partitioned by each seek into the comments table for a user ID that gets passed in from the users table. So we could even take that out here. For this query, what I’m going to do is force the index where score is just an include column, though.

All right. And run this. And, I mean, it’s sort of the same plan shape as last time.

But just to sort of make the point obvious, this is an apply nested loops join. We can tell it’s an apply nested loops join because it has this outer references thing in the bottom. And what apply nested loops indicates is that we come in and we seek to the user ID column each time we make a trip into here.

So rather than like with the original form of the query where it was like join and then this thing over here, we sort of for each trip that we go into, we only sort for that particular user ID, which can be very, very useful. So another thing that I want to sort of bring out here is separating informational from relational. So what we could do if we wanted to save it.

Save it. If we wanted to just keep running our query this way with the join, we could cut down the we could just get a narrow set of columns here, not select C dot star. And then outside of that, we could join back to comments.

And then we could get the text column out here from that join back to comments rather than get that text column in here where we’re generating the row number. Because like I’ve talked about, the sort operator has to write down all of the columns that we’re selecting by the things that we need to organize. By the things that we need to order them by.

Right. And when we do that, we still have a sort in here because we’re back to using the oh, I lied. We have a sort here now because this index scan happens in batch mode and SQL Server does not trust batch mode stuff to rowstore indexes to maintain order properly when batch mode is used.

But we still have a much smaller memory grant. Right. We still have a much smaller memory grant at 1234 megabytes because we get all our relational work done up to here.

Right. So the join between users and comments and the filter to get down to row number equals one here. That’s this thing.

That all happens like over here. And then we get down to 600 rows here. And then we join to the comments table with an adaptive join. Very batch modey.

We can tell by the thickness of the arrow that it uses this one. Right. And then we join back to the comments table in order to get the other informational columns that we have. So the three things that we’ve talked about in this video that can help you control memory grants is one, indexing to put your column data in the order that your queries are asking for it in.

Two, separating informational from relational columns. Doing the relational work earlier in the query. Getting the informational data later in the query.

And three, using things like crossfitting. Using things like crossapply to do a bunch of smaller sorts via nested loops join sort of a for each loop rather than doing one big sort like with the derived join. Anyway, it is hot in here.

My brain continues to leak out of my ears. I love you, but I need to go drink some water. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

And I will see you back next week on Tuesday for another rampaging Office Hours session. All right, 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.

Learn T-SQL With Erik: Memory Grants Intro

Chapters

• 00:00:00 – Introduction
• 00:03:44 – Memory Grants Overview
• 00:06:04 – Memory Grant Sharing
• 00:07:39 – String Columns and Memory Grants
• 00:10:23 – DOP and Memory Grants

Full Transcript

Erik monitoring tool mogul darling here with Darling Data. In today’s video, much like I think I foreshadowed in yesterday’s office hours video, we are going to talk about memory grants. We’re going to do a somewhat gentle introduction to them and then in the next video we’ll talk a little bit more about where they get interesting. I apologize for the state of my hair. It is very hot and humid here today. I’m starting to get weird, curly, I think my head is boiling at this point.

These hot lights that make the green screen effect possible are not helping. Anyway, down in the video description you will find pretty much everything you need in life. Period. I can’t imagine what else would go wrong.

If you want to hire me for consulting, you can do that. If you want to buy my training, you can do that. If you want to become a supporting member of this channel for as little as $4 a month, you can do that. If you want to ask me office hours questions, you can do that.

Quite frankly, food, clothing, and shelter are overrated. Pale in comparison to those things. And of course, if you find this channel has any value whatsoever to you in your life, you can always like, subscribe, and tell a friend.

It’s a pretty sweet thing for you to do. You can also, down in the old video description, you can get a hold of my free open source SQL Server monitoring tool. It does everything.

That you would ever want a performance monitoring tool to do. And more, probably. It monitors stuff like weight stats, blocking, deadlocks, top queries, all sorts of internal server metrics, CPU, memory, disk. You name it, it is in there. It covers the bases, baby.

It will even tell you about memory grants, which we’ll talk about today and tomorrow. And if you are having a good time embracing our robot companions, there are optional built-in MCP servers.

So you can have your robot friends look at your performance monitoring data and do some analysis on that. So you don’t have to go poking around through various charts and graphs and other sundry information that my monitoring tools expose. Right now, I only have two travel dates on the calendar.

That may change, depending on local factors. I will be at Data Saturday Croatia, June 12th and 13th. And I will be at PaaS Data Community Summit in Seattle, Washington, November 9th through 11th.

You can purchase tickets to my Advanced T-SQL Precon here, now. If you’re in the Croatian area and you’re watching my YouTube channel. It would be nice to see you in Croatia.

Have you ever had a Croatian hug from an American? We can have all that happen. But for now, May is May-ing along.

I don’t know where May is. I got the idea that it could have an 80-something and 90-degree day here in New York. But it doesn’t look like this here, right?

It looks more like the June image, which we’ll get to in just maybe about a week or so. But anyway, let’s talk about our… Let’s introduce memory grants, right?

So I’ve got query plans turned on here. Hold your horses, I know. And this is material that I’ve used in other videos. So if you’ve already seen this, perhaps you can just turn off the sound and watch me gesticulate and make faces for a while.

Maybe any one of those things would be fine for you. But I’ve written this query in sort of a silly-looking way. But the silly-looking reason…

The reason for this query looking silly-looking… Ah, that’s too many lookings. It will become obvious in a moment. If I run this query… And we look at the actual execution plan for this query, we will see that there is indeed a memory-consumering operator.

Ah, boy. This heat is boiling my brain and making me incapable of speech. There is a memory-consuming operator here, and that is a sort operator. Sorts require memory to write down their results in.

And if we hover over this select operator, we will see that this required 182 megs of memory in order to function. All right.

We’re just selecting 1,000 rows of an integer column and ordering it by reputation. We get 182 megs for that. Now, it is possible for SQL Server to share memory between operators in the same execution plan.

So what I have here is this query essentially twice, right? I am selecting this one and I am selecting this one, and I am joining them together on this ID column, right?

And when I do that, and we look at the execution plan, you might think to yourself, Eric, there are two sorts. There are two memory-consumering operators in this plan.

Surely, we will ask for double the memory, but we do not because there is a third memory-consuming operator in this plan, and that is this hash join.

And what this hash join is going to do is prevent both of these sorts from executing at the same time. All right. So that hash join is a blocking or a stop-and-go operator, meaning it has to absorb all of its results.

Technically, the sort is too, but the hash join is, it will be, all will be revealed in a moment. But the hash join is really where the magic is here because we have to stop and we have to build the hash table to use on the inner side of the join.

So this only asks for 183 megs of memory, so only one extra meg of memory, right? Because this sort runs and uses its memory, this hash join starts building its hash table, and then this part of the query plan in here runs, and this sort right here reuses that memory.

That this sort has shared with it. So all sorts of wonderful sharing things happen in this plan. We have the warm embrace of memory sharing within the query plan. But if I run this query with an inner loop join, right?

Inner loop joins are not stop-and-go operators. And so if we run this, we will get a slightly different looking execution plan, a rather different plan shape here, right?

We have nested loops instead. When it’s nested loops, like I said, are not blocking operators. They are not stop-and-go operators. So in this one, we ask for 364 megs of memory, which if you’ve got a sufficient number of fingers, you will come to the conclusion that 364 is indeed 182 times 2.

Or you could use addition for that as well, right? You could even use subtraction, and you could subtract 182 from 364.

There are myriad ways in which you can reverse engineer that problem. One of the biggest memory-grant villains in SQL Server, probably in databases in general, is, of course, strings.

Are, of course, strings. All strings. All strings must die. We should never have put them into databases. They cause nothing but problems.

And if you look over here, we’re sort of keyed. We have a little decoder tool. We have a little decoder chart here, where if we were to quote out these columns, I apologize to my loving fan base for having leading commas in this query.

I do. I would not do this unless there were an insanely practical reason for it. And that is, if we quote these out and back in, we will see the memory-grant grow as each column is introduced, sort of ending up with this about me column, ending up as a 10-gig or 11-gig memory-grant here.

I think it’s a little different right now, when this nvarchar max column is added in. And this is because SQL Server, when it’s estimating memory-grants for strings, it needs to sort of figure out how much string it’s going to deal with.

And what it does is it imagines that every single row for a string column is half full. So if we had a varchar 100, it would imagine that 50 bytes of it were full.

And this is probably a pretty good arrangement for most string columns, assuming that your developers are not lazy piles of so-and-so, and they have actually given your string columns a reasonable width.

If you have a varchar 8000 column that just says, like, state or a country in it, and it’s just like, you know, state abbreviations or country codes, you’re probably not going to be happy with your developers.

So if we run this query, and we wait for the query plan, and we look at the query plan, we will see that this query has asked for an 11-gigabyte memory-grant with all of those columns in there, almost as promised down here.

I guess SQL Server 2025 has added a gig to our memory-grant. One thing that is important to understand about memory-grants, though, is that memory-grants are not multiplied by a DOP by degree of parallelism in a parallel plan.

They are divided by DOP in a parallel plan. All query plans in SQL Server start as serial execution plans. And those serial execution plans, if they are chosen, if they become parallel plan candidates, and a parallel plan is chosen, SQL Server uses the memory-grant that it assigned to the serial plan for the parallel plan, but gives an equal portion, an equal share of memory, the warm embrace of DOP division to each thread, which can be good and bad.

It can be good if your queries have fairly even row distributions, but if your queries have very uneven row distributions, you may see certain threads spill and other threads not spill.

So here we have a parallel plan with a hash join in it, right here, doing essentially the same thing. If we hover over the select, we will see that this ran at degree of parallelism 8.

Now, I don’t want you to feel misled here. Remember the serial version of this plan asked for 182 megs of memory. We needed to add in a little bit here because the parallel exchange requires memory, right?

And all that other good stuff. The exchange buffers, those are memory-consuming things. So we needed a bit more memory. We needed about 15 megs more memory to do all of our parallel stuff, right?

We needed to distribute streams and gather streams and gather streams and well, you know, distribute streams and gather streams.

We had to do a lot of parallel exchanging and so we needed a little bit of extra memory to do that. All right. So in the next video, we’re going to talk a little bit more about memory grants, controlling them and other good stuff like that.

So thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that your brain is not boiling and it’s making you speak strangely. That’d be nice, right?

Anyway, good enough for now. 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.

SQL Server Performance Office Hours Episode 65

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction to Memory Grants
• 00:03:05 – Worker Thread Starvation Causes
• 00:06:47 – Query Text and Memory Grants
• 00:09:21 – Trivial Queries and Massive Memory Grants
• 00:11:08 – Why SQL Server Gives Trivial Queries Large Memory Grants

Full Transcript

Erik Darling here with Darling Data. That’s right, the one, the only, the monitoring tool mogul. That’s me, fresh back from Poland into a massive New York heatwave, delicate sheen on my face, but I did come home to something nice, which you can see behind me, which is a brand new green screen, which is apparently a better green than I had before because I have far less green on me and there are far less figments in front of me. There are far less fragments of green sploogey things going on behind me, so it’s all very exciting. It’s also collapsible, so I can put it up and down and not just permanently have the giant wall of green fabric up in my office. Now I get to see all the cool stuff that I have in my office again, which is nice. Anyway, it is time for Office Hours, where I answer five community-submitted questions to my Google spreadsheet. We’ll talk about where you can find that if you have not heard this spiel before.

Down in the video description, you will find all sorts of useful, interesting things, much like the interesting things that I have all around my office that you can’t see, but down in the video description, there are links, right, and that’s where you can ask me Office Hours questions if you want to. That’s one of the links. You can also hire me for consulting. You can purchase my training. I’ve got very good SQL Server performance tuning training. You can become a supporting member of the channel, so if there’s… There’s some little piece of you that says, wow, Eric, all the hard work you do here is worth like four bucks a month. You can do that and give me like four bucks a month into the old tip jar.

You can also download my completely free open-source SQL Server performance monitoring tool, right? It’s pretty good. It is, I mean, maybe not like a, you know, like drag-and-drop replacement for like third-party commercial monitoring tools, but it’s getting close, right? It’s getting up there. Lots of neat features, monitors, all the stuff that you would care about if you care about SQL Server performance, you know, weight stats, blocking, deadlocks, long-running queries, all the, you know, weight stats and other metrics that you look at when you care about SQL Server performance, so it’s lots of good stuff in there.

Give it a shot. Let me know what you think. I have two conferences left so far this year. I don’t know. Maybe something else will come up. Maybe it won’t. We’ll have to find out. Past Data Community Summit in Seattle, Washington.

November, I’m probably reading these in the wrong order. And then Data Saturday Croatia coming up in, wow, that’s less than a month away. I better start practicing my Croatian. Ah, anyway.

So I’ll be there, both of those places. I don’t have anything else lined up so far, but, you know, who knows? The year is, well, kind of young. It’s about halfway to dead, so, you know, I don’t know.

Waiting for the right call for speakers email to come my way. But anyway. It is still May.

May is also about half over. A little more. So it is time to answer the questions. Let’s do that.

All right. First one. Wow, Zoomit working on the first try. We are. Now I wonder what else. I’m going to get hit by a comet now. When one wishes to index a view with multiple tables, you instead recommend making an indexed view for each table and joining them together in a non-indexed view.

I don’t think I’ve ever said those words directly. But OK, let’s let’s let’s just roll with it when you do with this. When you do this, what kind of code is typically in this single table index views?

How is it distinct from what a normal single table index offers? I have not been able to imagine a case where I would want to index a view, but would be happy to settle for making index views from its parts. So typically, I mean, like the primary use case for index uses aggregation queries.

If, you know, like doing big group by, you know, stuff on your data is slow and painful, then having that index view store that aggregated data and store and maintain that aggregate aggregated data for you is a pretty good deal. Of course, a lot of the index view stuff is overshadowed by batch mode, columnstore, yada yada. But there are still times to do it.

I. Yeah, I mean, there are maybe a few edge cases where I would create an index view that didn’t involve a large aggregation, maybe a very specific where clause of some sort. Maybe.

Yeah, I think that’s about it anyway. Yeah, I mean, you could certainly join index views together in a non index view. You might even give you I think there was a YouTube commenter who said something about putting a no expand hint into the into the view, which was I thought that was a good trick there.

But, you know, really, it’s like just pre calculating aggregation so that you don’t have to spend all your time doing that. Anyway, let’s see. How do you tell when statistics are misleading the optimizer versus just bad query design?

Well, there are many visual indicators of bad query design. Table variables, local variables, non-SARGable predicates and the like. So if you see those, it’s SQL Server is doing its best, but you just might not be able to get past the crappy things you have done to it.

But if you’re looking at a query plan and you notice that your data, or rather, if you look at your query text and you don’t see those things. And you are not dealing with some level of view nesting where someone has buried the nastiness somewhere else. Then you don’t see those things.

But you look at the query plan and you notice that maybe your data acquisition operators, where SQL Server, you know, first starts pulling rows from your tables and or indexes, those have bad estimates on them, then that’s probably where I would make that determination or at least make that supposition from maybe not be able to fully determine that you would have to look at, you know, of course, when statistics were last updated, how many modifications have occurred since those updates and the such, but that’s probably where I would start. Join operators would not be a good place. Because join cardinality estimation is just fraught with peril, even under the best of circumstances.

So we’re lucky SQL Server is smart enough to get that right a lot of the time. Why do memory grants fluctuate so much for the same query text, sometimes huge, sometimes tiny? So you’re playing a game here with me, toying with my emotions.

The same query text. I don’t know if I buy that. If you said the same query plan, we might have some different answers. But, you know, it could be it could be one of the memory grant feedback mechanisms, you know, SQL Server will make adjustments to memory grants, depending on, you know, how memory was utilized, or how much, how much a query spilled to disk when it was executed.

Otherwise, you know, it kind of sounds like same query text has a little bit of wiggle room in it. Sort of like, you know, it gets the same essential query text is essentially the same. But maybe some of something in the where clause is like a literal value, where it’s a little bit different, and maybe SQL Server is compiling an execution plan specific to some set of literal values, and sometimes it estimates that grant to be larger, and sometimes it estimates that grant to be smaller, but could be it could be a parameter sensitivity thing if it is a parameterized query, but it’s all sorts of stuff.

Could it be? Could it be? You’ll have to be a little bit more.

You have to give me a little bit more detail. So if you want me to answer that more thoroughly. What besides CPU pressure can cause worker thread starvation? I mean, just primarily blocking, right?

That would be the big one. You know, you don’t have to necessarily have CPU pressure to run out of worker threads. So maybe that’s why you’re asking, because it happened.

You’re like, CPU is at 2%. Why do I have no worker threads? Blocking would be the primary thing there. There are all sorts of things that aren’t CPU intensive that do. Look up a lot of worker threads.

I think one of the real funny things is people kind of don’t realize that when they put a lot of databases into an availability group, that availability group requires worker threads to synchronize the data. So like I have 590 databases in here. I’m like, you have 512 worker threads.

What do you think is going to happen? No, it’s not going to be good. So I would primarily say blocking. All right.

Why does SQL Server? Sometimes give trivial queries, massive memory grants. I wonder if you’re related to the memory grant person up there. It might even be the same person.

It’s the closest relationship you could possibly imagine. Anyway, you don’t have to have a tremendously complicated query to get a pretty big memory grant. Let’s just say for argument’s sake, you’re doing like a select top 1000 star from some table ordered by some column.

And you have no index that puts your order by column. You have to put your columns into the correct order. Now, SQL Server will have to do something with that.

You’ll have to sort that data and it has to write down all of the columns that you’re selecting and the order of the columns that you’re ordering it by. Just to make the demonstration easy, let’s visualize this as what an Excel file does. All right.

So we’re going to add a column here called sort and we’re going to say 54321. All right. And so when you’re like, hey, SQL Server. Order by this column, SQL Server is like, there’s no index on that column. I have to ask for memory to sort all this data, especially long string data that gets.

That’s really where big memory grants come from. But it’s sort of like when you click this button right here and then you hit this sort button over here and you’re like, I want to order by sort smallest to largest. Right.

SQL Server has to write down these columns and the order of the column that we just ordered by. Right. So all this text just flipped to match this ordering. So that’s essentially. What SQL Server has to do.

Right. So you’re selecting a bunch of big old string columns and you’re like, hey, order by some other column, some integer column. SQL Server is like, well, crap, I don’t know how big those strings are. What SQL Server does is estimates that every row in a string column will be half full.

So if it’s a VARCHAR 100, it’ll estimate that every row has 50 bytes of data in it. That means if some are larger, some are smaller. It’ll land somewhere in the middle.

Where that gets dangerous, though, is if, you know, you have a particularly long string, like, let’s say a VARCHAR 8000, but the name of the column is like state. And so it’s like, you know, M-A-N-Y-C-T, those are all in the northeast. I’m giving myself up here, but like that, oversizing that SQL Server would still estimate that the 4000 bytes of that column have data in them, even though in reality, only two bytes have any data in it.

SQL Server is not looking any more closely at stuff. Right. So that’s that’s that’s usually why.

So anyway, that’s five questions that are now completely out of order. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where, oh, God, we’re going to we’re going to talk.

Actually, we’re going to for the next two videos, we’re going to talk more about memory grants. So these memory grant people are really, really getting their money’s worth this week. I hope I hope they’re paying subscribers.

It’s a lot of memory grant material for them. Anyway, thank you for watching.

Going Further

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