Timeline for SQL Server Max Memory not limiting use of RAM
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 27, 2012 at 21:47 | comment | added | Jon Seigel |
Restarting the instance to change memory settings could be a big mistake. While changing the memory settings doesn't cause a CHECKPOINT operation on the databases, it does flush out the procedure cache. If you restart the instance just to change memory settings, not only will the procedure cache be cold, but the data cache will be cold, too. If the max memory cannot be reduced because of dirty pages in memory, run the CHECKPOINT command on the databases to flush the dirty pages to disk, then change the memory setting at off-peak time without restarting the instance.
|
|
| Nov 27, 2011 at 21:07 | comment | added | mrdenny | Correct, SQL can't release a memory page while it is dirty (has been written to). Every time the system checkpoints the dirty pages are written to disk. I don't believe that changing max server memory causes a checkpoint to happen. | |
| Nov 24, 2011 at 17:07 | history | edited | Craig Efrein | CC BY-SA 3.0 |
added 327 characters in body
|
| Nov 24, 2011 at 16:58 | comment | added | Nick Chammas | I would edit these details into your existing answer and delete this one. Perhaps mrdenny can answer your question about flushing dirty pages. | |
| Nov 24, 2011 at 16:29 | comment | added | Nick Chammas | You "may" need to restart SQL Server. It's not always required. Do you know the conditions under which a restart is or isn't required to release memory? | |
| Nov 24, 2011 at 16:10 | history | answered | Craig Efrein | CC BY-SA 3.0 |