SQL Server Performance Office Hours Episode 67
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:04:08 – Memory Grants and Sorts
- 00:07:59 – Suspicious Plan Operators
- 00:10:26 – Batch Mode vs Row Mode
- 00:12:19 – Conclusion
Full Transcript
Your best friend, Erik Darling, here with Darling Data, and we have another absolutely thrill-filled episode of Office Hours For You. This is where I answer five SQL Server user-submitted questions about anything you want to ask about. It doesn’t have to just be SQL Server. If you want to know anything about me, my life, aside from SQLs, I mean, I know that, like, you know, it’s cool to get free advice from someone who charges for advice about SQL Server, but, you know, if you ever just want to get to know me better, the human Erik Darling, you can ask other questions.
Anyway, my hair looks weird today. I don’t know why. It looks lumpy for some reason. I can’t quite figure that out. Ah, I’m not going to get over that. Anyway, we’re going to keep rolling here. I’m going to try to contain my embarrassment at the shape of my head, and we’re going to soldier on here.
If you would like to hire me for consulting, buy my training, become a supporting member of the channel, or continue to ask me Office Hours questions, all of the links to do that are down below in the video description. Every single link you possibly need to achieve any of these goals is down below.
And of course, if you would like to help my channel gain a larger audience, then please do like, subscribe, and tell every single one of your friends to do the same. If you would like free SQL Server monitoring, boy, howdy, I got it.
I’ve been doing a lot of internal work on that thing lately, not a lot of user-facing stuff, but stuff to make my life easier and maintaining the project, hopefully. And I am working on some exciting new stuff, which will all be revealed in short order.
But for now, if you want to download the current implementation of things, you can go to my GitHub repo. Again, this link is all down in the video notes. Video description, rather.
Totally free, open source, no strings attached. You download it, you point it at a server, you start getting useful performance information. I recently hit the 10,000 download mark, which I am very happy about. The emails about people divorcing their paid monitoring tools and using this instead have been steadily coming in, which of course brings my heart great joy and glee, because there is no reason that people should be paying for such…
much junk these days. Anyway, out in the world, Data Saturday Croatia, steadily getting closer. Boy, oh boy, just a couple of weeks away at this point.
And then I guess I’ll be doing nothing for a little while unless something interesting comes up. And then, of course, there is PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So we will have a joyous time there.
And since this will be publishing… in June, we have switched to the June image in which our database is including this faceless wonder over here. I don’t know where your face went, database, I’m sorry.
I tried to… I forget what the prompt was, but the end result just made it look like a bunch of databases were, like, in that movie Midsommar having, like, a real bad time in a field somewhere.
Yeah, I think they’re all going heat crazy or something. Let’s just leave it at that. Sort of some strange leg situations over here.
I’m not sure what happened with this one. It’s like some sort of appendage there and then over here gets a little dicier. I don’t know.
Anyway, let’s answer some questions before I get too involved in the analysis of that picture. Because, you know, try not to judge art too harshly around here. Anyway, let’s see.
First question here. How do you decide between a store procedure versus a function? Versus a view for things like WhatsApp blocks and SP Quickie store? Well, the store procedure one is easy. If I need to have a variety of parameters and variables and temp tables and loops and all sorts of…
And error handling, dynamic SQL and stuff, then I need a store procedure. I think for most other things, I would prefer to write an inline table-valued function. But I guess the design choice there is maybe…
Again, I know I have, like, WhatsApp blocks, which is a table-valued function. And I think the reason that I chose that there is because there were a very common set of parameters that I would usually pass into that.
And it was easier to sort of just remind myself of them in the definition there. I do have one that is a view called WhatsApp memory. And since I didn’t find myself often filtering on things in there, usually just, like, show me everything and sort it, I just sort of, I just left that as a view.
But there wasn’t, like, I wish that more people would use inline table-valued functions instead of views because they offer sort of better placement of parameters and stuff. So most of the time, I would prefer to not use a view because, I don’t know, then it’d be one of those view people that everyone yells at.
Anyway, when there are multiple seeks on an index, does that suggest that the index gets seeked once per seek key? Yes, I have a couple of videos about that.
It is called multi-seeks or dynamic seeks. And that does indeed mean that the index gets seeked through once per seek key, which can hide a lot of work that you may otherwise avoid with just a single seek through the index.
So careful on that. How much do sorts actually hurt? And when should I worry about?
How much do sorts actually hurt? And when should I worry about? Well, they can hurt quite a bit. Sorts are what’s referred to often as a size of data operation because you must write down all of the columns that you are selecting in order of the columns that you are ordering by.
And over a large enough result set, that can get pretty… turn out to require quite a lot of memory to do. You sort of don’t want to end up in a situation where you have very, very large memory grants stealing lots of memory away.
So that’s why you want to keep your memory away from your buffer pool, because you know, like I said in many videos, most servers that I look at are undersized from a memory perspective. And you know, they need a lot of help to sort of balance the data to memory ratio out.
But as far as when sorts start hurting, well, you know… So let me go through this in a little bit of mental detail here. So obviously large sorts are the obvious one that you would want to track down and sort of look at, right?
you can look at the plan cache or a query store by the size of a memory grant that a query is getting. The plan cache, for some reason, offers more details about how much of the memory grant the query actually used, but you can still look at the size of the memory grants in both. You would look for anything using a large grant and see if there’s a sort operator in there.
That being said, in versions of SQL Server older than 2019, where you don’t have the in-memory TempDB stuff available to you, even small sorts could hurt and cause TempDB contention. I saw this on servers where there was a little execution plan, and the sorts would sometimes get an undersized memory grant and just spill a little bit, but if this query ran a ton altogether concurrently, then all of those sorts would cause TempDB contention. If you’re seeing that, then…
You could address that either via indexing, maybe if you’re on 2019 plus the in-memory TempDB metadata feature helps quite a bit with that, but large sorts are a bit easier to pin the tail on that donkey. The small sort TempDB contention thing, that’s a very high concurrency problem that you might have to deal with. I think in general, I’d probably just look for the big sorts first because those are the ones that are most likely to cause issues with the workload because, A, they’re going to be stealing memory from the buffer pool, and B, refilling that buffer pool space, knocking a bunch of data pages out of the buffer pool, bringing them back in later.
That’s going to cause a lot of variance in your query performance. What plan operators instantly make you suspicious something is wrong? Well, in select query…
I would say spools of any variety make me think, I could do something better here. But very specifically, the eager index spool or the lazy table spool, aka the performance spool, those are two things that give me pause on every single occasion when I see them. Again, not because the spool is evil. The spool is just trying to help. It’s Microsoft that’s evil because they haven’t touched the code in spools, and since SQL Server 7, not 2007, SQL Server 7, not 2007, SQL Server 7, that is the 90s, and so spools have not benefited from any of the sort of improvements that like other temporary objects that when in tempdb have had like temp tables and table variables and stuff.
So spools are one of them. In a modification query of any variety, spools are usually there for Halloween protection, so they don’t catch my eye as much. There’s those. I think the sort of constant scan, like merge concat, like sort into a seek thing, that’s one that always catches my eye because that usually, that either means that you have a join with an or clause, or maybe you have like a mismatch data type, like you’re comparing dates and date times. Like maybe you have like a date time column or a date column, and you’re comparing it to a non-precise match to a date time column.
Or a temporal data type, like a date or a date, right? So I guess I would go for date time too as well. That’s another pattern that I look for. Parallel merges, parallel merge joins, that’s another big one, stuff like that. So top above a scan is another pattern. So it’s not often a single operator outside of spools. Often it’s a pattern of operators that I see that make me suspicious.
Is batch mode always better, or are there workloads where row mode wins? For me, batch mode is really only better when you have a lot of rows that you need to do something with. For me, batch mode is not necessarily better if I’m doing very tiny OLTP-ish things, little seek point lookups. There’s just not a lot of benefit to batch mode in there.
You know, batch mode used to unlock a lot of extra optimizer stuff that row mode has slowly… been inheriting. So for me, no, batch mode is not always better. Batch mode is a very specific optimization for large scans, aggregations, hashes, hash joins, hash aggregates, stuff like that.
You know, I guess the adaptive join thing is nice, but you know, if you’re just working with like sort of reliable OLTP data where there are not large skew-sensitive portions of the data, then batch mode doesn’t really offer all that much as far as, you know, benefit goes. Anyway, that is five questions. One, two, three, four, five. I did it. Counted right this time. Good for me. Five fingers, five questions. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we’ll talk a bit more about aligning and designing indexes and queries. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.