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.

  • 1
    Did you monitor the queries on this server? Even having a table of 10Mb you can create a query with 5 cross joins and ORDER BY that will try to sort 1Tb of data – sepupic Sep 26 '17 at 7:07
  • 1
    It may be time to open a support ticket with MS. – John Eisbrener Sep 26 '17 at 14:26
  • 1
    @JSC try running sp_Blitz OutputType = 'markdown' as described here, and paste it into your question: brentozar.com/archive/2009/03/getting-help-with-a-slow-query – Brent Ozar Oct 4 '17 at 22:19
  • 1
    Is this instance running SharePoint? – Tom V Oct 8 '17 at 10:38
  • 1
    Can you check the resource monitor and see if it is actually SQL server taking the memory? Might be a memory leak of another program – Stijn Wynants Oct 10 '17 at 12:10
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.

  • While you are correct on CU part, this definitely is not the answer to OP's question, regarding memory leak we need to be sure because with current output I doubt it. Anyhow it seems like OP is not interested in this question anymore so he is not replying to most of the questions asked in comment section – Shanky Oct 12 '17 at 6:04

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.