SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Change Max Memory
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 08/12/2013 :  07:31:33  Show Profile  Reply with Quote
Hello,
They added memory to the server over the weekend. I now want to increase the Maximum Memory limit for my instance. Do I have to do this during an off time or can I do it anytime? Do I need a restart the service to take advantage of the new limit?

Thank you,

djj

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 08/12/2013 :  09:20:40  Show Profile  Reply with Quote
quote:
Originally posted by djj55

Hello,
They added memory to the server over the weekend. I now want to increase the Maximum Memory limit for my instance. Do I have to do this during an off time or can I do it anytime? Do I need a restart the service to take advantage of the new limit?

Thank you,

djj

You can increase the memory on a live system. You don't need to restart or do anything special.

SQL Server may not utilize all the additional memory immediately, but it will grab more and more as and when required and then hold on to it.

Make sure that the operating system has recognized the additional memory before you do this (In computer properties).

Also, remember that the maximum memory is specified in MegaBytes. Not in Gigabytes or KiloBytes. It is MEGABYTES.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 08/12/2013 :  12:32:04  Show Profile  Reply with Quote
Thanks. Had a rough time when we initally went to 2008 with max memory set to the default (quite large). So I am gun shy now about messing with memory settings.

djj
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 08/12/2013 :  16:10:10  Show Profile  Reply with Quote
Maximum memory being left at the default value (2147483647 Megbytes) can cause problems. As SQL Server needs more and more memory, it looks at the maximum memory setting and with an evil grin on its face, grabs more memory from the OS because it is allowed to. It doesn't care whether it will starve other applications or even the Windows OS itself in that process. And when it does starve the OS, life becomes very miserable.

If you keep the maximum memory at a reasonable level, then this should nto happen. A rule of thumb is that on a dedicated system you should leave about 2 Gigabytes for the OS, but a bit more wouldn't hurt. The maximum memory you specify is just for the buffer pool. I tend to leave 2 or 3 GB if the system has 16 or 32 Gigs, and 4 or 5 Gigs if it has more.

In case you are admiring me for my ability to carry around that number "2147483647 Megbytes" in my head, please don't. My dev server is left at the default, so it was easy to look up. But, do as I say, not as I do.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 08/13/2013 :  10:33:59  Show Profile  Reply with Quote
Yep, when set to max we would get memory dumps. Contacted Microsoft who had no idea why. Took them a couple of days to tell us to check the max memory, but once we did joy in Mudville.

By the way I am trying to leave 2 GB on our systems as they are all below 16 Gig (money savings - grumble, grumble).

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000