memorysql-server-2012
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
The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you've run enough queries - which tables / indexes it should load into buffer pool memory.
So yes, it is normal to see a gap in these values. You can probably get much closer to the max values if you pump the buffer pool by issuing a bunch of SELECT * FROM dbo.BiggestTables; on each instance, but it is usually better to let SQL Server allocate the memory as it deems appropriate based on actual usage by your real application. And remember too that max server memory doesn't just cover the buffer pool.
In short, SQL Server is pretty good at memory management; let it do its thing, and worry about adjusting max memory on your various instances when you have an actual performance issue.
The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.
There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:
Using locked pages in the memory manager
Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:
Whereas Resource Monitor shows:
Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.
There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.
It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.
Generally speaking, locking pages in memory is a good thing.
Best Answer
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).