ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I delved into some detailed SQL Server performance tuning scenarios and queries related to memory management and query optimization. I started by discussing the importance of monitoring memory usage in a database environment, particularly for an OLTP system where user queries and reports are frequent. We explored how to identify and mitigate memory pressure using tools like SP_BlitzCache and resource governor, as well as how to manage memory grants with hints like `MAX_GRANT_PERCENT`. For ETL processes in data warehouses, we discussed the potential impact of memory pressure on columnstore index compression and load times.
I also addressed a specific query from Justin about singleton lookups, explaining that these are typically associated with key lookups, especially when using nonclustered indexes to find partial data and then needing to retrieve additional columns from the clustered index. We used `SP_BlitzCache` to track down expensive key lookup warnings and reviewed how `sys.dm_db_index_usage_stats` can provide insights into index usage patterns in query plans. The discussion highlighted the importance of profiling workloads and capturing detailed query plans to diagnose performance issues effectively.
Full Transcript
All right. There we go. Yay, audio. Yay, audio. I don’t know why this thing decided to start picking a different microphone, but now I just have to remember to check that every single week. The rest of my life.
Now this is, this is a kind of thing that’s going to make me move to Twitch. I can’t share screens. I can’t have multiple people. The number of things I can’t do is amazing. So yeah, I’m thinking about getting off of YouTube for this.
As much as I like being able to… Yeah, sorry about that, Forrest. I don’t know why. I don’t know why. I couldn’t tell you. This is, this is why I’m thinking about switching this thing over to Twitch, because YouTube is very limited in what it allows me to do, aside from a stand here, like a monkey looking for props.
Like, this headband that my daughter made me. Unfortunately, the flower has fallen off of the headband. I’m sure I’d look just as creepy in it either way.
So, I’ve got that going for me. It’s a good time. I also, I love the smell of gardenias, gardenia flowers. I like magnolias too.
And silver linden, I believe they are. But I particularly like the smell of gardenia. So, I bought a candle. It said gardenia on it. There it is. Gardenia. And what I didn’t realize when I bought it, is that it’s a creepy candle.
Because when the candle melts, it turns into massage oil when poured. Melts into massage oil. So, now I’ve got this creepy candle in my office.
And that’s that. It smells nice though. It smells wonderful. It smells exactly like gardenia. I don’t know how they do that. Fantastic. Fantastic scent of gardenia. I just have to forget that it’s creepy.
It’s a creepy candle. I don’t know. I don’t really have anything else interesting over here. Right now. Yeah.
This is it. So, let me ask. Are you able to access Zoom conferences from work? Do you use Zoom at work? Do you use Zoom at work? Not Zoom like the MP3 player. Zoom like the thing that SQL Server does.
Z-O-O-M. Because Zoom offers a little bit more flexibility. It’s not quite as. Yeah. So, Zoom is a little bit better for that kind of stuff.
But. You know. It’s not quite as. Just show up on my YouTube channel and hop right on. It’s kind of like. I’d have to. Set up. Invites or. I don’t know. It’s weird. Anyway. I would like to collect as few email addresses as possible.
For. For things in my life. Anyway. Uh. Yeah. So. Maybe I’ll do that. And. And tech.
And tech. Not tech. The technological news. I. I ditched my Fitbit. I have thrown my Fitbit away. The thing is useless. It has never given me an accurate reading. On anything. Ever. Not sleep.
Not heart rate. No. Gone. No. It’s not a cardio thing. It’s a. I don’t know. I. I just don’t. I don’t think. I don’t think. Me and the Fitbit ever.
Ever quite got along. I think we are always at odds. Because. I would do things like. Sleep. And it would do things like. Tell me I’m awake. For hours. I don’t know. Anyway. We do have a technical question this week. Can you believe we have a technical question?
Hello Alex. Hello. Gather Uncle. Hello everyone. Uh. We have a technical question. From. My friend Ted. Who unfortunately can’t be with us today.
He’s not dead. Don’t worry. He just. Had other things to do. And. Ted says that. He has a server. That he thinks is under memory pressure. And when he looks in. Sys. Dm. Os.
Memory. Clerks. To see how memory is being used. He sees what you’d. What will. Something you wouldn’t expect. For an. OLTP or server. Now. This server has a 360 gig. Database on there. Uh.
Ted didn’t mention. Ted didn’t mention. How much memory. The server has. But. Uh. He did say that when he looks in. That dmv. Um. That. Let’s see if I’ll make sure I read this right.
See the SQL optimizer. Entry has. 22.4 gigs. The object store lock manager has 5.6 gigs and SQL buffer pool only has 1.4 gigs. Should I. And he wants to know if he should be concerned.
Be concerned about that. And. The short answer. The short answer. Is yes. Yes. You should. Because. The entry that you’re seeing. The.
The entry that’s in there. For. Uh. Memory clerk. SQL optimizer. Is. The memory clerk that gets. Populated when. Uh. You are giving out memory grants. To queries.
So when you. Have a query that does a. Big sort or a big hasher. You. Small ones. You have to give that thing memory. And when you give that thing memory. It has to come from somewhere. SQL Server and windows do not go into cahoots.
Or collusion in order to make more memory for you or to. Compress things in memory or. Do anything else. Though. What happens is you. You end up mostly taking memory from. The buffer pool. And.
So. One other way that you can validate this is if you look in. Uh. Some of the perfmon counters. If you look at the stolen pages. Perfmon counter. You’ll be able to see. How much. Uh. Aggregated. Uh.
Memory has been stolen away from the buffer pool. So. The. Quick answer is that yes. This server is definitely under. Memory pressure. And yes. You should be concerned. But. You should also try to correlate it with some events. So.
First. I think what I would look for. Is. Resource semaphore weights. Because resource semaphore weights are most likely going to show up when. Queries are waiting on waiting to get memory to run to do those. Sorty hashy things.
We don’t want to judge the severity of the memory pressure. So we’re going to look at stolen pages. We’re going to look at look for resource semaphore weights. And. And then. Uh. Well. After that.
You know. You’re going to have to figure out a way to figure out when those weights are happening. Right. So you could try logging SP who is active to a table. If you don’t have a monitoring tool. You could get a trial. Monitoring tool. I would suggest. Sentry one performance advisor. Uh.
Get a trial of that. Set it up and try to figure out when. These memory weights happen. You can be able to see very clearly in. In the. In the dashboard there. When. Memory is tanking. For. And. Hopefully be able to track it down to some query activity. So like.
You know. Stuff that is definitely. Going to. Um. Cause memory issues. Uh. If you. Have. You know. 360 gig database is your biggest one. Well. If you. If you don’t have. A lot of memory on that server. Say.
That server is a standard edition box of maybe. 64. 96. Or 128 gigs of RAM. You’ve got way more data than RAM. And when you need to do things like run check DB. Or. Read a big table in some other way. And it has to come from memory to. This is going to knock a lot of other stuff out of.
Out of memory. And then on top of that. You’ve got. The potential for. Query memory grants. To be at odds with even things that you’re trying to read into memory. Just. Gnashing teeth.
Together. So. Yes. I would be concerned. But I would also want to. You know. Measure my. My. My concernedness. Make sure that I am. It. Like. It makes you wonder if. This is just something like.
Like. You were running some scripts you found out there on the internet. You just. Went and hit F5. Some stranger said. Hey kid. Run this script. And you ran it. And. You saw that. You saw this happening. But you know. It’s one of those things where it’s like. Well. Are users complaining about it? Is this something that.
That happens. During maintenance. Like. Say you rebuild a bunch of indexes. Or reorg a bunch of indexes. Read a bunch of stuff on into memory. And then that happens. I don’t know. It’s lots of things to think about. Because. Index rebuild and all that require. Memory too.
When they. They sort data into index key order. Every single time you run them. Crappy. Crappy. Anyway. Gazaranco. Says. Is. On a similar subject.
For ETL DW server. Would you care about memory pressure. E.g. Truncating everything. And inserting each day. So not OLTP. I would not. Well. That depends a little bit. So if it’s a data warehouse.
And. You. Are using columnstore indexes. And you are coming under memory pressure. You can end up. With. Poorly. Compressed.
Row groups. Because. The memory pressure will not allow. You to read in. Big row groups. And compress them. Something like that. Joe Obish explained it once to me. And this is as much as I can remember. My. Louvre. Addle brain.
But yeah. So there. There are times when I would worry about it. If you’re using rowstore indexes. Perhaps a bit less. If you’re hitting. If you’re hitting memory pressure. For. Load queries that are. I don’t know. Again. Sorting. Hashing things. It might slow them down a bit.
But. You know. That’s. That’s kind of up to you to figure out. If I’m hitting a lot of memory pressure. When users are running queries. Or when reports are getting generated. Then I might pay a little bit more attention to it. You know. If. So. If ETL load times are cutting into.
When people need to run reports. And sure. I would worry about memory pressure. Then. Because perhaps memory pressure. Is the reason that. Things are slowing down. And if. You know. Because you don’t. You don’t want loads to still be going on. When people are trying to generate reports.
If that’s not happening. And people are just. Hitting. Memory pressure. When they run reports. Well. It’s a little bit of a different story. You know. You do need to exercise. Some. Caution. And. You know. The way that.
You. You let people do things. So. There. There are. A couple of things that I would explore. Maybe. You have. A bunch of processes. That.
Are all asking for too much memory. In which case. You could. Use resource governor. Or if you’re on a newer version of SQL Server. Like 2012. SP3 plus. Or something. You could use the max. Grant. Percent hint. To limit the amount of memory.
That a query asks for. You can reduce memory pressure quite a bit. By reducing the memory grants. That queries are asking for quite a bit. You know. If you have. So. What I would. Do is. Try running.
SP blitz cache on there. See if. Any of your queries. Are getting the unused memory. Grant. Hint warning. You might see that. If you’re on. A new enough. Version. You might see that in there.
If it’s. If it’s in the DMVs. Other things that you could look at. Well. You do. Kind of be on a query by query basis. You would have to. You know. You would have to profile queries. In some way to. Like. Use extended events.
Or. Profilers. Like. I don’t even know. I don’t even know if that’s in profiler. Jeez. You might have to just use extended events. Or. Or. Or. Or trace things. In a specific way. To see. How much. How much.
Like. You could use. Sys.dm resource. Semaphores. And. The memory grants. One. To see if. Queries are. Using. The amount of memory. That they’re being given. Stuff like that. So. That’s one. That’s what I would look at. That finishes in time.
I just feel it could be quicker. Well. I don’t know. What makes you feel that way? Is it. Like a. I guess. Or. Is it. Rooted in fact.
At some point. I don’t know. I don’t know. I don’t know. What you’re up to over there. You crazy kids. Your ETL processes. Let’s see. Justin. Justin. Has a question. I’m going to go for this. Justin.
I’m not going to read ahead. I’m just going to read the question. As I look at it. I’m. I’m avoiding. I’m averting my eyes. So I can’t see it. Justin says. Could you shed some light. On what events increment. Singleton lookup count. I have an index. It has zero scans and seeks. But millions of singleton lookups. Yes.
Usually. Key lookups. So where you. Where I see that most commonly. Is with the primary key. And or clustered index of a table. Where you have. Say some narrow. nonclustered indexes. That help queries.
Find certain bits of data. But they don’t cover all the columns needed. By. The rest of the query. Say that. That. So you have like a single. Column index. God forbid. On. On a table. And. You know.
Your query is like selecting three columns. From that table. Where your. Single column index. Equals something. And so SQL Server says. I’ll use you. Little index. I will. Find this data. That I’m very interested in. And then I will. Go back to my cluster index.
And I will find. I will get the rest of the data needed. For this. Query. So that’s usually when I see it. That’s what I would look for. Again. You could. If you’re. If you’re. If you’re. If you’re. If you’re. In perhaps tracking the source query. For some of this stuff down.
What I would do is grab. SP bliss cache. From. The first responder kit. And I would. Run that. And I would look for. Expensive key lookup warnings. If you see. Expensive key lookup warnings. You may have found your culprit.
If you. Crack open some query plans. And you see. Key lookups in there. And all that. And you. Of course. May have also find your. Culprit. But yeah. That’s usually what I see. Driving that. That thing to take up. It’s a lot of fun.
Learning about this stuff. Isn’t it? A lot of fun. The other fun thing. About. Sys. Dmdb. Index. Usage.
Stats. Is that. They. Will. Show you. Or. Rather. Sys. Dmdb. Index.
Usage. Usage. Stats. Is that. They. Happened. Or show you. Usage. In query plans. If that. If there’s an operator in the plan that is accessed. That table in some way. Even if that operator doesn’t execute.
Fun. Operational stats is even crazier, but. We’re not going to talk about that. Justin says I have searched the last three months of query plans and no lookups on that guy. Oh, I don’t know. Were there any.
Was it on the inner side of nested loops anywhere? Where do you have. These query plans saved to. These three months of query plans. That’s what I would be. That’s what I would be very interested in.
Where did you. Where did it come from? And see it just updates. Oh yeah. I’m not sure then. You might need to. Profile your workload in some way that allows you. To capture queries that specifically touch that table.
You know, like there’s a lot of reasons why plans don’t get cached. Or why plans might not be. Collected for various reasons. You know, recompilations hit like saying recompile recompilation events. Using temp tables, you know.
Server memory. There’s all sorts of reasons why you might not see something in there. Redgate SQL monitor. You know. You know. You know.
You know. All right. Uncomfortable silence. I might want to. Perhaps try monitoring the server in a different way. That. It captures things a bit differently. Would be.
Would be. Would be my first suggestion. Again. Sentry one performance advisor does a very good job of capturing. Plans like that. And you know. Telling you when they ran and what weight stats went on. And kind of what they did in there. And you can even query the.
The repository directly. To. You know. I believe they might log. Stuff like that in there. Because I know that when. You open plans up in plan explorer. They tell you the. The lookups. And all that stuff. And I know that plan explorer is built into performance advisor.
So. There’s all sorts of stuff that you can find in the repo that. That might. Might show you. A different perspective. On your server. Anyway. No problem. Happy to answer. Happy to. Have something of moderate value to contribute.
Once in a while. Once in a great great while. All right. Let’s see here. Uh. Uh. Uh. Well. Okay. All right. No questions over there. We have questions over here. No.
All right. It’s a 500 gig index. And I was hoping to get rid of it. Is that clustered? Non-clustered? What kind of index is that? Non.
Oh. Oh. You have. Lookups against a non-clustered? Well. Then. You’re not going to find key lookups there that help. I would just look for where. That call. That. That index is just on the inner side of a nested loops join. Perhaps.
That might. That might. Might. Might lead you to some. Might. Might lead that horse to water. Uh. 500 gigs. Yeah. Someone. You could just take that column out of the index. I mean. That’s what I would do. There’s no.
No need to have multiple copies of that. That’s. Well. I mean. I could say there’s no need. So like if. You know. Say you ever. God forbid. Hit. Database corruption. It might. You might be thankful. Someday that someone is like. Oh yeah. We have.
We have another copy of that column in this nonclustered index. We can just. We can just put it back from there. But. You know. Oh. I would. I would much rather just have a good backup. Cause now. You know. Your backup is going to be 500 gigs bigger because of that index. Damn it.
I’m sure it’s not just that columns fault. I’m sure there are some other bad choices in there. But. Who am I to judge? Who am I to judge? Who am I to judge? Who am I to judge? Who am I to judge? Who am I to judge? Big old nobody. All right.
Do we have any other questions? Does anyone else. Have something they want to know about? Cause I am ready to go to bed. Ready for sleep. I mean.
Maybe a nap or something. I don’t know. I don’t know. I don’t know. I don’t know. Peter says, I always read the docs to mean that. An index on a VARCAR max is only an index. On the VARCAR 900 portion of the field. Uh.
I’m not sure that. That’s the case for. Included columns though. And that’s the thing. Is included columns don’t have any restrictions on. All right.
Alex says, I’ve got a couple of tables with aggressive indexes. Total lock weight times greater than five minutes row and page with short average weights by SP Blitz index. There was a high number of lock escalation attempts with zero escalations. All are clustered IDXP case.
What is the best course of actions to resolve the issue? Um. Well. That’s a fairly easy one. If you don’t have any nonclustered indexes on the table, then you’ve got your answer. Is that everything that goes in and out of that table, all the traffic, whether it’s a modification, you know, insert, update, delete, or a re query just to select has to go through somewhere. And those indexes are that indexes that somewhere.
So it might be a good idea to check out what indexes you have on there. You know, if they’re, if you just have a clustered index primary key, no missing, no missing index request doesn’t mean anything. That means nothing.
Nothing. Missing index requests are low level garbage. Low level garbage. Um. So what I would do is pay very careful attention to, um, a couple things. One.
Modification queries that hit that table. Probably that have a where clause. So like an update with a where clause or a delete with a where clause. Uh. And make sure that you have indexes that support seeks for your updates and deletes. The other thing that I would do is.
Um. Make sure that. If I am modifying that table, I’m not doing so in gigantic chunks like, you know, 100,000, 500,000, million rows plus at a time. Uh.
Uh. Because that’ll certainly lead to way more lock escalation attempts. So when I see a lot of lock escalation attempts, I can, I can tell that either you’re doing very, very big modifications to the query and SQL Server is attempting to lock the table rather than take row or page level locks. And that usually happens either when modification queries need to have a where clause or something where they need to find data.
And they don’t have an efficient way to find that data or where you’re just saying update. Hey, most of this table will do something crazy. And, and you end up trying to update like a bunch of RC lock escalation happens. To simplify this a bunch.
SQL Server will attempt to. To escalate locks when it hits around 5,000 row or page locks. And it’ll attempt to escalate row or page locks up to a table level lock. It doesn’t go row page table. It just goes row table or page table. So I can tell that either. You don’t have the right indexes, which is why SQL Server can’t find exactly what it needs to lock.
So for example, it might say, well, I was going to take a bunch of these row locks, but I can’t because I can’t find rows. And say I’ll just lock a bunch of these pages and then it ends up just saying, oh, we need way too many pages here. Let’s go for the table.
So that, that definitely happens when either you’re doing big modifications. And let me go grab my, probably the most, I wish I had a tracker on how many, how many times I’ve sent people this link. But let me paste into chat a link from Mr. Michael J. Swart, my favorite Canadian about batching modifications.
Well worth a read. Well worth modeling code after. I think, anyway. I mean, again, who am I to judge?
But yeah, those are the things that I would look for. I’ve noticed, perhaps anecdotally, that modification queries are less prone to register missing index requests. For reasons that I’m not quite sure of.
I’m not sure if it’s because a lot of modification queries end up doing eager spool work at some point, or if there’s something built in, built in that makes them less prone to getting missing index requests. I just feel like something terrible has to be going on for a SQL Server to be like, yeah, we need an index to help this, this modification. Because I don’t think, because missing index requests don’t care about, about locks.
Missing index requests aren’t there to help you resolve locking problems. They might just sort of, you know, by nature of offering a half decent index suggestion end up solving a locking problem. But the goal of a missing index request is not to resolve locking.
It is to resolve where clauses and key lookups. Because every missing index request I see is a haphazard spray of columns from the where clause in the keys. And then join and selected columns in the includes.
So, you know, perhaps not ideal for most people or workloads or figuring out why you’re hitting locks or blocks or deadlocks or any of that good stuff. A lot of crazy gutches with those missing index requests. Almost enough to make you wonder.
Makes a fellow wonder. Gets the noggin joggin at full sprint. What the code looks like in Azure or Azure, Azure, to generate the A-B testing of indexes. I’d be curious about that.
Justin says, funny enough, the name of my troubled index is missing index 208. So it sounds like someone used the database tuning advisor to do that, maybe. Unless 2087048 is a bug ticket number or a really terribly formatted and confusing date in the future.
Is the zeroth month of 2087 the 48th day or something? I don’t know. I don’t know.
I don’t know. I don’t know what will happen. It’s crazy out there. Crazy. Oh my God. We have. Okay, cool. No, we don’t have a question. We don’t have a question there.
Fun stuff. It says, when you’re not fixing SQL servers, what else do you get up to? Boy. Anyway, let’s see. I go to the gym.
And I participate in barbell training because it, it, it appeals to, it appeals to me because it’s not cardio. That’s, that’s, that’s up there. And, uh, I do that four or five days a week, kind of depending on my schedule.
Uh, and then aside from that, I’m mostly a, I don’t know. I would, I don’t want to say home body cause I’m not like just sitting home a lot. My family body though, drag my family places, make them eat things.
I like going to restaurants. Restaurants are nice. People make food for you and you eat it and then you leave and you don’t have to clean anything or anything like that. It’s a wonderful, wonderful idea for an exchange of currency.
Food for money. I like it. Prepared food for money too. Not like one of those garbage delivery services where they’re like, cook your own food. We’ll give you chicken. And you’re like, I don’t need that. I don’t know. That’s about it.
Occasionally go to a movie. I hate, I’ve hated every movie that I’ve seen recently though. Hated every movie that I’ve seen recently. Hated TV recently too. Game of Thrones last season was. Then Avengers was.
Like, like, like it was almost like, like Hollywood was like, all right, we have two big franchises ending. Let’s pit them against each other to see who can come up with the worst ending. And they both lost.
Dismal dismal. The only thing. The only thing. The only thing I’ve liked. He’s only is season 10 of Masterchef. It’s the best thing on television right now. Best thing on television. I don’t know.
Sabrina’s a bum out. Umbrella Academy was pretty good. I just want every show to be the X-Files again. So I ended up watching the X-Files. I’ve been rewatching Archer.
I’m telling you about things you didn’t ask about. I’m sorry. It’s been like, it’s been an hour now. I’m boring all of you. I’m going to go open my office door. So I have air conditioning back. Thanks for hanging out and doing stuff, asking, asking great questions. You’ve been a wonderful crowd.
And I will see you next week at the same time place. Probably. 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.