SQL Server Memory Issues After Upgrade from Express

memorysql server

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

Best Answer

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.

Related Question