SQL Server Enterprise Edition can leverage large memory pages to reduce the amount of memory pointers required for larger SQL Server deployments. Reducing the number of pointers makes the database engine more efficient, especially for SQL Servers with greater than 32GB of RAM. A normal memory block is 4KB, and many thousands of pointers are required to manage the memory underneath a larger SQL Server. Large memory pages can change the block size to 2MB, greatly reducing the number of pointers required for memory management.
Leverage SQL Server trace flag 834 to enable this setting and restart the instance to validate.
Note: Large memory pages requires the use of lock pages in memory for the service account before large memory pages are enabled.
For maximum performance of SQL Server in a virtual machine, the use of large memory pages is highly recommended, as it reduces the number of translation lookaside buffer (TLB) misses, which will improve the performance of the SQL Server. Large memory pages are fully supported within all modern hypervisors.
Note: Do not use large memory pages if columnstore indexing is in use in any SQL Server database on this instance.
Note: Be careful if the VM is also under memory pressure, as if the operating system is squeezed too hard, SQL Server will not return memory to the OS and system instability can occur.
To enable large memory pages, add trace flag 834 to a startup parameter for the SQL Server service through the SQL Server Configuration Manager. Restart the instance once this trace flag is added.
To validate that large memory pages are enabled, execute the following query.
You can check the error log for entries related to large memory pages on instance startup.
If the results show large memory page allocations greater than zero, large memory pages are enabled.
Now, validate your system performance before and after! Let me know the results!