max memory in sql server usage - setting

this sql server has 4GB ram in total.

in memory setting, the min mem is 0 and max is 2147483647

this is a dedicated sql server. is there any concern in the way it is defined above?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You can leave it as it or set the max value as 3.5GB.. Also please read this
http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
0
w33mhzCommented:
Well SQL server will take memory as it needs it, so the more data that gets accessed the more data that will get put into RAM, so its good practice to set the max to about 1 - 2GB less then what the total system RAM is.  When the sql server service starts it will grab a small amount of RAM then as new data is accessed it will try and cache that data into RAM for an amount of time, if its not accessed again it will free that memory up, but it will NOT release it back to the system to use, it will just keep it reserved to cache more data.  This is because the perfomance is better to store data that it has already allocated in RAM then to request it from the system.

Usually on larger capacity (or RAM) sql servers I tend to try and reserve at least 4GB of RAM to the system.  Sqlsevr.exe process will not care about the system memory if you don't set a reserve and it will eventually try and take all the RAM in the system.
 
Of course if this server that you are setting up is not going to be a critical server, not hosting a lot of mission critical data (which I am assuming because of only 4GB of RAM), then you may not need to worry about it, but its good practice to limit the RAM.  If this is just a SQL Server Express installation then I wouldn't even really worry about it.
0
Anthony PerkinsCommented:
is there any concern in the way it is defined above?
Yes.  You need to install more memory.
0
Sagir87Commented:
Hi guys,

Please go throguh this link:

http://technet.microsoft.com/en-us/library/ms178067.aspx

To change max memory setting in sql server usage please go through the below link:

http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.