Min and Max Dynamic Memoery

[SimonTheSponge]

Hi,

We have SQL Server 2000 Standard configured to use memory dynamically. What are the min and max recommendations. We have 1.5 Gb settings are 755 min and 1268 max. We plan to upgrade to 2GB. Is there a rough and ready rule for these settings as a percentage of total available ram (50%, 80% eg)

Cheers

 

[JamesLean]

If this is a dedicated SQL Server then best option is to not set explicit limits and just let it take what it needs.

If you have to set limits, it would depend entirely on what else was running on the server and how much memory that might need. I would think that there are few instances where you actually want to set a min level, but you might want to set a mx level if you don't want SQL Server eating up all the memory.

--James

 

[SimonTheSponge]

James,

Does this mean that if we do set limits and the dynamically configure SQL Server Memory radio button is checked that it will dynamically use memory between those two specified limits or it will ignore the limits. I am using Enterprise Manager via the memory tab

Cheers

 

[JamesLean]

Yes. Select "Dynamically configure memory" and set the min and max limits you want.

As I said, if possible put the min fully to the left (0) and the max fully to the right (this is the default configuration).

--James

 

[mrdenny]

You shouldn't ever configure the SQL Server to use all the memory that is installed in the server. If you do then you leave no memory for the OS.

I always recommend leaving the minimum at 0 (there is no need to force the SQL Server to take more than it needs). However the Max memory should be set at 512-1025 below the total memory on the server. In your case you have 1.5 GB installed, so I would set the max memory for SQL at 1 GB. This will leave 512 MB for the OS to do what it needs to do.

When you move the server up to 2 GB, then you can increase the SQL Server to 1.5 GB of memory.

If you don't reserve any memory for the OS the OS will use only the page file for it's own use, which will greatly slow down the servers performance.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Corran007]

i usually go with 75/25 for server memory. Seems to work fine but your milage may very

 

[SimonTheSponge]

Thanks All,

Looks like we're upping from 1.5 to 2.5, so likely to set the max limit to 2Gb leaving .5 for the OS

Cheers

 

[mrdenny]

What OS are you running? Windows 2000 standard only supports 2 GB of memory.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SimonTheSponge]

Hi Denny

The tech support area tell me that it's Windows 2000 Advance. They're the ones doing the memory upgrading, so I'm assuming that they know the ins and outs of how much memory they can pop in (I hope) ;-)

Cheers

 

[JamesLean]

Just be aware that, regardless of what version of Windows you are running, SQL 2000 Standard only supports up to 2GB RAM.

--James