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
Shekar Kola
  • 2,477
  • 2
  • 10
  • 24
Lickrob
  • 84
  • 1
  • 9

2 Answers2

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.

James Jenkins
  • 6,318
  • 6
  • 49
  • 88
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.

Alocyte
  • 418
  • 3
  • 16