0

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).

  • 1
    How do you know SQL Server process is taking up all the memory? – Shawn Melton May 20 '14 at 18:06
  • Where exactly are you measuring "rose again to full"? After you changed max server memory to 4 GB (you should really change this to 28 or 29 GB, though, since SQL Server clearly does want to use a lot of memory), did you restart the server? – Aaron Bertrand May 20 '14 at 18:08
  • 2
    Emptying the caches every night is almost certainly not going to help. SQL Server is just going to populate them again when the data is requested, and for this the application will have to wait. This is like shrinking your log file every night, knowing it's going to grow again tomorrow. What was the point? – Aaron Bertrand May 20 '14 at 18:11
  • @Aaron Bertrand: I measured it in resmon. Right after I changed it to 4GB, the the memory footprint dropped dramatically from 100MB free to more than 10GB even without restarting the server (though we have restatted it since). – Alda May 21 '14 at 6:48
  • 1
    I'd be curious to see the top consumers of private bytes for this machine. That can be done with PowerShell: Get-Process | Sort-Object PrivateMemorySize64 -Descending | Select-Object ProcessName, PrivateMemorySize64 -First 10 – Thomas Stringer May 21 '14 at 12:09
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:

Boost SQL Priority

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.

  • Ah, interesting! I'll try that. Thank you. – Alda May 28 '14 at 13:36

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.