0

The process of SQL Server 2014 is using high memory even it's idle and no query is running on. after restarting the service, it happens again and memory usage grows to 2.2GB after a while.

theProcess

the host is Windows Server 2012.

Why? and how can I find the problem?

  • Why is this a problem? If your total available memory is low, you can always set the MAX Memory to be used by SQL. However, I believe even this doesn't guarantee it won't go beyond that value. – Nestor Apr 22 '17 at 11:55
  • 1
    By default, SQL Server will use and hold onto as much memory as it would like unless you configured max server memory limit, or when it detects memory pressure. Read the documentation: docs.microsoft.com/en-us/sql/database-engine/configure-windows/… – Dan Guzman Apr 22 '17 at 12:41
  • @Nestor : Just to know if there is a problem. thank you all – Amin Sh Apr 23 '17 at 5:12
5

It's not unusual for SQL Server to have rather high memory utilization, even when the server is idle.

SQL Server will increase memory utilization up to the configured maximum--then hold on to that memory, even if it is not being actively used. This increase in memory usually corresponds to data being read from disk, into memory, and held in cache (thus improving the performance the next time that data needs to be used). The only time you might see SQL Server release memory is if the system is under extreme memory pressure and SQL Server is also idle.

The default configuration for SQL Server is to use a minimum of 16 MB, and a maximum of 2147483647 MB (ie, unlimited). Under this configuration, you are essentially allowing SQL Server to self-manage it's own memory.

To see how your instance of SQL Server is configured, you can run this in Management Studio to see the minimum & maximum memory configuration:

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'min server memory (MB)'
EXEC sp_configure 'max server memory (MB)'

If you wanted to configure SQL Server to allocate 128MB of memory immediately on startup, and max out at 1024MB, you would use this script:

EXEC sp_configure 'min server memory (MB)',128
EXEC sp_configure 'max server memory (MB)',1024
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.