The Lock Pages in Memory policy came up in our internal discussions. I wanted to chat about it a bit more and I couldn’t think of a better person than Rick Lowe. After our recent SQL Trail, we sit down to discuss this policy option.
Great SQL Server Debates: Lock Pages in Memory by Jonathan Kehayias
“Virtualization is its own ball of wax, and you need to know more about what’s going on in the system and that lock pages in memory won’t save you if you’re having issues there.”
“The downside is that ultimately you could commit all the memory in SQL Server and you could lock up the machine, or in the case of a virtual environment, multiple machines.”
“It would be really interesting to see some new blog posts on this stuff, from the people who are just elbows-deep in the internals, because there have also been a couple of Windows releases, just a couple, since then.”
Listen to Learn
00:38 Intro to the guest & topic
03:22 Explanation of lock pages in memory
05:50 Should we use lock pages in memory as a default?
08:11 Physical versus virtual memory
11:14 The things that would determine whether we want to turn it on
14:54 Measure twice, cut once
18:00 Closing Thoughts
Carlos: Compañeros! Welcome to another edition of the SQL Data Partners Podcast. My name is Carlos L Chacon, your host, and I am joined today by Rick Lowe. Hello, Rick.
Rick: Hi, there.
Carlos: So compañeros, maybe not quite as real-time as we would have liked. We’re coming to you by way of SQL Trail. Originally, we wanted to do a live podcast, and then turns out that that really doesn’t exist. But you can do live streaming and more video and whatnot, and SQL Trail changed a little bit, which I’ll talk about in a future episode, and then we thought, “oh, well, let’s just sit down and we’ll talk for a little bit,” and then of course, the conference happens and finding a quiet spot to do that is challenging. Anyway, so we come to you post-SQL Trail and Rick was there. We were lucky enough to have Rick there, and one of the things that, it didn’t necessarily come up, but I wanted to talk with you about Lock Pages in Memory. So, as a Windows setting, we’re talking about SQL Server, and ultimately using this setting in conjunction with SQL Server. Now, the genesis for this question is I have a consultant who– so, in the past, I have not recommended this as a default, and I guess I shouldn’t say in the past. So for the last, I’ll just say 18 months, and we’ll get into more of this. But I have a consultant, one of my guys, and we started doing some assessments, and he’s like, “oh, lock pages in memory, you’ve got to do it.” So we started having this internal debate, if you will, and I thought, “hm, who do I want to run this by?” and I couldn’t think of a better person than Rick Lowe. And so I was like, “hey Rick, let’s talk about Lock Pages in Memory. So that’s what we want to talk about. So, this is Episode 177 and we’re kind of doing a, I don’t know, an impromptu session, if you will. So, some of the other pieces, there’s many compañero shout-outs that I need to give, and we will circle back to those in later episodes, and so thank you for your patience, compañeros. Again, Lock Pages in Memory, I gave you a little bit of the genesis, and I guess I want to start, I’m going to point out– first, let’s define what it is that we’re talking about. So, when we talk about lock pages in memory, this is a Windows security setting policy that determines which accounts can use processes to keep data in physical memory so they won’t page the data to disc, if it comes to that. So, I almost feel like we need a little architecture discussion here, first. So, in SQL Server, obviously one of the very common settings to set is the max memory setting. Okay, so that’s number one. So, the thought is that everything in SQL Server gets read from memory, so when I do SELECT*, it goes to the disc, it’s going to bring that up and put that buffer, or that data into the buffer cache, which goes into physical memory. The operating system then gets to determine how it’s going to manage that memory and Rick, of course, correct me if I go astray, here. It’s going to manage that memory, and sometimes, or many time, it’s going to need to remove some of that buffer information to make room for more. Something in the SO could trigger, virus scans, you name it, could happen, and it’s going to take care of that memory segment. So, what could happen is a couple of different things, one is that it could keep it “in memory” and it could put it out to disc. So you think about paging files and things like that, so this is an example of what it could do. It could simply just remove it and say, “you’re out. I’ve got to remove you from memory, so I can make room for the next things.” And there may be even a couple of options. But it is that question of whether that process has the ability to say, “hey, I don’t want you to page to disc” or not, is ultimately what we’re talking about with lock pages in memory.
Carlos: You on the same page with me, Rick?
Rick: Yep, yep, definitely.
Carlos: So, one of the things that is also worth pointing out is that there’s a little bit of a– and admittedly, a lot of the blog posts that I’m seeing are a bit dated, at this point. So, it’s 2019 that we’re talking about this, and a lot of the posts are from the SQL Server 2005, 2008 time-frame, and SQL Server’s changed. Not only SQL Server, Windows has changed. Everything has changed since then, and that, then, makes a difference. So, the question is, should I enable lock pages in memory as a default? So, let’s start there and we can talk about all the different nuances. So, what do you say, Rick?
Rick: Probably predictably, because I tend to kind of middle-of-the-road on a lot of these issues. I’m somewhat agnostic on the lock pages in memory setting. I don’t use it, myself, mostly because with workloads that I encounter, it’s not something I run into that I’ve needed to turn on very often, if things like ‘max memory’ are set appropriately. That said, of course, if you have ‘max memory’ set low enough to leave room for the OS, you’re not going to have paging issues. I mean, really what the setting is about is how the system responds if it’s not set correctly, what your failure modes look like. Which is why I don’t necessarily argue against setting it too strongly, either. My understanding is that there were some architectural changes made to Windows quite a while ago, I think it might have been about 2008, but which made the setting less critical, because Windows is maybe less over the top about reclaiming memory than it used to be. But I’ve also heard reports that there are still cases for altering memory inappropriately. Some people much smarter than I do insist that they should still be set by default.
Carlos: Right, so one of the great articles that’s on the Redgate blog, so Jonathan Kehayias, which again, 2011, so we’re a bit dated, here. But a couple of– so we’re getting into nuances here. So one that you’ve already mentioned is, is the memory setting set? Another one is physical versus virtual, so in 2005, yeah, I mean, I think virtualization was kind of there, but it’s definitely much more prevalent that it was. I mean, even the small shops now have virtualization, which it tended to be, you had to have, I don’t know, I’m not sure what the threshold was, but you had to have a fairly large server to then be able to segment into smaller VMs, and I feel like that’s another consideration. Generally, the genesis for lock pages in memory tended to be for physical servers running 32-bit architectures as well, that didn’t have quite as much memory. Those are the use cases for lock pages in memory. And then, of course, lots of things have changed since there, 64-bit architecture, more memory, things like that. And so, it seems like virtualization is its own ball of wax, and that setting, you need to know more about what’s going on in the system and that lock pages in memory won’t save you if you’re having issues there.
Rick: Yeah, and I think in the virtual world, this goes back to not so much whether or not the setting’s important, if all of your other settings are set correctly, but more what happens when you mess up. You know, I believe in the virtual world, the nightmare scenario for the people who say you shouldn’t set this as a default, especially in virtual environments; I think what they’re concerned about is if memory is overcommitted on the host and your page is locked in memory on 4 or 5, well hopefully not 6 or 7, but a handful of SQL Server instances on the same hypervisor. You’re not just going to gork your own instance, you’re going to gork your neighbor’s, also.
Carlos: Yeah, the memory is now hostage, in a sense.
Rick: Exactly. Yeah, so on one hand, the answer to that is well, don’t overcommit memory.
Carlos: Easier said than done.
Rick: Yeah, cause you can always make a mistake and accidentally overcommit.
Carlos: And there’s plenty of environments, still, where the data folks and the virtualization folks are not the same people, so you don’t know what your virtualization environment looks like, necessarily, from a host perspective, and that is the challenge, or the rub. So then, I guess, that brings the question as to well, okay, so if we’re using the ‘max server memory’ for SQL Server, and we’re not over-allocating, what would, in essence, be the tell-tale signs that this might be a good idea? I think one of the other challenges that I have with something like, “it should always be” or defaults and things is, I guess, the why. Why would we do this? So, originally, we talked about the way memory was used in the operating systems, and yes, I think we just discussed that things have changed, that yes, there may still be instances that it’s needed, but kind of that, “oh yeah, let’s just always do that by default” is what gives me pause. But then, what kinds of things would we be looking for to determine whether this might be something we want to turn on?
Rick: Well, yeah, of course the most direct thing to look at would be excessive paging activity. The problem with saying that is, I know we always say that there shouldn’t be anything but SQL Server on a database server, but you usually have something else running in the background, even if it’s not a full-on application or some kind of (unintelligible) standard agent that’s likely there. So I guess really, what we’d probably look for foremost, would be sudden drops in the size of the buffer pool, or something like that.
Carlos: Sure, that aren’t correlated with maintenance plans, right? Because normally, in the evenings, what we generally like to see, if you’re tracking that on a graph is the sawtooth type. So it goes up and then plateaus and goes up and plateaus and then a lot of times, particularly if you’re running it every night, is that you’ll see the sharp drop, because, I don’t know, you’ll do a DBCC, CheckDB or something like that, right? So then, of course, obviously your memory all gets out of whack, and you’ve got to start from the beginning. So I think what you’re saying is if you see the drop, and it’s not correlated to one of those maintenance activities, that could be an indicator. Or your paging file grows a lot in your operating system.
Rick: Yeah, and I guess, actually, another decent indicator would be if you monitor available system memory or available MB performance counter or something like that, and notice it seems like it’s getting lower than you would expect. That could be a sign you’re a little bit more on the edge than you thought you might be.
Carlos: Yeah. So would you use that on virtual environments? I guess you’ve already indicated that generally tend to shy away from it, but I almost feel like, “measure twice, cut once.”
Rick: I think some people find me a little maddening with these questions, because I tend to be out of step, in the sense that I don’t necessarily look at those just as a technical question. So, I think the reason I personally don’t use lock pages in memory very often is for the same reason that when I’m left to my own devices, and if I’m working in the lead on a project as opposed to sub-contractor, I tend not to use a lot of trace flags. It’s not that the behavior is bad, it’s nothing saying the defaults are great and that there aren’t behaviors I wish I could change, I’m always worried that if I come in and help someone with something and turn on a bunch of settings that are a little bit obscure or not well documented, or even undocumented in some cases, I might be hearing the response of, “oh wow, that was neat. In fact, that was so neat, I think I might like to do it, also. Let’s see what other trace flags I can find on the internet to start turning on.” So, I tend to worry about the soft-skills point of view. So, if the question is, am I likely to start wanting to set this by default personally, well, not especially, because I worry about that kind of stuff. But if the question is, do I think it’s necessarily bad in most scenarios for lock pages in memory to be on by default, I’d also say that, no it doesn’t sound like it would be bad, necessarily, for it to be the default, because again, if you’re setting all of the other settings appropriately, it actually shouldn’t really matter if it’s on or not, in terms of performance.
Carlos: Sure. Yeah, okay, that’s interesting. And it’s almost, yes, it is a setting that you, because you have access to the server and the security settings can change, but I almost wonder if there are better settings to change. We already talked about the max server setting, or maybe it just needs more memory.
Rick: Yeah, yeah.
Carlos: Which again, potentially easier said than done, but there’s less risk. The downside is that ultimately you could commit all the memory in SQL Server and you could lock up the machine, or in the case of a virtual environment, multiple machines. That is the downside. So, as long as you’re aware of that, I guess you could take that into consideration in your environment and use it as you wish. Does that seem fair?
Rick: Yeah, yeah.
Carlos: Okay, well, I think that that is, again, always interesting. Thank you, Rick, for talking me through that. I do appreciate it. So compañeros, I’m going to put Jonathan Kehayias’s blog post up on the show notes for today’s episode, just because it is so detailed. He goes into a lot of interesting things, talks a little bit about some of the things that we’ve considered, and goes into a little bit more depth in terms of memory management than we did. So if you want to read that, that’s something interesting to look at.
Rick: Yeah, I would actually say, when in doubt, listen to Jonathan, not me.
Carlos: Yeah, so that’s interesting, because I think that it’s one of those things, well, just to recap there, his thought is you should enable it. However, it assumes that you’ve already looked at these other pieces that we’ve already looked at. And it doesn’t appear to me that is is warranted as much in the virtual environment. He definitely recommends it in physical, the virtual is a little more nuanced, in my mind. At least that’s the way that I read it.
Rick: Yeah, it’d actually be really interesting to see some new blog posts on this stuff, from the people who are just elbows-deep in the internals, because there have also been a couple of Windows releases, just a couple, since then.
Carlos: Just a couple, that’s right. Yeah, yeah.
Rick: Which may have changed things, because at the end of the day, the lock pages in memory is about overcoming potentially dysfunctional behavior in Windows.
Carlos: Exactly. And, between now and then, we also have SQL Server running on a different OS, as well, so that is another kind of wrinkle into all of this. So compañeros, I think that’s going to do it for today’s episode. If you have questions or comments, of course we’d be very interested in getting back with us. Let us know what you’re thinking. We’re very interested in having your comments. We appreciate those who get up a chat on LinkedIn. That’s the way I communicate with a lot of folks. And so, Rick, if people want to get in touch with you on social media, how would they do it?
Rick: Best bet is probably Twitter, @dataflowe is my handle.
Carlos: There we go. And compañeros, I’m on LinkedIn at Carlos L Chacon, and we’ll see you on the SQL Trail.