Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 19:59

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
Hmmm... Server 1 does have more physical memory space available than the other two. I will investigate this. –  Jon Seigel Apr 4 '11 at 21:01
    
Yep, this is it. Looks like our memory allocation for SQL Server was a tad aggressive. Thanks! –  Jon Seigel Apr 4 '11 at 21:08

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

share|improve this answer
    
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 '11 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.