To talk about lock pages in memory, you have to talk about paging. You have to talk about disk and memory too. If there are too many requests, too many things going on in memory and there’s not enough RAM, the OSV go and use the page file as secondary storage, and if there’s one thing we know about disks, or where page file is, is they are slow.

Full Transcript:

Hello and welcome back to another exciting video with me, Erik from Brent Ozar Unlimited. You’re probably sick of hearing that by now. Today we’re going to talk about lock pages in memory, which is kind of a controversial, weird setting where you allow SQL Server to effectively get pages into memory and never let them go. So no matter what Windows does, no matter what other processors do, SQL just says, no that’s my memory, I’m going to hang on to that, you get your own memory.

To talk about lock pages in memory, you have to talk about paging. You have to talk about disk and memory too. If there are too many requests, too many things going on in memory and there’s not enough RAM, the OSV go and use the page file as secondary storage, and if there’s one thing we know about disks, or where page file is, is they are slow. They are much, much slower than memory. Most of the time, people didn’t want that happening, they didn’t want their SQL Server using the page file because then you’d have to – that wasn’t memory, now that’s on disk, and now I’m reading – it’s confusing for SQL, and it’s slow for you and it’s slow for users and no one wants to do that. No one wants to deal with that.

SQL Server wants to use as much memory as it possibly can. It will always – as soon as you start reading stuff into memory, SQL will say, I’ve got all this memory I’m just going to keep using more. You go over there and you go over there and I get this table here and sort over here and this hash join over here. It will just keep grabbing memory and using it, it’s a big pity.

Now, lock pages in memory is a Windows account based policy. It’s not something you really set inside SQL Server. This is something you go onto security policy and you give lock pages in memory privilege to your SQL. In older versions of SQL Server and on older operating systems, memory management wasn’t as great, wasn’t as kind as SQL Server. For instance, memory would be allocated to SQL Server and then the OS would want more and it would go and trim the working set, which is not friendly. Trim the working employment set, which means you’re laying off a bunch of people. And then it would start pushing the pages in RAM to the page file. That was slow. Users hated you when that started to happen. Performance would take an awful nosedive, wasn’t even a nosedive, it would just straight up crash.

You had to be especially careful in virtualized environments. If you’re setting lock pages in memory on a VM, you’re most likely trying to start around your VM admin not giving you resource lock for your VM [inaudible 0:02:49.1]. So that’s one kind of tricky way of getting around it, but I’d much rather see you work with your VM admin to get the right reservation in place. Memory management does and has gotten much better in newer versions of Windows and SQL Server. The SQL OS as far as I know, still constantly under improvement.

So should you enable lock pages in memory? Well, probably not. SQL really should be the only thing on the box, and you really should be giving SQL Server enough memory that you don’t have to worry about it paging out. If you start seeing error messages in your SQL Server’s error log that you are paging stuff to disk, I’d be much more concerned about the hardware that I’m on, because I know that you’re doing everything you can for SQL Server to be running by itself on a server, not getting messed with by anything.

It can’t – lock pages in memory can be a helpful setting if you are Instance Stacking, which means you are installing multiple instances on a single server. I don’t want to teach you how to do that. It’s a bad idea and I know it helps with licensing stuff because you can install a bajillion instances and not pay extra for them, but it’s really not something that you should be doing. If you’re doing that, you obviously don’t care enough about performance for the setting to matter anyway. Despicable human being.

Kind of new in SQL Server 2016 is the ability to see if you have any memory setting changed from the default to the DMVs. If you go to sys.DMOSsysinfo and you select from that, all the way at the end, there are a couple columns. There’s SQL memory model column and then there’s the SQL memory model description column. I prefer the description column because that’s where the human readable English words are that I can figure out. In there, there are three different possible states that it could be. There’s conventional, which is the default, which means they’re not logging pages in memory and you’re not using large pages. There’s the lock pages description, which is pages are locked in memory, and then there’s the large pages description, which means you have gone and you have enabled large pages and you’ve turned on trace flag 834 and you are now using large memory pages for some reason. Good luck.

Anyway, that’s about all there is to say about lock pages in memory. I hope you learned a little bit, I hope you enjoyed reading this, and I hope you’re on SQL Server 2016 so you can figure out that type of memory model from the DMVs. Thanks a lot.