SQL SERVER – 2008 – High Availability – Hot Add Memory

After reading my previous article about SQL SERVER – 2008 – High Availability – Hot Add CPU the same developer who suggested Hot Add CPU asked me if there are any restrictions in Hot Adding Memory. He wanted to know what Hot Add Memory needs from hardware and Windows.

Yes, there are few restictions to Hot Add Memory as well. I am listing them here.

1) Underlying hardware is always key concern. Hardware should be capable to add memory when previous memories are operational.

2) Operating system should be either Windows Server 2003 or 2008 Enterprise or Datacenter Edition.

3) This feature is only available in 64-bit SQL Server Enterprise Edition, or the 32-bit version with Address Windowing Extensions(AWE) enabled.

In summary, just like Hot Add CPU, Hot Add Memory have some restrictions but they are not as strict as Hot Add CPU.

What to Do After You Hot Add Memory

Adding the memory to the machine is only the first step. SQL Server uses only what its settings allow. If max server memory is set to a fixed value, the new memory sits unused until you raise it. Check the current value with sp_configure 'max server memory', turning on show advanced options first if needed, and change it with sp_configure and RECONFIGURE. The change takes effect without a restart.

Leave room for Windows and other processes when you pick the new value. Giving every megabyte to SQL Server can starve the operating system and slow everything down. Watch the available memory for a few days after the change, and adjust if Windows runs short.

To confirm what the server sees, run SELECT total_physical_memory_kb, available_physical_memory_kb FROM sys.dm_os_sys_memory on SQL Server 2008. The total should match the new amount.

On virtual machines, the host can often give a running guest more RAM in the same way. The same checks apply: the guest operating system and the SQL Server edition must support it, and SQL Server still needs its max server memory setting raised. Test the whole process on a non-production server first, so you know it works before you need it in a hurry.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.