Recurring Memory Issues

Question

We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server.

The problem is, periodically (every week or two) the SQL takes up all the memory, literally all of it: resmon shows 0MB available. Now, I did look the problem up, and I know SQL is supposed to take up the entire memory, but it really hurts the web performance, and in the end the SQL itself starts stuttering; scheduled jobs don't execute (the logs say "insufficient resources") etc.

I tried limiting the max memory size to 4096MB. The memory footprint dropped - and rose again to full in a week. I tried scheduling a job that would empty the caches every night. Is that likely to help? If not, what will?

edit Last time it crashed, already after I limited it to 4GB, I wasn't on-site, unfortunately. But the SQL Server logs show the available page file space dropping below 4MB, and the Windows logs report low virtual memory conditions for hours, with sqlservr.exe consuming the most memory (though only about 1,640,919,040 bytes, for some reason).

Answer 1

This sounds a lot like a problem I have experienced before at a client who was also running IIS and SQL Server 2008 R2 on the same server. The problem would also start with reports of very bad web performance and then slowly grind to a complete halt.

After many hours of trouble shooting we managed to attribute it to a single SQL Server setting found under the "Processors" heading in the Server Properties dialog:

When this setting was enabled we constantly experienced the degradation in performance. Although this setting is related to CPU resources we also observed increased memory usage on the server as you explained. It looked as if when this setting was enabled SQL became so hungry for resources that IIS was unable to allocate the resources needed to serve the incoming requests and this in turn caused a backlog of SQL operations which in turn made SQL want to consume more resources and so on and so forth. I have no empirical evidence to back up these claims but this was the observed behavior at the time.

I realize correlation does not equal causation, but we were able to confidently replicate and subsequently mitigate the issue on multiple servers by switching the setting on and off.

After seeing this repeated at a few clients we adopted the policy to have this setting switched of at all clients and we went so far as to report on any changes made to this setting. Suffice to say we have since then not yet experienced issues of this nature again.

I am not saying this is definitely the solution to your problem but given the similarities between the issues you experienced and the ours I would recommend you try it as a first resort since it is very easy to attempt.