Overview
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 OS Will go and use the page file as secondary storage, and if there’s one thing we know about disks, or where a page file is they are slow.
Also, Virtual machines share Memory resources and the underlining Hypervisor could take memory from SQL for any reason.
So why not prevent Windows from taking what is already SQL's and lock that down.
Prerequisites
Run the following query to see what Memory Model is currently running
SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;
If your system list 'Conventional' then using the default configuration
if your system list 'Locked Pages' then it's using the Group Policy setting to ensure memory allocation.
To set a system to use locked pages is pretty straight forward and easy.
Run services.msc and find the SQL instance service
What we want to know here is what account the SQL service is using.
In this case 'NT Service\\MSSQL$SQLDEV2019_2'
Now run GPEDIT.MSC
Navigate to Computer Configuration -> Windows SEttings -> Security Settings -> Local Polices -> User Rights Assegnment
Select Locked Pages in Memory
Add the user account and click apply and ok
Now restart the SQL service
Run the above 'SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;'
Job done
Windows can no longer Steal memory from SQL.
This query can be used to see how much memory has been allocated
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Total Server%';
or
DBCC memorystatus