2

We doubled the memory on host that has a SQL Server Enterprise 2014 instance on it. After a reboot of the server we noticed that the SQL service is only using half of the memory.

A little background info:

  • The instance is running in compatibility mode for SQL 2008
  • We went from 64 gb to 128 gb
  • On the instance in Properties > General > Memory it shows 131072 MB
  • On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

Do you have any idea why the service is not taking up all of the available memory?

3

After a reboot of the server we noticed that the SQL service is only using half of the memory.

SQL server will use memory as an when needed. Once you reboot your instance, it will take sql server some time to ramp up (bringing pages in the buffer pool), since a reboot will trash all the query plans and reset DMV data.

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Let your workload run and sql server will eventually reach your MAX MEMORY setting.

Check : How to determine ideal memory for instance?

On the instance in Properties > General > Memory it shows 131072 MB

This is the total memory available to the instance i.e. It lists the amount of RAM installed on the server.

On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

The is the MAX memory and you should change it away from the default (use up all memory)

Also, from sql server 2012, Memory management has gone some major changes.

Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.

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.