4

I ran sp_Blitz that pointed me to a memory issue saying:

Memory Dangerously Low The server has 32755 megabytes of physical memory, but only 235 megabytes are available. As the server runs out of memory, there is danger of swapping to disk, which will kill performance.

The Server has 32 GB RAM total, 0-2 MB Free RAM, 200 - 2376 Mb Available.

@@Version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I watched Performance Counters for a while and see the following values there:

Page Faults/s _Total ~ 1.200 Average, Max 18.000, regular Peeks up to 1500.
Page Faults/s sqlserver ~ 380 Average, Max 1600, regular Peeks up to 1000.
Target Server Memory 30006
Total Server Memory 30006
Buffer cache hit ratio  AVG 99,9
Target Pages 3.750 stable
Total Pages 3.750 stable
Free Pages Avg 8300, Max 13.000
Memory Grants Pending 0
% Processor Time _Total Average 15%

No. of User Connections is constantly around 300.

From my perspective some of these values indicate that the server is doing well, e.g. Memory Grants Pending or Buffer cache hit ratio. Page Faults/sec look as if there is some caching in place. Resource Monitor shows that Hard Faults /sec (is this the same value) also has regularly high peeks.

So I wondered why sp_blitz indicated this issue. Am I facing a serious I NEED MORE MEMORY issue or is it just pointing to that 30GB assigned to sql of 32Gb physical memory leaves not enough memory left for windows?

  • Please add output of select @@Version. Are you saying that server has total 32 G RAM and max server memory is 30 G ? – Shanky Oct 2 '15 at 7:52
  • I added it in the question. Yes, that is how it has been configured before I suddenly started to own it :) – Magier Oct 2 '15 at 7:55
5

If you look closely at SpBlitz memory recommendations it says it has two possible meanings

Memory Dangerously Low or Max Memory Too High

In your case looking at perfmon counters output you have shared their does not seems to be a memory pressure. So we are left with the fact that max server memory is configured incorrectly. And this seems true, on a system having 32 G memory you have given 30 G to SQL Server this would definitely leave OS fighting for more memory.

In this case OS will start paging out SQL Server processes to disk making it very slow. Or if Locked pages in memory is there for SQL Server service account OS process would be paged to disk. Both these conditions are very bad for SQL Server and OS.

I suggest you take help from This SE thread to calculate appropriate value for max server memory. I have given answer on this thread which asks user to take help of perfmon counters to reach to a sensible value.

You can start by giving 27 G to SQL Server and leaving 5G for OS. Then use perfmon counters mentioned in SE thread to reach to sensible value.

EDIT: I forgot to ask you to apply SQL Server 2008 R2 SP3, currently SQL Server is on SP2. This is must if you need extended support. In any case, to be on safer side you should always make sure SQL Server is patched to latest service pack.

  • thank you - following the thread you quoted, I result in 2550 MB of memory leaving (not knowing anything about "+Max worker threads * 2MB") - this is very close to what I have in place, isn't it? On sqlskills.com/blogs/jonathan/… I read another rule that leads me to keep about 5 GB for windows. So should I use about 3500...? – Magier Oct 2 '15 at 13:08
  • I cannot understand your comment. If guess you want information about max worker thread. Yes 5G is correct but it might not always be correct in long run. Assuming there would be changes on system. You should use perfmon counter to monitor this value periodically. – Shanky Oct 2 '15 at 13:53
  • I tried to say: Following the instructions you have posted, I ended with a setting very close to what I already had in place (using 30G or 32G). Meanwhile I have changed the settings to use 28k instead of 30k Mb of 32k MB. Available MB raised slightly, PLE increased very slightly. Frree MB of course increased. The Hard Faults Peeks decreased. So it looks like a success. TY – Magier Oct 6 '15 at 13:46

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.