Sql-server – SQL Server takes far too much memory

memorysql serversql server 2014

We have a problem with one of our SQL Servers that I have never seen before.

The SQL Server (Enterprise Edition) runs a database with only 70MB data. But after a few hours the server takes 10 GB memory or more. After a few days the server runs out of memory. I already configured the max memory parameter in SSMS, but it didn't help.

Do you have any ideas what to do to find the problem?

Here is the link to the result of dbcc memorystatus: https://drive.google.com/open?id=0B2FJAT6lteKJOEJmUFdqb0ZERFU

Database Version: Microsoft SQL Server 2014 (SP2-CU5) (KB4013098). Enterprise Edition. This is a virtual machine.

I checked another thread and found this query:

SELECT TOP (21) [type] = COALESCE([type],'Total'), 
  mb = SUM(pages_kb/1024.0)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY mb DESC;

I ran this on the SQL Server and get the following results:

  1. total = 2452,9
  2. CACHESTORE_SQLCP = 2141,5
  3. MEMORYCLERK_SQLBUFFERPOOL = 160
  4. MEMORYCLERK_SOSNODE = 33
  5. USERSTORE_SCHEMAMGR = 30.94

Is this normal that the cachestore value is that high after it has been running only hours?

Running this query:

select value_in_use
from sys.configurations
where name = 'max server memory (MB)'

Gives the result 8192. This is the value we configured for the instance.

At the moment we have to restart the SQL Server every night so that it is not using too much memory.

Best Answer

Your question, at first, surprised me. Usually, I've seen memory issues with max server memory being configured incorrectly. But then I started pouring over the cumulative updates and realized that their are minefields of memory leaks sewn throughout all these cumulatives for SQL 2014! It's like Microsoft took a hint from their SQL 6.5-7.0 playbook and went retro on us.

Rather than spend hours of trouble-shooting, I would consider:

Option A: Taking a snapshot of the VM and installing the latest cumulative 7 update for SQL 2014 sp2. And rolling back if necessary. (it looks like a memory leak related to SSAS is included in this update).

Option B: Create a Microsoft support ticket to help yourself and the rest of us out by possibly adding a memory leak fix to cumulative 8 when it comes out. And updating your question based on Microsoft's findings.

Personally, I don't think I would consider any reason satisfactory for SQL Server going much above it's max memory limit--for any situation.

Related Question