Memory settings in SQL SERVER

Question

Memory settings in SQL SERVER

santhosh411

Ten Centuries

Points: 1155
More actions
September 2, 2013 at 11:45 pm

#282748

I have 4GB Ram on OS and What will happen to my SQL server if i configure Min memory settings more than OS RAM that is 5GB and Max memory settings 6GB on SQL Server Memory settings.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Since SQL will never be able to reach 5GB (and hence the min server memory setting will never take effect), nothing different to the default setting (where max server memory is a couple thousand terabytes)

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

durai nagarajan SSCoach Points: 15775 More actions · Answer 2

We have only 2005 test server for us , so i tried on strange it is accepting the value.

Regards
Durai Nagarajan

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

Why wouldn't it accept the value?

If you check books online, max server memory must be a value between 16 and 2147483647, min server memory must be a value between 0 and 2147483647. The values you set are within that range, so they're valid.

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

santhosh411 Ten Centuries Points: 1155 More actions · Answer 4

Is any issues with performance and if my SQL server is running state and configured SQL with min5GB and Max 6GB and the windows team suddenly decreed OS memory from 7GB to 4GB what will happen to SQL Server.

durai nagarajan SSCoach Points: 15775 More actions · Answer 5

But why not they check OS Memory before accepting it?

Regards
Durai Nagarajan

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

santhosh411 (9/3/2013)
Is any issues with performance and if my SQL server is running state and configured SQL with min5GB and Max 6GB and the windows team suddenly decreed OS memory from 7GB to 4GB what will happen to SQL Server.

I already explained how it'll work. After SQL restarts (after the memory removal), it can't reach 5GB so the min server memory will never take effect. If the decrease in memory is permanent, you should go back and change the memory settings, but it'll be no different from a server with the default memory configuration of max server memory being 2000 or so TB.

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

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

durai nagarajan (9/3/2013)
But why not they check OS Memory before accepting it?

Why should it?

How does SQL know that I'm not setting max server memory higher then going home while the server admin adds more memory? Or the VM admin configures more memory, etc?

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

santhosh411 Ten Centuries Points: 1155 More actions · Answer 8

santhosh411

Ten Centuries

Points: 1155

September 3, 2013 at 1:16 am

#1646887

Thank you!