Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
djj55
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-08-12 : 07:31:33
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-12 : 09:20:40
|
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. |
 |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-08-12 : 12:32:04
|
| 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-12 : 16:10:10
|
| 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. |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-08-13 : 10:33:59
|
| 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 |
|
|
|
|
|
|
|
|
Change Max Memory