1

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.

1
  • If you want me to check specific Hyper-V settings, let me know and I'll ask my sysadmin. He already said all the settings should be the same.
    – Jon Seigel
    Apr 4, 2011 at 19:59

2 Answers 2

3

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

2
  • Hmmm... Server 1 does have more physical memory space available than the other two. I will investigate this.
    – Jon Seigel
    Apr 4, 2011 at 21:01
  • Yep, this is it. Looks like our memory allocation for SQL Server was a tad aggressive. Thanks!
    – Jon Seigel
    Apr 4, 2011 at 21:08
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

1
  • This is only a small fraction of the metrics we're monitoring. The server activity on each is very similar, but the differences shouldn't explain why I'm seeing what should be a fixed setting fluctuate over time. That's what I'm interested in here.
    – Jon Seigel
    Apr 4, 2011 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .