Do I have a memory issue?

Question

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?

Answer 1

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.