We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite acceptable, but the memory consumption gets more and more until all available memory (30GB) is exhausted. After that, the system gets slower and slower.
Dbcc freeproccache have no effect.
There are not many transactions in
select * from sys.dm_tran_session_transactions (not more than when the system is fine), some times this list is empty.
The first result of
dbcc memorystatus is
VM Reserved 42136628 VM Committed 1487176 Locked Pages Allocated 24994048 Reserved Memory 1024 Reserved Memory In Use 0
A restart of SQL Server solves the problem for a while.
- What causes this behavior? How can it be avoided?
- If a real solution for the cause is too difficult, is there a command that forces SQL Server to actually release all memory without a complete DBMS restart?
The server is running on dedicated hardware (not a VM). We had some scheduled jobs, but we disabled them for a while, with no change. There are other mid tier applications running on the same server, but they use no more than 2GB memory, negligible CPU, and almost no I/O. We restarted all such applications with no change.