1

I have a SQL Server 2014 Standard (x64) instance that was recently upgraded from SQL Express.

The VM that the SQL instance is on has ~16GB of RAM and SQL MAX MEMORY has been configured to use ~12GB

I want to understand while i configured: max server memory (MB) = 12000, why the SQL instance is not consuming more than ~4154MB when i verified it with following query:

select (physical_memory_in_use_kb/1024) usage_in_MB
from sys.dm_os_process_memory

  • 3
    Why do you think it should use more? If sql wont use it unless it needs it. Have you selected more than 4gb worth of data? How big are your tables? And is it causing any problems? – Sir Swears-a-lot Sep 19 '19 at 10:16
  • Can you post complete output of select * from sys.dm_os_process_memory and select @@version. – Shanky Sep 20 '19 at 6:57
2

max server memory is the maximum you will allow SQL to use. This is primarily set to leave memory for other process like the operating system, or if you have multiple instances on one machine.

Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. Source

SQL only uses as much memory as it needs, so it is not unusual to see memory usage much lower, when SQL does not need it.

Even when you think SQL should use more CPU, sometimes it chooses not to. I have some index reorg jobs that I want to work harder, but they will never use more then 10 or 20% of CPU. See for yourself, make several "Ugly" indexes and then use the tool of your choice to fix them.

Setting Max at 12GB when you have 16GB is a well balanced choice in most single instance configurations.

| improve this answer | |
0

run this to check configuration:

sp_configure 'max server memory (mb)'
sp_configure 'min server memory (MB)'

if you are still concerned it won't access the memory up the minimum to 8GB, and see if the values changed accordingly:

sp_configure 'min server memory (MB)', 8192
reconfigure with override

set it back to the value observed in the first call to reset it normal.

| improve this answer | |
  • 1
    According to Server Memory Configuration Options, SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory unless the value of min server memory is reduced. – Ronaldo Sep 20 '19 at 12:18

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.