6

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.

3
  • 1
    It may be time to open a support ticket with MS. Sep 26, 2017 at 14:26
  • That DBCC memorystatus dump shows nothing unusual and under 400MB of memory use. Consider reducing Max Server Memory to 6GB or setting 'optimize for ad hoc workloads' if your application generates lots of single-use plans. learn.microsoft.com/en-us/sql/database-engine/configure-windows/… Sep 26, 2017 at 16:14
  • Is this SQL Server on VMware, please also check Vmware configuration so that host is not over committing. LPIM should be helpful in this case as of now SQL Server account does not have this privilege.
    – Shanky
    Sep 27, 2017 at 7:25

1 Answer 1

1
+50

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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