Hot Add Memory
One of the smaller improvements made to SQL Server 2005 was the ability of SQL Server to utilize memory added while SQL Server was online and running. SQL Server before 2005 supported dynamic memory but if you needed to add memory to a server after you had started SQL Server, you couldn’t since SQL Server only recognized the memory that was present during start up. SQL Server 2005 added support for Hot Add Memory. While this is a great concept, I probably will have a hard time finding more than 2 people in the SQL Server world how have actually utilize this feature. I bet I could a few dozen who needed this feature but didn’t know about it or who knew about the feature but didn’t have their server configure to utilize Hot Add Memory. I hope this little blog helps a few database administrators understand this concept and how it may help them in the future.
So what is Hot Add Memory?
What Hot Add Memory basically means that a Windows administrator can crack open the case of a server, add new memory, and then close the server up and Windows and SQL Server will recognize that additional memory and utilize it without having to be bounced. This allows for the addition of extra memory at a later date without having to schedule server or SQL Server downtime. The ability of having additional memory added without downtime may actually serve a few of us with our uptime requirements or even allow us to put a server into place before we actually have that additional memory we figured out at a later date that we really needed for new environment.
So how do you configure your server to utilize Hot Add Memory?
As stated above, Hot Add Memory requires either Windows 2003 or later Enterprise or Data Center Edition. Hot Add Memory is available for both 64-bit and 32-bit SQL server. Hot Add Memory is a server feature that some of the server have, older servers may not the ability to add hot memory since the server itself must have the configuration and feature set added to the server, and requires either Windows 2003 (or 2008) Enterprise or Data Center edition.
There are a few SQL Server related configurations that must be in place before SQL Server can actually make use of Hot Add Memory. If you are on a 32-bit version, you must have AWE enabled, even if you do not have more than 4GB of memory. You cannot make use of Hot Add Memory without AWE. Another item which will keep most database administrators from using Hot Add Memory is that you must have the –h startup option in place before using Hot Add Memory on a 32-bit version. This startup parameter reserves additional virtual address space for the Hot Add Memory metadata when using 32-bit AWE and since it is a startup parameter, you must have set –h before starting SQL Server. While you could just set this parameter on all your servers and be done with it, the parameter comes with a price. The –h startup parameter consumes 500 MB of virtual address space. Remember VAS is limited to the first 2GB of memory (3GB of memory if you are using the /3GB switch) on a 32-bit installation. This means that 500 MB of the 2GB is being used for Hot Add Memory metadata. Just having this parameter set may cause the need to add additional memory. Be careful when setting this parameter and only do it for servers you know will require additional memory but the memory is still on order and you just need to put what you have in place before it comes in. I would also remove the parameter as soon as I added the new memory so that on the new reboot the 500MB of memory can be recaptured for SQL Server to utilize.
One good thing, if your server (not SQL Server) is configured to use Hot Add Memory, you no longer have to use the /PAE switch in the boot.ini. No, the 500MB will not be automatically consumed as the –h startup parameter is a SQL Server parameter, not a Windows setting.
Note: If for some reason you remove memory from a server - I can’t think of a single time I would allow this to happen to any of my servers - you still have to restart everything. There is no Hot Remove Memory feature.
Summary
This was just a small posting about a feature of SQL Server 2005 and 2008 that most of us do not know about. I know it will not be a heavily used feature, but when you need it, it will be great to know about Hot Add Memory.
