Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DataBase Max Server Memory Setting


DataBase Max Server Memory Setting

Author
Message
kumar.4friends
kumar.4friends
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24176 Visits: 37946
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 .

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kumar.4friends
kumar.4friends
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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 ...
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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

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
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 1351
Would 4 GB on OS RAM be sufficent for a database being used with sharepoint ?

Jayanth Kurup
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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

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


TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 8301
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 at GMail
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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

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


kumar.4friends
kumar.4friends
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search