SQL Server – Best Use of RAM for Multiple Instances

sql serverwindows-server

I have a new Windows Server 2016 box with 32GB of RAM, it will be used for dev testing by one or two users max. There will be occasional use of VM/Hyper-V instances and a couple of IIS test sites running from time to time.

I also need to install all versions of SQL Server from 2008 through to 2016. These will each have many databases attached but very few will actually be active at a time unless testing calls for it.

Assuming there is no practical issue with installing several different SQL Server installations on the same box, what options do I have to configure the best use of RAM? I know that on a standard installation, SQL will try to hog all available RAM but I want to try to avoid this leaving around 12GB for other services and allocate a maximum of 24GB for all the SQL instances to share equally without fighting with each other over resources.

Is there a good way to achieve this?

PS I'm not an experienced DBA just a humble developer

Best Answer

You can allocate maximum amounts of memory that each SQL Server instance can use through the sp_configure stored procedure. However, that won't be an absolute maximum, but a maximum amount of memory allocated to the storage of certain types of data, mainly data cached from the database.

If you're planning on running one instance of each version of SQL Server since 2008, you're looking at five instances of SQL Server on a 32MB server; you'll need to leave a few GB RAM available for the operating system and anything else on that server (SSIS? SSRS? SSAS?). At a conservative estimate, you'll probably only have about 4GB RAM available for each instance, so you would run something like:

exec sp_configure 'show advanced options', 1;  -- ensure memory configuration option is available 
GO  
RECONFIGURE;  
GO  
exec sp_configure 'max server memory', 4096; -- maximum memory in MB 
GO  
RECONFIGURE;  
GO

Note that difference versions of SQL Server interpret the configuration setting in different ways - more recent versions apply the setting to more types of memory allocation. There's a table about half way down this sizing consideration document that shows what's changed.

Related Question