Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Max server memory Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:01 AM
Points: 54, Visits: 151
I have a SQL2008R2 server that pretty consistently hogs about 47GB of the 48GB of RAM on the box. One of the sys admins has asked me to change the limit. He didn't specify what to set it at. It's currently just the default (dynamic). I've never played with this and I'm iffy about it. It's a critical server. It's our SharePoint database, which houses several business-critical functions and it's hit this hard 24x7x365. My fear is that if it's consistently using all of the RAM, it may be because it needs it. If I crank that limit down, is my server going to crash?
Post #1543228
Posted Wednesday, February 19, 2014 3:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
It is a best practice to set a max server memory limit. Right now, you are starving the OS of memory and in the end you could be causing contention for memory.

You should reduce max memory for sql server. Here is an article with a guideline and formula.

http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1543269
Posted Wednesday, February 19, 2014 3:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:51 AM
Points: 25, Visits: 271
If no maximum is set, then SQL uses as much RAM as it needs. You can set a maximum, say 44,032 MB -- allows 4GB for OS operations. One catch - you must restart the SQL service before the changes take effect. You will have to schedule a momentary outage.

Blog: http://sqlexchange.wordpress.com
Post #1543270
Posted Wednesday, February 19, 2014 3:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
Gary Gwyn (2/19/2014)
If no maximum is set, then SQL uses as much RAM as it needs. You can set a maximum, say 44,032 MB -- allows 4GB for OS operations. One catch - you must restart the SQL service before the changes take effect. You will have to schedule a momentary outage.



No. The change is immediate and no need to restart SQL Server.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1543272
Posted Thursday, February 20, 2014 12:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 40,204, Visits: 36,607
See Chapter 4 of http://www.red-gate.com/community/books/accidental-dba for a lot of discussion of memory and some guidelines around the setting of this value.

The setting takes effect immediately, no restart is required.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1543344
Posted Friday, February 21, 2014 7:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 9:51 AM
Points: 25, Visits: 271
Boy, do I feel stupid. I stand corrected. I just tested this via the GUI and a script and the change is effective immediately.

I'm not sure where I picked up my misinformation. Thanks for the correction, as it will make my life easier.



Blog: http://sqlexchange.wordpress.com
Post #1543983
Posted Friday, February 21, 2014 7:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
Woohoo - that's a win then.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1543999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse