Home / server / Questions / 255611
Accepted
Jon Seigel
Asked: 2011-04-05 11:40:36 +0800 CST

What can cause SQL Server's maximum memory setting to appear to change over time?

  • 772

I started analyzing our SQL Server PerfMon logs in Excel, and discovered this.

Legend:
Red - SQL Server - Target Server Memory
Green - SQL Server - Total Server Memory
Blue - Available System Memory

Server 1:

Server 2:

Server 3:

Has anyone else seen this before, and should we be worried?

Configuration is identical across all servers, except for the maximum memory setting -- 14GB on Servers 1 and 2, and 10GB on Server 3. Minimum memory is zero on all servers. These are SQL Server 2008 R2 instances, each in it's own VM running Windows Server 2008 R2 (SP1 applied mid-month) under Hyper-V.

What I would expect is to see a graph like Server 1 (horizontal red line), but for Servers 2 and 3, how is the value being reported for Target Server Memory fluctuating over time? What can't be seen from the graphs I've shown is that the fluctuations match up somewhat to the Available Memory metric. Also, the server restarts (blue vertical lines) don't seem to affect the values either. Is this a bug in PerfMon?

PerfMon is set up such that all 3 servers are inside a single data collector set, with each server having its own performance counter with all the parameters we're tracking.

Any help or insight would be appreciated.

sql-server memory perfmon memory-usage sql-server-2008-r2

2 Answers

  1. Best Answer

    It sounds like you are confused by target server memory and presume that it's a static value. Target Server Memory is the upper limit amount of memory SQL Server plans to use (note that in the docs the terminology is "can consume" not "is available". This is a dynamic number and during memory pressure , it will reduce the Target Server Memory, and then reduce (or increase) Total Server Memory until it reaches the Target. From just what this graph shows I would expect there are other things putting memory pressure on the other 2 SQL servers

    • 3

  2. Based on the information you have shared so far, I don't see any issues. What you need to ask is the activity on all these servers equivalent? How are you measuring that? If your total and target are not the same and if total < target that means you have more head room. You may also want to look at other metrics like PLE, Buffer cache hit ratio etc... and don't ever rely on only one metric.

    Are you seeing any performance issues on these servers? Have you checked the WAIT STATISTICS on these boxes? Have you looked at the Virtual File Stats etc... My theory is you should monitor these metrics over a period of time and see if they are trending up or down and don't worry about the number for a specific small interval.

    HTH

    • 2