SQL Server Performance Office Hours Episode 65

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.