Tag: create index in sql server

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

Posted on by Erik Darling

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

Chapters

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

Posted on by Erik Darling

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

Chapters

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

Learn T-SQL With Erik: Memory Grants Intro

Chapters

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

Posted on by Erik Darling

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.

Learn T-SQL With Erik: Indexing and Sorting And Searching

Posted on by Erik Darling

Learn T-SQL With Erik: Indexing and Sorting And Searching

Chapters

  • 00:00:00 – Introduction to Indexing and Sorting
  • 00:02:34 – The Impact of Indexes on Query Performance
  • 00:05:16 – Common Misconceptions About Indexes
  • 00:07:28 – Creating an Imperfect Index for Specific Queries
  • 00:09:23 – Understanding Row Goals and Their Effects
  • 00:10:40 – Optimizing Query Plans with Shared Ordering
  • 00:11:05 – Recap and Conclusion

Full Transcript

Erik Darling here with Darling Data, and today’s video we’re going to continue getting little whiffs and whispers and snippets of my Learn T-SQL course. This one is about indexing and sorting in a query. So, I know it’s not the snappiest title, but you’re just going to have to learn to live with the fact that I am not a snappy marketing person.

So, if that’s what it takes, sorry I don’t have it. Anyway, down in the video description, all sorts of helpful links, including one to purchase the entire corpus, the big body of this training, that is going to be down below in there. There’s even a coupon code attached to it if you celebrate coupons, many of you do.

There are other links as well, you can hire me for consulting, you can buy my other training, Lord knows I’ve got other stuff. And you can even choose, if you appreciate this and you don’t want to see me turn this into a tickling enthusiast channel, then you can also choose to support my efforts here for as few as $4 a month.

You can also ask me office hours questions, and if you want to be my friend, then you will like, subscribe, and tell your friends, so that the numbers associated with this channel continue to rise in the correct direction. Down in the video description as well, also, my goodness, you can also download my completely free SQL Server monitoring tool.

It is open source, you can see everything it does. It’s all the stuff. It’s all the stuff that I would care to look at if I were working for you and monitoring your SQL servers.

One thing I can’t vouch for entirely is if you want to have your robot friends look at your monitoring data, you can do that. There’s MCP tooling built in. But, you know, of course, the advice that you get will not be anywhere near as good as the advice you would get if you really did hire me.

But if you don’t have any money, then that’s going to be your silver medal. Upcoming events, down to, at this point, well, I mean, it’s, at this point, I will be on my way home from Poland, so Poland, I hope I had a great time.

I hope you enjoyed having me. And maybe I’ll see you again in the near future. I’m sure I was problem-free. But I will be at Data Saturday Croatia June 12th and 13th with an advanced T-SQL pre-con.

I will also be at PaaS Data Community Summit in Seattle, Washington from November 9th to 11th doing who knows what. The gods must be crazy. Erik Darling is going to Seattle again.

Maybe I’ll just get a barista job shortly before the baristas all get replaced with robots, too. But for now, it is May, and we will continue our May monitoring tool mogul month with creepy, gothy database skull whispers. Ah, man, it never gets old.

All right. So now you’re getting a preview of next month. You can’t see how grand it is in that. Maybe if you zoom enough. But I promise it’s going to be a banger.

When that thing goes full screen. But anyway, I’ve got two queries here. And the only difference between these two queries is the way that data gets sorted for them. This one is ordering by ID descending, and this one is ordering by ID ascending.

But they have very, very different execution times. If you look at the results for them, well, the results don’t matter. What matters is the query plans.

All right. The first query, which is ordering by ID descending. Go away, tooltip. You were supposed to zoom it. And you didn’t. And now you’re making me look foolish in front of all these nice people. The first one that orders by ID descending finishes instantly.

Zero milliseconds. The one that orders by ID ascending takes 18.6 seconds. Dear God, why? Why would you do that to a poor query?

All right. What did that query ever do to you? So what’s interesting here. So this is the votes table in Stack Overflow 2013, which means time ends on January 14th. January 1st of 2014.

And the votes table has about 53 million rows in it. And a lot of them are vote type ID 2, which is an upvote. The big thing here, though, is that ID being the clustered primary key of the votes table, it means that it shares something with the creation date column.

So that ID column is an identity, meaning it is monotonically increasing. Every time a new row comes in, it comes in with a higher value. Same thing with the creation date column, right?

Because you’re not creating like a higher ID with an older column. You’re creating a new row. You’re creating a new ID with a higher ID. And that’s just silly.

All the creation dates are going up too. So when we sort by ID descending, we get to the range. We get to the pages where we have the 2013, 2014 data a lot more quickly. All right.

These are both just scanning the clustered index. I’ll go back and show you that in a second. But the one that sorts by ID ascending, we have to go through a lot more of the table until we get to the rows where 2013, the last month of 2013 happened, right? So, you know, again, both just scanning the clustered end.

I do wish SSMS offered you an option to expand object names because I don’t like being left hanging. It’s a little obnoxious. But you can also see shades of that.

I generally hate this query plan, but it’s good to illustrate sort of the problem that we have here. So I’m getting the min ID and the max ID with the same where clause, right? And keep in mind, we don’t have to sort this data, right?

We don’t have to like… Rather… I’m not saying order by here, right? But implicitly, min and max have to sort of order by to find the lowest and the highest. If we look at the query plan for this, one of those branches finished very quickly.

You can bet this is the max branch. One of those branches finished pretty slowly. It took 6161. Look at that symmetry.

It took 6 seconds to finish or 6.161. 6 seconds to finish. You can bet this is getting the min. Well, I do find this query plan helpful to illustrate the problem. That we’re having here.

It is highly offensive. If you want to… If you see query plans like that and you are equally offended, you can add these hints. Index 0, which is in use even though we have a clustered index and a tablock hint. And if we run this, we will get just one single pass of the clustered index where we find our min and our max.

Fun things, SQL Server. What will you think of next? I don’t know. But like I was saying, ID and creation date have that sort of increasing value in common.

Now, what I want to do here is create what is probably sort of like maybe an imperfect index, right? But it’s to show you something a little bit later on. Now, will that create…

Actually, I think I have the query again down here. We don’t have to go all the way up. Now, we’re still going to stick with the query ordering by ID ascending, right? And like we would think that, you know, the index up there that leads with creation date would be able to find that last index.

We would be able to find that last month of data very, very quickly. And then we would be able to, after we locate that range of rows, then we would be able to locate the vote type ID 2s. And then maybe we’d be able to order by ID.

But B-tree indexes just don’t work like that because the initial greater than equal to is already messing up like the preserved order of the equality predicate. But just watch, right? Okay.

So let’s just run this thing now, right? We do this. We say, hey, go and run, right? And it’s still going. It’s still going. It finishes faster, right?

Notice it finishes faster, but it doesn’t use our index, right? We are still scanning the clustered index of the votes table, but it finishes much more quickly. Okay.

That’s interesting. Well, let’s focus on why here a little bit, right? So let’s run these two queries. And I’m going to run these.

I’m going to keep this query at max.1 just to keep the plan a little bit more simple. But in the first query, we’re letting it run as it is, right? In the second query, we’re telling SQL Server to use our index. So this is kind of what’s happening here.

Now, the first one that scans the clustered index still finishes in about six seconds now. So that’s cool, I guess. But the second one down here finishes like instantly, right?

Well, just about instantly. Much faster than, like, I mean, really, just like six seconds faster. We have 6.39 versus 300 milliseconds. Just chop off the six.

They’re basically equivalent. But if we look at the cost that SQL Server puts on this one, right? Very, very low cost. Estimated subtree cost, 0048194, I can read. And this one has a subtree cost of 125.232.

SQL Server costed the index seek into the votes table very, very highly, right? I believe this is where it pops up. But, yeah, if we look at this, we have a row goal set in this, right?

Because we’re using offset fetch. And the row goal is cheapening the clustered index access. Let’s see.

Properties. This query, if you look, does not have a row goal attached to it, right? We do not have that.

If we just flip back and forth between these two, the estimated row goal, estimate rows without row goal is on for this one, but not on for this one. So that’s a little strange and confusing there, right?

This query uses a top. This query uses a top and sort. So in reality, all the rows that we want to get need to get to this sort operator. And then we’ll do that.

So the query plan changed in a way where the row goal got removed from the second one. And so that query appears to be much cheaper to SQL Server because now SQL Server is like, well, with this one, I think I can do this in 10 rows.

With this other one, this is going to take me a lot more rows to do this. And man, that is a sad, sad bummer, right? So what we can do is change the optimizer’s mind by taking advantage of the shared ordering between creation date and ID, right?

Well, we do need to keep ID as a tiebreaker because creation date is not unique. It’s not guaranteed to be unique, right?

So what we’re going to do is say, let’s order this by creation date and ID. And this also buys us a little bit of extra juice, because now that we’re ordering by creation date, these inequality predicates don’t mess us up with preserving the sort of B-tree index ordering.

And this finishes absolutely instantly, just like we would want, right? And that’s seeking into our fancy index there. So like I said, this ultimately happens because of the row goal that offset fetch introduced.

If we run this query without… Why are you doing that, SQL Server? If we run the first query that we did without the… Sorry, with the row goal disabled, then we will get a pretty good query plan out of that as well.

But anyway, that’s all I had on this one. Index, searching, sorting, ordering, indexing, all the stuff that I promised would be in here.

I hope you enjoyed yourselves. I hope you learned something. And I will see you in Tuesday’s Office Hours video. 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: Indexed View Serializable Locking

Posted on by Erik Darling

Learn T-SQL With Erik: Indexed View Serializable Locking

Chapters

  • 00:00:00 – Introduction to Index Views and Blocking Issues
  • 00:03:45 – Isolation Level Promotion to Serializable
  • 00:06:51 – Blocking Analysis with SP_WHOISACTIVE
  • 00:09:28 – Trace Flags for Detailed Locking Information
  • 00:11:56 – Summary and Next Video Preview

Full Transcript

Apologies for the low audio in this video — I was testing a new microphone setup that didn’t work out. Should be back to normal volume in future videos.

Erik Darling here with Darling Data, now with a fully turned on microphone. In today’s video, we are going to go through a little more snippity-tidbitty tiny chunks of material from my larger Learn T-SQL with Erik course. You can buy that in a very cool way by looking down in the video description.

If you look on down yonder, what you’ll find are all sorts of helpful links. Helpful to both you and I. Helpful to you because you can get help, or you can get training.

Or you can support this channel so that I don’t turn it into an Amiga repair channel. And that gives me money and it gives you SQL Server stuff. Fair trade.

You can also ask me office hours questions. And of course, if you enjoy this content, please do like, subscribe, tell a friend, family, foe… I don’t know, tell your foes too. At least, maybe they’ll get so into this channel they’ll just become completely distracted.

You’ll no longer have to worry about them attacking you in some way. But the training in question does have a coupon code linked down in the video description if you are the type of person who needs, wants, requires, or enjoys high quality SQL Server performance training.

For free, also linked down in the video description, my SQL Server performance monitoring tool. It’s almost like if you hired me to come work for you.

And you said, hey Eric, we got these slow SQL Servers. How can we figure out what’s slow and how can we make them faster? And this is the stuff that I would look at. I might even bring my own monitoring tool in and be like, hey, we’re going to start looking at this stuff.

And if you need help with looking at your monitoring data, I have enabled you to opt in to turning on some MCP tooling servers. And so you can have your robot friends look at your, collected performance data and only your collected performance data in a read-only way so that you can figure out what’s going on with your SQL servers.

We are down to two. Because this is publishing while I’m in Poland in Wroclaw for SQL Day. I’m sure I’m having a great time.

I’m sure that I haven’t been arrested and I’m sure that I am very healthy and not hungover. All things that I’m sure are true at the time of this thing. Going live. But I still have some other things that I’m doing out in the world.

Data Saturday Croatia, June 12th and 13th. I have an advanced tickling pre-con. If you…

I mean, maybe I should. There’s probably better money in a tickling pre-con than a T-SQL pre-con. But I have an advanced T-SQL pre-con at Data Saturday Croatia. And I will be attending PaaS Data Community Summit in Seattle from November 9th to 11th.

And some manifestation. We’ll see what happens. But for now, it is May.

And I hate Star Wars. So we’re just going to keep going here. All right. Because that day happened. I am much more of a Cinco de Mayo person.

National Margarita Day, also a personal favorite in the Darling Data household. 100% of SQL Server monitoring tool moguls prefer… National Margarita Day to dumb Star Wars days.

Anyway, let’s go look at IndexView stuff. Now, IndexView maintenance is a very, very complicated topic. It requires a good bit of caution when discussing all of the different aspects and behaviors that get involved here.

For a single table IndexView, locking is pretty normal. You take some xlocks. And the transaction isolation level is left alone.

The query plans can get interesting. But, you know, that’s just IndexView maintenance for you. Paul White, friend of the chain Paul White, has an excellent post called IndexView Maintenance in SQL Server Execution Plans.

Written back in year of our Paul, year of our Kiwi 2015. But things get much more complicated. And something that I would probably caution you against doing is putting multiple tables into an IndexView.

Because when you do that, that’s when things start to get funky. Both from the perspective of, well, I mean, the complexity of maintenance for that. The performance of maintenance when it comes to that.

And also, what happens to locking and isolation levels behind that. So, let’s look at an example. I’ve already pre-created this because it takes a little bit to get set up.

But I’ve got this IndexView called UserPostScore. And it is joining the user’s table to the post table in the StackOverflow 2013 database, StackOverlord. And I have created myself a unique clustered index on it.

So, if you want to, oh, yeah, that’s the thing. If you want to reproduce this code, this little function that I’ve got here. What’s up, locks?

This is in my GitHub repo, the darling data one. You’ll find it under, like, helper, use, functions, things in there. But let’s start this running. And what I want to show you first is when we update the user’s table, right, we’re going to look at locks for the user’s table, the post table, and our IndexView, right?

So, if we run this, this takes about five seconds because I haven’t done any tuning work on it. It’s sort of intentionally bad. We’ll see that we have regular xlocks on users.

We even have some regular xlocks on the IndexView UserPostScore. We do not have any locks on the posts table, right? But notice this third line down here.

We have these range xxlocks taken on the IndexView. These are serializable locks, right? So, our isolation level has been silently promoted up to serializable. And that means, of course, that, like, you know, under the default index view, it’s going to be serializable.

It’s not read committed isolation level for most SQL Server people who are, you know, just refuse to listen to me about using read committed snapshot isolation. That means that if we try to run a select from the IndexView, we’ll get some locking that is sort of expected, right? We’re updating rows in the user’s table that affect rows and columns that are in the IndexView.

So, getting blocked here is, like, pretty acceptable. What’s weird. Well, rather, it’s acceptable under the unacceptable garbage isolation level read committed.

But if we also run this and we try to update the post table, the score column in the post table, this gets blocked, too, right? So, let’s come over here and let’s look at SP who is active. We’re going to use my favorite personal way of, why does that say execute?

There are more letters in that word. One of my personal favorite combinations. Of parameters for SP who is active.

Get additional info and get task info. This is a much more lightweight way of seeing blocking. There is the get locks parameter as well. So, if we say, let’s get some locks here.

Get some else socks. If we say that, why are you trying to fight in here, Management Studio? So, if we say get locks equals one, we will, of course, get this locks column.

And this locks column will tell us quite a story. For the query that, oh, that’s the next video. Sorry about that. Now we’re all spoiled.

For the query that’s doing the blocking or doing the locking, you know, there’s not a, maybe, there’s some interesting stuff in here, right? Some of the intent exclusive in the xlocks. And we see the range locks in here.

But this can often turn into sort of a lock for SP who is active to enumerate when there’s a lot of locking going on. So, that’s why I prefer get additional info. And get task info.

Because if you scroll over to this additional info column, for the query that’s doing the locking, it’s not going to show you anything all that interesting. But for the queries getting blocked, you will see what type of lock is being taken, the object that it’s getting hung up on. And so, the select query is, of course, blocked trying to read from the index view.

And the update to posts is now getting blocked on the user’s table. Well, that’s weird. All right.

Why does updating the post table get blocked on the user’s table? Well, it’s all about that index view, right? So, let’s get rid of these things. Let’s make sure we are good citizens and roll back our transaction.

Remember, kids, if you begin a transaction and you just hit cancel, it doesn’t roll back or commit the transaction, right? You have to actually tell SQL Server what to do. And let’s also kill this one here.

We don’t need to do that. We don’t need to keep running that. So, let’s also. We should roll this back as well because we don’t need that thing sitting out there in the breeze. So, the way that you can see what happens is by using a bunch of trace flags.

I believe that if these aren’t in Paul’s post about index view maintenance, they’re in another post of his. So, of course, you know, I take no credit for the discovery of this, but I do want to make sure that it continues to be discovered. So, it’s going in here.

So, if you turn on these trace flags, 8606, 8607. And you run this update, you will get in the messages tab a whole bunch of stuff, right? There is a lot going on in the messages tab.

I’m not going to try to scroll to find all this stuff here. But there are a few points in here that you will see if you run this and scroll through everything. Most importantly, these two lines.

So, on the post table and on the user post score index view, SQL Server internally applies these hints to the query. You can’t see them. They’re not in any of the block process or deadlock report XML.

They’re not even in who is active because, again, the optimizer, the engine, let’s call it, adds this stuff in when the queries execute. But you’ll see the serializable, internal. Detect snapshot conflict in case you have snapshot isolation on.

And then the one on user post score is updelock serializable detect snapshot conflict. So, SQL Server, this is the case where it’s silently upgrading your isolation level to serializable from whatever in order to make sure that the index view maintenance maintains referential integrity. So, very cautionary thing against using multiple tables in an index view.

I believe I’ve said it in other videos. Index views should be on a single table at a time and should be very generic in their aggregates. Typically, you don’t want to gear them towards just one single query because it’s far too specific.

They won’t get used a lot. And the impact that they may have on the entire database ecosystem can be sort of outsized for the amount of benefit that you’re getting from them. In other words, the cost of maintaining that index view is not amortizing or helping many queries.

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

And in tomorrow’s video, we will be looking at something a little bit more query performance indexy. So, we’ve got that going for us. Anyway, goodbye.

Going Further

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

SQL Server Performance Office Hours Episode 64

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 64


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and today’s video, we are going to do a whole bunch of office hours. Won’t that be fun for everybody? Because, you know, I like answering questions, and sometimes I can’t think of all the good questions, so I leave some of the questions asking up to you.

And so you, the fine people of the SQL Server, the greater SQL Server community, the greatest SQL Server community, can send them to me for free. Let’s talk about how you do that. Down in the video description, there’s all sorts of helpful links for you to do things.

So, one of those things is asking me office hours questions. There’s a link down there where you submit a question. It’s wonderful. And there are other links in there, too, that do require a little bit more, let’s just call it, money. Like, you can hire me for consulting, you can purchase my training, and you can even become a supporting member of the channel if you decide that, you know, you’re into, you know, my channel that much.

Like, four bucks a month or something. It’s very, very inexpensive. You won’t even notice it. It might even be tax deductible, because I am a charity. The darling data home for little wanderers.

Anyway, also down in the video description, another free thing you can do is download my open source SQL Server monitoring tool. It does all the performance monitoring that I would do if I worked at your company, and I was like, hey, we need to fix this. We need to fix this.

We need to figure out what’s wrong with these SQL servers. This is what I would do. And you can have it there for free. Again, like, you know, if you have a big expensive monitoring tool right now that isn’t helping you figure out your performance problems, try this one. Again, I make the same amount of money either way.

Zero. But you, you might actually get a good monitoring to a lot of it, which is a pretty good deal. Again, the link for that is down below as well. But, hey, come on.

Why didn’t you work? Anyway, so, like, actually, no, actually, I will be home from Chicago and in Poland when this publishes. So, forget those.

You know, time travel is weird. But coming up, I will still be at Data Saturday Croatia, and I will still be, as far as I know, you know, all things in the world considered, be at Past Data Summit in Seattle, Washington. So we’ll have, well, I mean, let’s just say I’m definitely going to have an advanced T-SQL pre-con here.

Here? I don’t know. Might happen, might not. We’ll just have to see what happens in the world.

Anyway, it is May, and ChatGPT thought May was a real gothy database rainy party, I don’t know, singing hymns to skulls. That’s… GPT got dark. I don’t know.

Anyway, let’s answer some questions. We got questions to answer here. Let’s see, is ZoomIt going to cooperate? How many layers of ZoomIt being uncooperative will we have to deal with? All right.

First question. How risky is plan forcing and QueryStore realistically? Have you seen it go badly? Oh, boy.

Doing what I do, I see everything go badly. So, like, I think the biggest risk that you run with plan forcing and QueryStore is, like, let’s say you’re in the GUI, right?

Because that’s the way most people approach QueryStore. I don’t know why, because I have, you know, SPQuickieStore, which does things a lot better than the GUI. But anyway, let’s just say you’re in the GUI, right?

Because one thing working with SQL Server is it’s a GUI mess. But you’re in there and you’re looking at, like, regressed queries or whatever. And you’re like, ah, when this query ID uses this plan, it’s always really fast and uses very few resources.

And when this query ID uses this plan, it really uses a lot more resources. And then you force the query that usually uses very few resources. And then you come to realize that the query plan that used very few resources did so because it was doing very little work.

And the query plan that was tied to that query ID that used more resources was, of course, doing a lot more work. And now you’ve just got, like, this awful parameter sensitivity problem. So, you know, what I say is always test carefully in those cases.

If you’re going to force a query plan, make sure that you understand what plan you’re forcing it for. Perhaps as far as you can get the compile time values for each plan. If you’re using, like, optimize for unknown or local variables or something like that, you’re not going to see them.

Other things might mess you up, like temp tables, table variables. You’re not going to know what data was in those. You know, if you use option recompile, you get the literal values.

You don’t need to know what data is in there, which you can figure out. But you can also use my free store procedure, SP Query Repro Builder, to help you figure that out. It’s another free tool in my darling data repo.

So if you go there and you get that, you can give it, like, a query ID or a plan ID or just about any other plan identifier in Query Store. And it will try to build a reproducible, executable query for you to run and look at the query plan for and all that good stuff. So, I don’t know.

That was more than I thought it would be there. Anyway. All right. All right. Zoom it. All right. Zoom it. You’re going to be like that today. Wow.

Okay. Is parameter sniffing actually a bug or just misunderstood behavior? I mean, look. I was going to say, Microsoft has had some pretty long-standing bugs. But I’m pretty sure if this were a bug, they would have done something about it by now.

In fact, they kind of did with the parameter-sensitive plan optimization. I don’t have my beefs and grievances with that thing. But, you know, we don’t need to get into that here.

At this point, you know, if anyone misunderstands the behavior, I think that they are just being willfully ignorant. There are 10 billion years of blog posts and informations out there. And, you know, especially now in the modern day of the robot, if you, like, robots love pointing at, I think, well-known problems, right?

Like parameter-sensitive. Ah, classic bug. Ah, classic parameter sniffing issue, right?

They love talking about that stuff. So, you know, I think it depends on how you look at it a little bit. There are other well-known databases.

Let’s say, like, Postgres where there is not really plan sharing. I know there’s, like, a reusable session thing. But, like, if you have, like, 50 different sessions all running the same query, they’re not going to, like, cache and reuse a plan.

There’s not really, like, the concept of that. Right? There’s not really a plan cache the way there is in SQL Server. So other databases do approach this differently.

You could consider that good or bad depending on, you know, what problem you’re dealing with. I generally consider parameter sniffing a good thing. I consider parameter sensitivity a thing that I like to fix.

But, you know, you can make the case for either one being a good approach depending on the workload, depending on the characteristics. I do have one friend of the channel. Who I will not name.

Be decent about this. Who was experimenting with getting his application migrated from SQL Server to Postgres. It’s called Extreme OLTP.

Make him happy when he hears that. But the cost of the connection and compilation of these tiny little queries on every connection for Postgres is a very big penalty for them. Where in SQL Server, it is, you know, like, almost instantaneous because we have the plan cache and we have plan reuse and stuff like that.

Anyway. What kinds of performance problems only show up under heavy concurrency and never in test? Geez.

So many. You know, I think probably the first one that I can think of is, like, deadlocks. Right? Because, like, not every deadlock is just a predictable, like, table A, table B, table C. Right?

Table B, table A. We got in a fight. Like, some of them really are just, like, bad luck and bad timing. And they only show up under, like, you know, actual heavy, like, you know, real world concurrency. You know, ditto some blocking problems.

Though I think blocking problems are somewhat easier to immediately forecast because you’re like, well, this modification query takes forever when I test it. What could go wrong? So there’s that.

But there are all sorts of other ones, too. You know, primarily amongst sort of, like, resource exhaustion. Resource semaphore would be one of them. Thread pool would be another one. Resource semaphore query compile when you have all the queries in the world trying to get through those compile gateways.

So there are a lot of things that show up. And even, you know, before you get to anything that’s, like, monstrously catastrophic, like, you know, like resource exhaustion, big blocking, deadlocking stuff. You know, you’ll run into little things, too.

Like maybe even queries. Maybe even queries will just be a bit slower because there is just resource contention for things, right? Whether it be objects in the buffer pool, whether it be access to CPUs, things like that. You know, something I talk about a lot in my training is, you know, the difference between tuning one query to be as fast as humanly possible or as fast as SQL Serverly possible.

And, you know, sort of tuning a query so that it is a good citizen in a workload, right? Because, like, you know, let’s put it into two pieces. Let’s say you have a very big query, like something reporty, data warehousey, right?

But you’re reporting off OLTP because you’re that kind of crazy person. And you might, you know, look at that query and that query plan. And you might say, oh, well, I have, like, you know, maxed up is set to four.

All right. Well, I have four threads working in this query. Then each processing, like, I don’t know, let’s say two billion rows apiece, right? And you’re like, yeah, that’s not good.

If I run this query at maxed out. All right. I’m going to stop eight. Then I can cut that in half because, you know, we’ll have twice as many threads for the queries to sort of divide the rows to divide up on. So that’s one way of looking at it.

But then the other way of looking at it is sort of like, well, like, maybe not every query can get, like, all the maxed up and all the memory it wants, right? Maybe some of these queries are going to have to run a little bit imperfectly so that when we have our larger workload running, the green screen over there is getting real annoying. And I don’t understand why.

It’s wiggly. I’m just standing here. Like, you might have to, like, sort of have them sort of run in a more cooperative way. So, like, you know, some queries may run imperfectly so that the larger group of queries can all run together. But, yeah, you know, there’s lots of stuff that will only that only shows up if you have that only shows up under, like, real user workload. I think accurate load testing is one of the hardest things in the world right now. I mean, I don’t know. But, you know, a lot of that just just simplyب shows up if you have that only shows up under like real user work load. I think accurate load testing is one of the hardest things in the database. How do you approach tuning when you have zero control over the application code.

Hardware, Hardware hardwa. Now let’s get sometimes it comes sometimesit is Hardware, but you know that’s that’s more of a generalized thing. Most of the time it would be indexing.

You know. and various other things that you can do in SQL Server that can have queries do a little bit better. Let’s say indexing is the first one that would include index views, that would include filtered indexes, that would even include indexed computed columns.

So if I have zero control over application code, but I have carte blanche for the schema and anything else in the database or that the database is sitting on, then that sort of becomes the realm that you have to work in, right?

Is it ever safe to fully ignore missing index DMV suggestions? Sure, I do that all the time. The thing with the missing index DMVs is like, there’s a lot of gaps and holes in them.

They are very opportunistic. They are part of query optimization. So like SQL Server isn’t spending a long time thinking really hard about the index that it’s asking for. It’s very where clause, centric, will often ask for very, very wide indexes, like the sort of normal spate of complaints about the missing index suggestions.

So yeah, I do ignore them a lot. Where I don’t ignore them is in query plans that I’m tuning.

So the chances are, if I’m looking at a query that is not running as quickly as I want it to, then there’s probably something that I want to do to fix that. One of those things might be adding an index or modifying an existing index.

So what I’ll usually do is I will take the query, I will get an actual execution plan for it, and I will see if the missing index suggestion or any of the missing index suggestions line up with a slow part of the query plan.

And if I think that adding that index will indeed positively affect that slow part of the query plan. But from there, sometimes I will go and look back at the missing index DMVs and say, okay, how often would a query theoretically use this?

I can see how many times a query is executed in various ways, whether it’s the plan cache or query store, but I still need to deal with figuring out, is this index good generally for the workload?

Not just once a day if this query runs, but are there many other things that would use this? Anyway, this is really annoying me, so I’m gonna go try to fix it.

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 T-SQL-y, I promise. God, green screen is a real.

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: Indexed View Matching

Posted on by Erik Darling

Learn T-SQL With Erik: Indexed View Matching

Chapters

  • 00:00:00 – Introduction
  • 00:02:45 – Creating the Table with a Computed Column
  • 00:06:39 – Index View and Filter Definition
  • 00:08:17 – Non-Parallel Plan Reason
  • 00:10:34 – Scalar UDF Not Parallelizable Warning
  • 00:12:07 – No Expand Hint Effect
  • 00:12:57 – Summary and Next Video

Full Transcript

Erik Darling here with Darling Data, and in today’s video we’re going to continue on the Learn T-SQL voyage that we have started, and I’m going to talk today a little bit about indexed view matching, because SQL Server is, let’s just call it a mature, or an experienced database engine, and is quite capable, at least in Enterprise Edition, Standard Edition, you do not pay the Microsoft Friendship Tax, so you will be taxed performance-wise, but is quite capable of matching base queries to an indexed view where the syntax matches in some way between them. So, usually exactly between them, not in some way, usually pretty close to just about what you would ask for.

But that’s why, you know, it’s important when you’re, if you’re going to go down the indexed view path, and I do, I do want to say that if you’re going to start creating indexed views, you know, you should be very careful with them, you know, usually you want to limit them to just involving one table, and you want to write indexed views that are sort of a general purpose variety. And that’simo Scrum, and the more obvious, like the number of times, you want to stay in one table because that’s very common.

And you aren’t really going to send a lot of pub, or a ton of files, or not a ton of files back into the indexed. You’re going to have to deal with a diverse set of Mmmm. Repetitive Data or Web connect devices and all sorts of things, in terms of the three sections.

But a lot of this being ordered is flip side up a lot more often than previous, and I work with native comic books, of course. And I would like to turn it up a little bit now with a full-on summary of some 할게요 and that kind of, cause Iersh entrepreneurial app. CAR N stick?

store still has quite a bit of hobbling on standard edition and so on standard edition you just might need to go down the index view path. There’s also something I think to be said for having the data already pre-aggregated for you in some cases because look as powerful as columnstore is you know you still have to do the work to get the result if you have that data pre-aggregated you’re skipping a lot of work in a lot of cases but you know anyway I’m going on too long without showing you anything interesting rambling babbling like a brook let’s let’s talk about some stuff and then look at some management studio goodies down in the video description you can hire me for all sorts of helpful things for you mostly well I mean it helps both of us right like you get expert SQL Server help and or training and I get money so that’s that’s that’s that’s that’s that’s that’s that’s that’s that’s that’s that’s that’s that is a really fair deal I think you can also become a supporting member of the channel if you’re like wow Eric thanks for doing all this YouTube and for us you sure sure put in some hard work you can support this channel monetarily again links are all down down down below that’s why the fingers are pointing this way and of course if you’re just I don’t know if you’re you’re too poor for words or I don’t know maybe you just don’t like me that much you can always say well maybe someone else will like them better and you can you can like subscribe and tell a friend who might like me better right because that’s what friends are for even if I mean your friends not where I don’t know about you and me maybe someday what we have to meet and sort some things out I think if you would like absolutely free no strings attached SQL server performance monitoring I have my free open source monitoring tool available on github that’s a link but there’s a link down in the video description below if you want to check that out as well that’ll take you there you know just average ordinary everyday T-SQL collectors brings all the important performance data in you know the stuff that I would care about and look at if I were performance tuning a SQL Server on my own makes them up puts them in all pretty charts and graphs and whatnot and one thing that’s pretty cool is if you want if you optionally would like to use your robot companions to help you analyze summarize or analyze about your SQL Server performance problems you can do that there are a whole bunch of read-only built-in MCP tools that allow you to interact with your performance data and just your performance data for weeding out your problems I think when this gets released I will be in Chicago so it might even be May 7th and 8th this comes up no I think I think it’s probably maybe just the seven uh let’s see um actually maybe not I don’t know whatever uh so uh you will have missed your opportunity for pass on tour uh but you would still be able to get to go to SQL day Poland and see me so that would be cool if you did that I have an advanced T-SQL pre-con there uh I’ll be hopefully wowing my my friends from Poland and the surrounding surrounding climbs or areas or whatever uh with with my T-SQL wit and wisdom um after that uh home for a little bit uh then I will get data Saturday Croatia June 12th and 13th uh I also have an advanced T-SQL pre-con there uh while I’ll be uh wowing my Croatian friends and friends from the surrounding areas uh I hope some Hungarians show up so I can I can talk about being part Hungarian and then saying yeah okay you’re American uh I’m gonna say yeah yeah you’re right but I had a grandma and they’ll say great anyway uh after that uh presumably home for even longer and then it passed data Community Summit in Seattle Washington November 9th through 11. uh where uh undecided events will unfold uh as far as my my speaking goes but for now uh we are still Maying about being real rainy cold May people um bringing out the skull I guess uh ghosts of Springs past haunting us reminding remember remember when this used to be a beautiful time of year not this year damn it anyway um so the the thing that I want to talk about uh with index View matching is uh how like the optimizer even even if it matches your indexed view uh will always expand the view as part of query optimization it just does that um even if very early on it’s like yo index view why it matched to you it still expands that and like looks at stuff and that can have some weird impacts not only on your query plans but also on performance. I want to show you a very simple example of that with some stuff here. So like, you know, like in a lot of videos, I’ve talked about, you know, like, ah, you make a computed column, the filter definition of a filtered index can’t go on it. So you can sort of like work your way around that a little bit with index views. And I want to show you that. But I also want to sort of like prove out a little bit in the full video material. This is, of course, you know, this is I have a terrible salesman. This is, of course, part of the Learn T-SQL with Eric course. So the full like module material, I go way in depth on this with like trace flags and stuff to show you what’s going on. But in this one, I just want to show you a shortened version to hopefully whet your appetite for buying the full version. You know, it’s what you what I made it for so you can buy it, right? So let’s drop a view and a table if they exist.

And what I want to show you in this one is a simple table with something that I would normally find absolutely abhorrent and scream, holler and curse at people about. And that is this table has a computed column in it. And that computed column has a scalar user defined function in it, right?

One of the absolute worst things you can do to a table in SQL Server is this, don’t do this, do not follow this. But sometimes you have very, very big tables, that have these things in them. And sometimes you need to figure out a way to get your queries to not get beat up by them. And you put a scalar UDF in a computed column, or a check constraint, or a default constraint I learned recently from a brave and bold YouTube buddy.

It messes up parallelism for any query that touches that table. So if you have what even even a small table, and you join that small table routinely off to larger tables, where parallelism would be of some unknown benefit, you will not get it, right? Even scalar UDF inlining does not help you with computed column, check constraint or default constraint scalar UDF.

So we don’t we do not like this, we do not enjoy this. But we’ve got this situation here. And I think I created the table, I think instead of just pondering about it, that’s good. So of course, you know, we can’t create a computed column where the filter definition hits that, right? So fine.

But we can create a computed column where that’s the filter definition in our table. Because, you know, we can create a view on our table. And this can make a lot of sense and we can maybe want to try to get around that, you know, we can create in a view on our, on our on our table.

And we could even index that view. But what, you know, further annoying things in our lives is that we cannot create a filtered index on an indexed view, right? So this just extends even further into the things that don’t cooperate with each other in SQL Server.

into the index view, or rather into the view, then create the index. The thing is, you will still find the same problems unless you use a no expand hint.

And this is because SQL Server’s query optimizer, like I said before, really does expand those index view definitions. So let’s start off by looking at a select from the base table, right?

We run this, we actually, we end up using the index view, which, you know, great for us, I guess. I don’t know what that proves. Maybe that, you know, T-SQL’s, or rather SQL Server’s cost-based query optimizer really does do index view matching, but we have this warning, this non-parallel plan reason.

In earlier versions of SQL Server, you’ll just see a generic, could not generate valid parallel plan, but newer versions of SQL Server, we get like a reason, right? T-SQL user-defined functions not parallelizable. Oh boy, right?

Nuts, as they say. Even if we select directly from the view, right? And we say, SQL Server, I bet you’re not gonna do anything stupid now. SQL Server says, oh my God, you’re gonna hate me.

And we get the same thing here, right? So whether or not SQL Server does all that stuff, we still get the bad effect of the scalar UDF. However, if we add a no expand hint to this query, right?

We’re still not getting any results back. Notice, actually, one thing I should do here is I should run all three of these at the same time. Notice in the execution plans for the first two, we have a compute scalar, right?

And that compute scalar, if we’re having a very lucky day, right? Let’s see, is it gonna give it, let’s see. Well, no, it just tells us the column name. It doesn’t give us the full thing.

But the important part here, of course, is that the third query with the no expand hint does not have that compute scalar operator. There is nothing betwixt or between the stream aggregate and the clustered end.

Dot, dot, dot. What are you? I don’t know. But this query, I mean, obviously, there are no rows in the table or in the view. So it doesn’t help here.

But notice the lack of a non-parallel plan reason. It’s always difficult to prove a negative like this. But you’ll notice that we do not have anything in here that says non-parallel plan reason, the T-SQL scalar UDFs, yada, yada, right?

Whereas with these ones, of course, that thing, it does show back up. If we just tinkle back and forth, and then it’s gone again.

So the no expand hint can be quite useful beyond just getting SQL Server to directly match to an indexed view. When you query it, it can also keep SQL Server from doing some of the expansion activities that it is wont to do when you get involved, when you jump in bed with indexed views.

So anyway, that’s about good here. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video. Oh yeah, I did talk about that there. All right, let’s get rid of that index view.

Actually, no, there is no tomorrow’s video. I will see you in Tuesday’s office hours video. Forget. I’m taking long weekends now. It’s great. I get to rest, relax, do my hair, tidy up my face a little bit.

It’s beautiful. Drink more. It’s a good time. Anyway, goodbye.

Going Further

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