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:
- total = 2452,9
CACHESTORE_SQLCP
= 2141,5MEMORYCLERK_SQLBUFFERPOOL
= 160MEMORYCLERK_SOSNODE
= 33USERSTORE_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.