February 19, 2014 at 2:37 pm
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?
February 19, 2014 at 3:45 pm
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[/url]
Learn Extended Events
February 19, 2014 at 3:47 pm
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.
February 19, 2014 at 3:50 pm
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[/url]
Learn Extended Events
February 20, 2014 at 12:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2014 at 7:33 am
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.
February 21, 2014 at 7:47 am
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[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply