March 7, 2012 at 5:54 pm
Hi
My server RAM is 32GB, But the Max Server Memory set to 2147483647MB. How this effects? If I would like to change it to less than 32GB Will that effect anything on database data or Services.
Thanks.
March 7, 2012 at 6:48 pm
The current max setting is 2 TB. SQL Server could potentially starve the OS. You really do need to change that setting. Depending on what other software is running on the server (AV, other application software, etc) you should set it to something less than 32 GB. If the only thing running is SQL Server (and maybe AV), I would probably set it to 28 GB. That will leave 4 GB for the OS .
March 8, 2012 at 1:36 am
Thanks for the reply, I do have admin access to database but limited access to OS(log folders and Data folders). If I make any changes to less than 32GB will that effect any where like do I need to restart any services or OS.... I am doing the same server for sharepoint portal ...
March 12, 2012 at 12:40 pm
You can change the max memory setting and it takes effect without requiring a restart. Here is a good guide on what to set it to:
Suggested Max Memory Settings for SQL Server 2005/2008[/url]
The article only mentions 64-bit specifically, but the recommendations are relevant for 32-bit systems as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 12, 2012 at 12:52 pm
March 12, 2012 at 1:13 pm
Jayanth_Kurup (3/12/2012)
Would 4 GB on OS RAM be sufficent for a database being used with sharepoint ?
Depends on what else is installed on the server and how much memory there is in total. If there's nothing other than SQL Server on that machine and there's 32 GB physical memory then 4GB is certainly adequate and probably generous. If the sharepoint frontend is on the same server (so IIS and the web application) then the SQL max memory needs to be lowered to allow the 4GB for the OS and whatever IIS needs.
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
March 13, 2012 at 8:03 am
1) Never EVER leave sql server max memory setting at the default.
2) For 32GB system with JUST sql server running I would start at 27GB and monitor for execessive paging and go up or down from there. The more things you have running the lower you will need to drop the memory. Remember, max mem setting just controls the sql server BUFFER POOL - there are MANY other buckets of memory sql will use that are NOT constrained by that number.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 13, 2012 at 8:21 am
TheSQLGuru (3/13/2012)
Remember, max mem setting just controls the sql server BUFFER POOL - there are MANY other buckets of memory sql will use that are NOT constrained by that number.
Don't think I'd use the word 'many' there. Non-buffer memory is usually a lot smaller than the buffer pool, with a lot less in it. (unless using really wacky CLR that it)
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
March 13, 2012 at 6:15 pm
As I am already holding data in that database server, If I change that to lets say 27GB then do I need to restart any thing like service or database or any other to make this change effected. this change will effect any thing on the existing data.... no right?
March 13, 2012 at 7:43 pm
opc.three (3/12/2012)
You can change the max memory setting and it takes effect without requiring a restart.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply