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?