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 |
|
Peter99
Constraint Violating Yak Guru
498 Posts |
 Posted - 2011-07-20 : 16:26:48
|
| Hi,We have sql server 2008 R2 on windows 2008 64 bits. Right now min and max memory are setting as per default. I want to change max memory settings. Client application is not running well with this setting and they want to change it. I have few questions:1. what is formual to set max server memory?2. Do we need to restart sql service after changing memory settings?3. Any impact on running application?4. Any impact on monitoring tools?5. Any other issue we should consider?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-20 : 16:33:47
|
| 1. There's no "formula" per se, just be sure to leave enough RAM for the OS and non-buffer SQL memory. For example, on our 64 GB server, we set the max SQL memory at 56 GB.2. No. Just run sp_configure to change the setting, then RECONFIGURE after. It takes effect right away.3. None, unless it had maxed out the SQL memory at the time.4. None.5. Even though the impact is low-to-nothing, best to do this change during a non-peak time or your standard maintenance window. |
 |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-07-20 : 16:55:58
|
| Thanks robvolk for reply. Some confusion here:1. Can I calculate max server memory = total physical memory-os-application2. Can I change memory settings by right clicking server->properties-MemoryThanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-20 : 17:17:24
|
| Yes to both. |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-07-20 : 17:42:37
|
| Thanks for your help.Tara what is your opinion? |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-07-21 : 10:54:08
|
| Tara what you suggest? |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2011-07-22 : 16:43:29
|
| Here is setup:1. Windows 2008 R2 standard 64 bit.2. sql server 2008 r2 standard3. One application running on same server4. Total computer memory 4GB.How to distribute it or how to set max sql server memory?Application is running very slow and some time I cannot login to server.Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-22 : 16:52:09
|
| Run this SQL on that server:exec sp_configure 'max server memory (MB)', '3072'reconfigureOr right-click the server in Management Studio, choose Properties, and change the Max memory setting on the Memory tab to 3072.4 GB is not a lot of RAM these days, and setting a maximum may not improve performance (may even get worse). The best thing to do is add more RAM, at least 8 GB. |
|
|
|
|
|
|
|
|
memory settings