While I am a big fan of maximizing memory its important to consider your memory configuration!
You add RAM in a physical server and expect it to work as you want.
Anything that leverages lots of RAM to function, including a database server, can take a substantial performance hit on performance.
Depending on the DIMM configuration, you might slow down your memory speed, which will slow down your application servers.
This speed decrease is virtually undetectable from the OS.
An example : To configure 384GB of RAM on a new server.
The server has 24 memory slots.
• You could populate each of the memory slots with 16GB sticks of memory to get to the 384GB total.
• Or, you could spend a bit more money to buy 32GB sticks of memory and only fill up half of the memory slots.
• Your outcome is the same amount of RAM.
• Your price tag on the memory is slightly higher than the relatively cheaper smaller sticks.
In this configuration, a 16GB DIMM configuration runs the memory 22% slower than if you buy the higher density sticks.
Check out page 63 of the server build guide for an HPE Proliant DL380 Gen9 server. https://www.hpe.com/h20195/v2/getpdf.aspx/c04346247.pdf
The fully populated 16GB stick configuration runs the memory at 1866 MHz.
When you only fill in the 32GB sticks on half the number of slots, then the memory runs at 2400 MHz.
SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory is allocated to SQL Server. However, the max server memory option can be useful in some environments. Make sure that sufficient memory is available for the operation of Windows Server. . For example, make sure that you run a dedicated instance of SQL Server on a server that has at least 4 gigabytes (GB) of memory. If the available memory for the server drops below 500 megabytes (MB) for extended periods, then the performance of the server may degrade.
Use the ‘Memory: Available Mbytes performance counter’ for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods. If the available memory drops below 500 MB frequently or for extended periods, then we recommend that you reduce the max server memory setting for SQL Server or increase the physical memory of the server.
