Min/Max Server Memory

Question

Min/Max Server Memory

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Mani (12/18/2008)
Now, we would like to set the Min and Max Memory settings. Could you please advice me what will be the best values considering 16GB RAM and also the page file size.

I'd go for 14 GB memory to SQL, lock pages (since it's enterprise edition) and monitor the free memory. If it gets low, drop SQL's max to 13.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

just a small gotcha for this. If the OS is pressured and cant free memory it could bring the server to a halt. I prefer to start low and work up, the server has less chance of dying suddenly

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Perry Whittle (12/19/2008)
just a small gotcha for this. If the OS is pressured and cant free memory it could bring the server to a halt.

Lock pages just means that the OS can't swap SQL out. If the OS comes under memory pressure it will request a working set trim. Providing SQL's min memory is not set to the same as max, SQL will release memory when requested by the OS.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

assuming it is released quick enough

this link explains the pros and cons

http://support.microsoft.com/kb/918483

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Perry Whittle (12/19/2008)
assuming it is released quick enough

Sure, but the chances of the OS coming under sudden , extreme memory pressure on a dedicated SQL server machine are slim, unless people are using it to copy files or silly things like that.

If the server was for SQL and other apps, I'd set the memory much lower. Since it's dedicated to SQL more memory can be given to SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mani-584606 SSChampion Points: 11788 More actions · Answer 6

thanks. We are decided to go for 13GB as Max memory and adding Domain account in lock pages in memory.

But what about the Min value for Memory? Can we keep the default value 0 or do we need to change?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Leave it at 0. No real need to fiddle with that.

The main reason to change that is for servers that have multiple instances of SQL on them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

GilaMonster (12/19/2008)
Perry Whittle (12/19/2008)
assuming it is released quick enough
Sure, but the chances of the OS coming under sudden , extreme memory pressure on a dedicated SQL server machine are slim, unless people are using it to copy files or silly things like that.
If the server was for SQL and other apps, I'd set the memory much lower. Since it's dedicated to SQL more memory can be given to SQL.

quite agree, file serving, etc shouldnt be done on the SQL server but you see it all too common nowadays.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Mani-584606 SSChampion Points: 11788 More actions · Answer 9

thanks Gail,

We have 3 node a\a\p cluster setup.On node1 we have 1 instance and on node3 4 instances.

Presently we have memory issues on node1.Node3 is fine so far.

We have windows 2003 EE 64bit ,SP2 and SQL Server 2005 EE 64bit SP2. RAM 16GB

So on node1:

Going to change Memory Settings on node1 as

Min 0

Max 13GB

Increasing page file size to 1.5*16GB=24GB (right now it is 2046MB)

Adding Domain account in lock pages in memory.

Suggest me above settings for node1 are make sense or not?

On node3 for 4 instances the memory settings are set to default only.

It would be great help if you suggest me the memory settings and page file size for these 4 instances on node3.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

The balancing of memory among the instances depends on how much they're being used. As a guideline, you could divide memory equally and see how the instances perform and then adjust each from there.

Mani-584606 SSChampion Points: 11788 More actions · Answer 11

Hi,

I just want to make sure that Can I increase the page file size 1.5 times physical RAM?

I got the below readings from perfmon counters:

Paging File(\??\C:\pagefile.sys)\% Usage =27.31262983

Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841

Mani-584606 SSChampion Points: 11788 More actions · Answer 12

Hi,

In our 3 node a/a/p cluster on node 3 we 4 instances. In this case, to set Max n Min values what should i consider?

We have 16GB memeory and 2GB page file size.