Windows lock pages in memory — Ваш верный помощник с OS Windows

What is lock pages in memory

This Windows policy specifies which accounts can keep data in physical memory, preventing the system from paging it to virtual memory on disk.

RAM (Random Access Memory) and virtual storage serve as two types of memory in a computer system, each with distinct functions and characteristics.

RAM, the physical memory installed in a computer, provides fast access to actively used data by the CPU, determining the system’s multitasking capabilities. In contrast, virtual storage, often referred to as virtual memory, utilizes part of the hard drive or SSD to extend the effective memory capacity of the system when physical RAM is insufficient.

If an application requires more memory, it can make a request, however if the system memory is at capacity, Windows might move some data from RAM to disk (paging) in order to free up space.

The Windows policy setting lock pages in memory (LPIM) determines which accounts can keep data in physical memory, preventing the system from moving it to virtual memory on disk.

Why lock pages in memory

Locking pages in memory can improve performance when frequent paging to disk is expected. The setting lock pages in memory is regularly used by specific applications or services which require constant access or data without interruption. By keeping these pages in memory, the system ensures that this information is readily available at a moment’s notice. This is mostly used for applications such as: video editing, complex calculations, or certain scientific simulations

Ad Hoc Distributed Queries – SQL Server

Microsoft SQL servers and lock pages in memory

One of the specific programs which necessitates locking pages in memory is Microsoft SQL Server instances running on Windows operating systems. By enabling this setting, SQL Server can prevent its critical memory structures and data from being swapped out to disk, ensuring fast and reliable access to memory resources.

This helps minimize latency and improves overall performance by maintaining a consistent memory access pattern and reducing disk I/O overhead. In essence, Lock pages in memory enhances SQL Server’s ability to efficiently utilize memory, thereby supporting its performance and scalability requirements.

With varying memory models across different versions of SQL Server, managing SQL Server memory efficiently becomes paramount. It is essential to note that changing this setting requires restarting the SQL Server instance to take effect, ensuring seamless operation and optimal memory allocation for SQL Server workloads.

Why not to lock pages in memory

Users granted the “Lock pages in memory” user right have the ability to allocate physical memory to multiple processes. However, this allocation may monopolize RAM resources, leaving minimal or no memory available for other processes. Consequently, this scenario can lead to a Denial of Service (DoS) condition, impairing the functionality of the system.

How to enable lock pages in memory

To check the the lock pages in memory setting or to change to the recommended setting via GP, set the following UI path to No One:

Computer Configuration\Policies\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Lock pages in memory

Or follow this procedure: To enable the option

On the Start menu, select Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.
On the Local Group Group Policy console, expand Computer Configuration.
Expand Windows Settings.
Expand Security Settings.
Expand Local Policies.
Select the User Rights Assignment folder. The policies will be displayed in the details pane.
In the pane, scroll to and double-click the Lock pages in memory policy.
In the Local Security Policy Setting dialog box, select **Add User or Group…*. Add the SQL Server Service account. To determine the service account for the instance of SQL Server, refer to the SQL Server Configuration Manager or query the service_account from sys.dm_server_services. For more information, see sys.dm_server_services (Transact-SQL).
Select OK.
Restart the instance for this setting to take effect.

Possible values

User-defined list of accounts
Not defined
No One

Default value

The default value for this setting is: No One.

Recommended state

The recommended state for this setting is: No One.

Best practices and hardening

Unless you’re using a program that specifically mentions needing “Lock pages in memory,” it’s best to leave this setting alone. The automatic memory management in Windows is usually sufficient for most users.

Using server hardening can give peace of mind that the hundreds of Windows Security settings are configured correctly. With this comes the freedom of time for other crucial activities necessary to keep a business running smoothly.

Источник

To enable the lock pages in memory option:

Click Start > Run and type gpedit. …
In the left pane, navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies.
Expand Local Policies and select User Rights Assignment.

Should I enable lock pages in memory?
How do I lock a Windows page?
What is LPIM?
What is a role and responsibility of virtual memory?
Does SQL Server release memory?
How do I lock a window in Windows 10?
How do I pin a window in Windows 10?
Does increasing virtual memory improve performance?
What is the problem with using virtual memory?
Is virtual RAM effective?
How much RAM should I allocate to SQL Server?
How do I enable default Windows lock screen?

Should I enable lock pages in memory?

This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may boost performance when paging memory to disk is expected.

How do I lock a Windows page?

Option 2 – Windows Key + L

Hold down Windows Key and press “L” for a quicker way of locking your screen.

What is LPIM?

Lock Pages in Memory (LPIM) is a Windows policy that prevents the system from not performing data paging to virtual memory on disk during memory pressure. LPIM locks your data in physical memory to improve the system performance and can be very helpful for SQL Server.

What is a role and responsibility of virtual memory?

Virtual memory provides virtual address mapping between applications and hardware memory. It provides many functions, including multitasking (multiple tasks executing at once on one CPU), allowing multiple processes to access the same shared library in memory, swapping, and other functions.

Does SQL Server release memory?

When you restart the SQL instance service, that memory gets freed up and given back to the OS as SQL Server is no longer using it. Then, as queries run, the memory used by SQL Server will climb again until it hits the max memory limit, or the OS starts pushing back on memory requests.

How do I lock a window in Windows 10?

Press and hold the Windows logo key on your keyboard (this key should appear next to the Alt key), and then press the L key. Your computer will be locked, and the Windows 10 login screen will be displayed.

How do I pin a window in Windows 10?

In order to pin a window, right-click on the icon in your tray again and enter Pin Mode. Your cursor will change to a pin – click on the title bar of the window you want to always keep on top, and a pin will appear on that bar. It’ll be the color you set in the options menu earlier.

Does increasing virtual memory improve performance?

Virtual memory is important for improving system performance, multitasking and using large programs. However, users should not overly rely on virtual memory, since it is considerably slower than RAM.

What is the problem with using virtual memory?

Using virtual memory makes a computer run slower, as the processor has to wait while data is swapped between hard disk and RAM. As secondary storage devices have slower access times than RAM, the computer’s processing performance can be severely impaired.

Is virtual RAM effective?

Is Virtual RAM good for gaming? Virtual RAM expansion on Android will not boost up your gaming performance directly. Yes, it will help you to keep a few of your applications running in the background, as the extra bit of RAM will take over some of the duties of regular RAM.

How much RAM should I allocate to SQL Server?

To prevent Microsoft SQL Server from consuming too much memory, you can use the following formula to determine the recommended maximum server memory: Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications.

How do I enable default Windows lock screen?

Computer Configuration > Administrative Templates > Control Panel > Personalization. In the right-side pane, double-click on Do not display the lock screen to open its settings box. Select Enabled and click on Apply/OK. That’s it!

Источник

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.

Источник

As we know, this Windows policy Lock Pages in Memory option determines which accounts can use a process to keep data in physical memory, preventing the Windows operating system from paging out a significant amount of data from physical memory to virtual memory on disk. This Windows policy is disabled by default. This Windows policy (Lock Pages in Memory option) must be enabled for SQL Server service account. That’s because, setting this option can increase the performance of SQL Server instance running on the virtual machine (VM) where paging memory to disk is expected. When not enabled, there is a risk that SQL Server buffer pool pages may be paged out from physical memory to virtual memory on disk.
*Note: Only applies to SQL Server instances running on the virtual machine (VM).

You can use the following Transact-SQL script to check whether or not this Windows policy is enabled for SQL Server Service Startup account:

SET NOCOUNT ON;
DECLARE @CMDShellFlag [bit] ,
@CheckCommand [nvarchar](256);

DECLARE @xp_cmdshell_output TABLE
    (
      [output] [varchar](8000)
    );
IF NOT EXISTS ( SELECT  *
                FROM    [sys].[configurations]
                WHERE   [name] = N'xp_cmdshell'
                        AND [value_in_use] = 1 )
    BEGIN

        SET @CMDShellFlag = 1;
        EXEC [sp_configure] 'show advanced options', 1;
        RECONFIGURE;
        EXEC [sp_configure] 'xp_cmdshell', 1;
        RECONFIGURE;
        EXEC [sp_configure] 'show advanced options', 0;
        RECONFIGURE;
    END
SELECT  @CheckCommand = 'EXEC [master]..[xp_cmdshell]' + SPACE(1) + QUOTENAME('whoami /priv', '''');
INSERT INTO @xp_cmdshell_output
        ( [output] )
EXEC [sys].[sp_executesql] @CheckCommand;
IF EXISTS ( SELECT  *
            FROM    @xp_cmdshell_output
            WHERE   [output] LIKE '%SeLockMemoryPrivilege%enabled%' )
    SELECT  'Windows policy Lock Pages in Memory option is enabled' AS [Finding];
ELSE
    SELECT  'Windows policy Lock Pages in Memory option is disabled' AS [Finding]; 
IF @CMDShellFlag = 1
    BEGIN
        EXEC [sp_configure] 'show advanced options', 1;
        RECONFIGURE;
        EXEC [sp_configure] 'xp_cmdshell', 0;
        RECONFIGURE;
        EXEC [sp_configure] 'show advanced options', 0;
        RECONFIGURE;
    END
SET NOCOUNT OFF;

Here are instructions to enable Lock Pages in Memory option Windows policy:

On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder. The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, add SQL Server Service Startup account.
Restart SQL Server for this change to take effect.

For more information, refer to article “Enable the Lock Pages in Memory Option (Windows)” on the Microsoft Development Network website.

Very Important Note: You must also configure the following two server memory options, min server memory and max server memory, if you enable Windows policy Lock Pages in Memory option. That’s because these memory options help you to control the amount of server memory with SQL Server can consume. Ignoring this recommendation can severely reduce SQL Server performance and even prevent SQL Server from starting. For more information about how to configure these two memory options, refer to article “Server Memory Server Configuration Options” on the Microsoft Development Network website.

I hope you will find this post useful.

Have a nice week….

Basit

Источник

Право блокировки страниц в памяти (Lock pages in memory)

Для учётной записи, от имени которой будет выполняться служба экземпляра SQL Server, рекомендуется установить разрешение, дающее возможность блокировки страниц в памяти (Lock pages in memory). Это позволит сократить количество обращений к виртуальной памяти на диске в случае нехватки ресурсов, отдавая приоритет работе в закрепленных страницах оперативной памяти.

В консоли Local Security Policy (secpol.msc) перейдём в раздел Local Policies > User Rights Assignment, найдём и настроим политику Lock pages in memory

В нашем примере право Lock pages in memory выдано учётной записи gMSA KOM\s-MS01$, от имени которой будет выполниться основная служба экземпляра SQL Server Database Engine.

Внимание!

Если сервер 1С:Предприятие 8.3 планируется использовать на одной системе с SQL Server, то от данной настройки лучше воздержаться, чтобы SQL Server не отнимал чрезмерное количество ресурсов у системы и не мешал работе сервера 1С:Предприятие.

Дополнительные источники информации:

Проверено на следующих конфигурациях:

Версия ОС	Версия SQL Server
Microsoft Windows Server 2012 R2 Standard EN (6.3.9600)	Microsoft SQL Server 2016 SP2 CU4 (13.0.5233.0)

Автор первичной редакции:
Алексей Максимов
Время публикации: 12.02.2019 11:01

Источник