4

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.

  • Can you post the info of dbcc memorystatus? Also, are stats and index up-to-date and not fragmented respectively? – Kin Shah Jun 11 '15 at 16:22
  • I can't paste the memorystatus, it's too long. – kaitlyn Jun 11 '15 at 16:54
  • 701 errors are key thing that you described. Check this KB - SQL Server 2012 experiences out-of-memory errors - which is fixed in 2012SP1-CU2. You can post the dbcc memorystatus on pastebin.com – Kin Shah Jun 11 '15 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.