October 18, 2011 at 11:36 pm
If I have two instances of sql server with ram 4 gb on 32 bit Server then How much ram will be allocated to each instance and how memory will be distributed in this case??
Thanks
October 19, 2011 at 12:18 am
With the default memory settings each SQL Server instance will grab memory as needed. It will cache whatever data it can and if there is memory pressure each instance will release memory based on which data is least used.
If you want to control how much memory each instance has you will need to configure the memory in the server settings
October 19, 2011 at 12:27 am
As this is a 32 bit env, 2 GB will be vailable for the application. hence the instances which started first will take most of memory then the 2nd.
As said above you can use MAX Memory setting configuration for memory distributaion.
"More Green More Oxygen !! Plant a tree today"
October 19, 2011 at 12:35 am
If I will feel the memory pressure and enable the /3gb switch for both instances.What will happen?
Thanks
October 19, 2011 at 12:47 am
The /3gb switch is enabled for the OS only - it allows applications to use 3GB whilst the OS uses only 1GB.
Without specifying memory settings the two instances will share 3GB ram. As mentioned above, since it's a 32bit install you'll hit the 2GB limitation.
If you want to control the memory distribution you will need to specify the memory settings in the SQL Server settings.
October 19, 2011 at 3:41 am
If I have two instances of sql server with ram 4 gb on 32 bit Server then How much ram will be allocated to each instance and how memory will be distributed in this case??
Running Multiple Instances of SQL Server
--------------------------------------------------------------------------------
When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:
Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply