I have been having issues with our SQL server for a while now. It seems to run out of memory every few days and when I look at the memory dump, the
MEMORYCLERK_SQLOPTIMIZER seem to take over memory and eventually cause the server to crash.
Here is the SQL version we are using:
Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64) Jul 22 2014 15:22:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
It is on a VM on Windows 2012 Server.
It has 20 GB of RAM allocated to it and the
Max Server Memory is set to 16.5 GB.
I have seen the
MEMORYCLERK_SQLOPTIMIZER grow to about 11 GB at the time of the server crash.
Does anyone know why that is happening? What is causing the
MEMORYCLERK_SQLOPTIMIZER to get so high?
I have looked it up and it looks like it has to do with ad-hoc requests, but is there something I can do to bring that memory down when it gets so high so that I can prevent a server crash?
Do we just need to add more memory or is there a memory leak somewhere?
I have already enabled
optimize for adhoc and that does not seem to help. We will be deploying SP2 soon. We are using VMWare 5.1 and we are not using dynamic memory.