On page 50 there is a discussion about three counters to view for a quick look at memory pressure. My question concerns a statement about the MSSQL$<instance>:Memory Manager\Total Server Memory (KB) and MSSQL$<instance>:Memory Manager\Target Server Memory (KB). The book states that "If Total is significantly less than Target, then it's likely that SQL Server cannot grow the buffer pool due to memory pressure". It does say to go and investigate further.

In some other reading I was doing recently, I saw something that said that if Total was less than Target, it meant there was plenty of memory but if Total started to get equal to, or greater, than Target, this indicated memory pressure. Unless I'm missing something, these are contradictory statements and, the second one (not in the book), seems to make more sense to me.

This makes even more sense after checking one of my servers and I see that Target is about 6 GB and Total is about 4 GB but the system is moving along very smoothly and the average page life expectancy is just over 175,000.

I'm just now really digging deep into performance tuning so I would really appreciate some clarification on this topic.

Thanks!

Hi soccerhawg,

What you've observed is an instance of SQL Server that has been configured to use more than it currently needs.

The Target Server Memory of 6GB is either what you've set as Max Server Memory or what SQL Server has dynamically set as the most it can have.

The Total Server Memory of 4GB is an indicator of one of three things:

- The server hasn't been running long enough to grow its usage up to the target of 6GB
- You're experiencing memory pressure that is preventing memory usage growing to 6GB
- There isn't enough workload on the server for SQL Server to need more than the 4GB that its using

The next step is to check PLE which in your case is 175,000 (over 48 hours). This shows that you don't have any memory pressure so based on those results I would conclude that your SQL Server doesn't need anymore than 4GB Max Server Memory. However, that may change over time of course depending on the distribution of your workload.

I wouldn't like to comment too heavily on your other source of information without seeing the context it was written in but I will say that Target and Total Server Memory should definitely be the same or very close on a well configured, busy SQL Server.

I hope that helps!

Regards,

Christian Bolton
Lead Author - Professional SQL Server 2008 Internals and Troubleshooting
MCA: SQL Server, MCM: SQL Server, MVP

That makes perfect sense. I was just getting a little tunnel vision and I was giving too much weight to one counter when I should just use this as a guide and just one part of my research.

In this case, I believe it is simply just a case that the server doesn't currently need anymore than the 4 GB of memory. I'm not going to reduce my memory, though, because, based on what I know about the system, I suspect the extra memory will be used over time.

Thanks for the response and helping me take a broader look at the situation.

You're welcome :)

Christian Bolton
Lead Author - Professional SQL Server 2008 Internals and Troubleshooting
MCA: SQL Server, MCM: SQL Server, MVP