Database Reference
In-Depth Information
capabilities. Our experience has taught us to limit this practice to your most mission-
critical SQL Server databases and limit Max Server Memory to the confines of the
overall size of the virtual machine in question. We recommend that you should always
lock pages in memory.
Tip
For your mission-critical SQL Server databases, we recommend you lock pages
in memory to prevent the SQL Server buffer pool being paged out by the
Windows operating system. Make sure you have a reservation for the amount of
memory at the hypervisor layer.
The opposite is also true for your noncritical workloads; we recommend that you do
not
lock pages in memory. This will then enable the balloon driver to do its job and reclaim
memory for use by the hypervisor for other virtual machines on the host. This is
important especially when you are trying to consolidate a number of workloads onto a
single physical host. The assumption here is that they won't always need all of the
assigned resources at the same time. Never forget a virtualized infrastructure is a shared
infrastructure.
You want to avoid the yo-yo effect, where the reclamation process (Balloon Driver) is
recovery memory, then the resource (VM) that provided the excess memory is now in
need of it, so the reclamation process gives it back to the VM, then the balloon driver
recovers the memory again and so on and so on and so on. Every time the system
thrashes as resources ebb and flow, other resources are impacted, such as CPU and
disk. For example, as paging and swapping occur, the storage array is impacted.
How to Lock Pages in Memory
In order to lock pages in memory, it is important that the appropriate account has rights.
There is a Windows policy that determines which accounts are able to lock pages and
which accounts cannot. The account in your environment that has privileges to run
sqlservr.exe is the account you want to give the ability to lock pages in memory. For
SQL Server databases before 2012, you should also make sure this account has the
awe_enabled configuration set to on. AWE stands for “address windowing extensions.”
This allows a 32-bit SQL Server to address more memory. Note that awe_enabled is
not needed on the 64-bit versions of SQL Server.
Non-Uniform Memory Access (NUMA)
When you're building your virtual machines, we recommend that you size each one to fit
within a single NUMA node so that you get optimal performance for your virtual
machines.
