1

We have increased the RAM capacity on the OS to 40G and I have changed the max memory parameter to 36G in SSMS.

When I select "current value", it shows 36G.

However, monitoring memory usage at OS level doesn't show more memory being consumed.

I know that, in theory, it's possible to change the max memory setting while SQL Server is still running, but does it really work? Do I need to restart the server? Or am I being impatient?

Thanks

  • What are you using to "see" memory usage in Windows? Task Manager? If you are using task manager, and your SQL Server has the "lock pages in memory" right, task manager will not reflect the actual usage of the SQL Server process. – Max Vernon Nov 3 '15 at 16:31
  • I am aware of how memory resources are allocated to the database and how the database manage it and I have very good reasons to increase the RAM to the OS but for a long moment it didn't seem like the OS was allocating to the database. That's what triggered my question. As opposed to: My OS memory is totally consumed (by DB), I need more memory. (wrong) – Nicolas de Fontenay Nov 4 '15 at 21:55
3

That setting is not "go use a bunch of memory for whatever." It is a setting that dictates the maximum usage of memory for the buffer pool (data cache, plan cache, and a few other areas of memory reservation).

In order to actually use 36GB of memory, you need to run queries that will force the buffer manager to pull data off disk and put it into memory. Run the following query and you will see memory usage rise (also, in case it is where you are looking, do not use Task Manager for this).

SELECT * FROM dbo.YourBiggestTable AS ybt1
  CROSS JOIN dbo.YourBiggestTable AS ybt2;
| improve this answer | |
  • Guilty. I was doing exactly that. I do know it needs to run queries pulling from disk.| And I have very good reasons for increasing that memory :( And while I was busy looking around the web, I actually see it has increased memory usage by 4%. So there you go! Thanks for that answer though. – Nicolas de Fontenay Nov 3 '15 at 16:25
  • What is the metric you use if you don't look at Task Manager? – Nicolas de Fontenay Nov 3 '15 at 16:31
  • 1
    Performance Monitor is a much better place to get accurate memory information. – Aaron Bertrand Nov 3 '15 at 16:40
  • And if you are using 2008 and above and like to use DMV select * from sys.dm_os_process_memory will give you total memory used by SQL Server. Dont look at task manager – Shanky Nov 4 '15 at 9:29
  • 1
    @Ray Actually the buffer pool includes the data cache and the plan cache. The first link I found was this but there are plenty of places you can learn more about this on your own. – Aaron Bertrand Nov 4 '15 at 14:37
0

SQL Server will only grow to fill more memory as needed. It has not found it beneficial to increase it's memory usage so it is not. And yes, changing the max memory setting is an online operation, no need to restart anything.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.