Multiple instances of SQL Server – memory consideration

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

If you plan to run multiple instances of SQL server on the same server, you should plan on how you are going to share the memory between the instances. When you install a SQL server, you get some default settings and some of them are regarding memory. By defualt the “min memory server” setting is 0 and the “max memory setting” is unlimited. This is not a prefered setting in a single instance installation and it is even worse for multiple instance installations.

In a single instance installation, SQL server and Windows server will compete of the memory resources as SQL server wants to allocate all memory.

In a multiple instance installations, you’ll get multiple instances that is competing of the memory resources and all of the SQL instances wants to allocate all memory (based on the workload of cource).

If you have more memory than the workload of every SQL server instance, you probably don’t see any problems. If you don’t have enough memory, 1 or more instances have to release the memory in favour of another instance. This could be acceptable if the performance is not important in any of the SQL server instances.

When a SQL server instance have to release the memory, a message is written to the SQL server log:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: x seconds. Working set (KB): y, committed (KB): z, memory utilization: %.

If you see alot of this messages, your SQL server performance is suffering from memory deallocations.

To solve this, you should consider to change the max server memory setting of all the SQL server instances. It can be changed in the Server properties dialog or by using T-SQL, but has to be done on every SQL server instance.

 

Server properties 

 

T-SQL

Open a new query window and write the following code to change your max server memory to a desired value and execute the code

sp_configure 'max server memory (MB)', '13000' -- change the value to your desired memory
RECONFIGURE
 

Håkan Winther
COO / SQL Server consultant
Twitter: @h_winther