July 25, 2007 at 8:02 am
Hi,
I'm referring to the minimum/maximum server memory settings under Server properties, Memory. I'm a little confused as to how this works even though i've read the appropriate section in bol.
I want to be able to set a minimum figure for all instances when they start, say 256MB and allow them to use more memory as and when is required. Without the need for setting the maximum server memory, is this possible? I don't really want to 'fix' the amount of Ram available to a given server by setting the min & max settings to the same value.
We intend to have three SQL 2005 Standard environments Dev, Test/Training & Production, each virtual server has 4GB Ram and all run 8 instances of SQL 2005 apart from the PROD environment which will house 4 instances.
A bol document I found says "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" which is how I arrived at 256MB as a minimum on a 4GB server (256 x 8 = 2048).
Any thoughts, best practices, comments etc would be appreciated.
July 25, 2007 at 8:17 am
if you have more than one instance then you must set the max memory for each instance, the total assigned memory cannot exceed the total available memory less whatever for o/s. The min memory setting is not so important.
I might suggest that if you're really planning to use 8 instances on a 4gb ram server you will have to allocate no more than 384mb of ram to each instance - and I figure you may be pushing it on that.
Should anyone suggest you don't need to set max memory for all instances then they've probably not run multiple instances!!! If you leave dynamic management the first heavy query on an instance will bring the rest to a halt.
I'd suggest that for anything other than a very small database you'd need at least 16gb of ram to run 8 instances, and this will still give you less memory than a single default instance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 25, 2007 at 8:25 am
When you set min memory you have to set the max memory option too. THere is no alternative with 4GB in the server and if there are few databases being heavily used use the /3GB switch so that sql can use 3GB of memory and OS the remaining 1GB and allow sql to manage memory dynamically.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 26, 2007 at 4:08 am
Thanks Colin/Sugesh for your replies... we've already decided to up the amount of virtual memory allocated to each instance and will allow SQL to manage memory dynamically.
Cheers,
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply