Total Server Memory Vs Target Server Memory

Question

Total Server Memory Vs Target Server Memory

pshaship

SSCertifiable

Points: 6125
More actions
September 11, 2010 at 1:20 pm

#141922

Hi,
We have SQL Server 2005 EE x64 with SP3.
RAM = 16 GB
Max Server Memory = 12 GB
Min Server Memory = 0 default value
Lock pages is enabled
In our server, Total Server Memory (KB) and Target Server Memory (KB) are always same.
I read in many forums that if both Total Server Memory (KB) and Target Server Memory (KB) are same, then you have Memory Pressure.
You should have Total Server Memory (KB) less than the Target Server Memory (KB) and that indicates NO memory pressure
But today I read the book "professional-sql-server-2008-internals-and-troubleshooting" and from that:
MSSQL$<instance >:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$<instance >:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool. Total and Target should be almost the same on a server with no
memory pressure that has been running for a while. If Total is significantly less than Target,
then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure, in
which case you can investigate further
So it's the other way.i.e
If you have Total Server Memory (KB) and Target Server Memory (KB) are always same ---> NO memory pressure
If you have Total Server Memory (KB) less than the Target Server Memory (KB)---> Memory pressure
I appreciate your inputs and eliminate the confusion by clarifying which one is correct and why:-)
Thanks

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Total is what SQL currently has. Target is what it thinks it wants under the current load. So if total is lower than target, either SQL is still building up the memory allocation, or it needs more than it has (ie it's under memory pressure)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pshaship SSCertifiable Points: 6125 More actions · Answer 2

Thanks Gail,

On Server A, we have 4 instances. We have 16 GB RAM. Lock pages is enabled.

For each instance, I have set the Max Memory to 3 GB and Min Memory to default and left 4 GB for OS

For 1st 3 instances, the Total Sever memory = Target Sevrer Memory.

But the last instance has the Tatoal Server memory always has 2 Gb and Target Server memory has 3 GB.

In this case, can we say that the instance 4 is under memory pressure?

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Yes, Instance 4 appears to be experiencing Mmeory Pressure.

Thank You,

Best Regards,

SQLBuddy

pshaship SSCertifiable Points: 6125 More actions · Answer 4

Yes, Instance 4 appears to be experiencing Memory Pressure.

In this case for instance 4, when I set the Max Memory to say 4GB, then if the Total Server memory is going to increase to 4 GB then we can say that there is NO Memory pressure?

What if the Total Server memory is still 2 GB even after increasing the Max Memory to 4GB? Then the Memory Pressure still there?

If the Max Memory is set 3 GB and the Total Server memory is 2 GB, then I'm assuming that the instance is happy with 2 GB and it has 1 GB extra memory . Is that right?

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

You are right. Sorry for my previous post. I got little confused.

So here is the deal..

If Total Memory >= Target Memory ------> Represents Memory Pressure

Total Memory < Target Memory ------> Represents No pressure.

So Instance 4 is not experiencing any mem pressures. But the other 3 are experiencing Mem Pressure.

Thank You,

Best Regards,

SQLBuddy

pshaship SSCertifiable Points: 6125 More actions · Answer 6

If Total Memory >= Target Memory ------> Represents Memory Pressure
Total Memory < Target Memory ------> Represents No pressure.
So Instance 4 is not experiencing any mem pressures. But the other 3 are experiencing Mem Pressure.

But if you read the book "professional-sql-server-2008-internals-and-troubleshooting"

The memory pressure is explained the other way..from the book

MSSQL$<instance >:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$<instance >:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool. Total and Target should be almost the same on a server with no
memory pressure that has been running for a while. If Total is significantly less than Target,
then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure, in
which case you can investigate further

So I just want to know which one is correct;-)

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

I think this is correct

Total Memory >= Target Memory ------> Represents Memory Pressure

Total Memory < Target Memory ------> Represents No pressure.

http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx

http://blogs.msdn.com/b/teekamg/archive/2007/11/06/sql-server-memory-related-performance-counters.aspx

Thank You,

Best Regards,

SQL Buddy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

sqlbuddy123 (9/13/2010)
So here is the deal..
If Total Memory >= Target Memory ------> Represents Memory Pressure

If Total > Target it means that SQL is trimming its working set due to a request from the OS. It can signify that the OS is under memory pressure, but not SQL.

Total > Target means that SQL is reducing its memory usage. It's not going to reduce its memory usage if it is under memory pressure. In fact, it won't reduce its memory usage unless either the OS tells it to or the max server memory setting is changed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mcroce Old Hand Points: 358 More actions · Answer 9

mcroce

Old Hand

Points: 358

March 8, 2011 at 2:32 pm

#1296168

so who to believe?

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 10

Right, I'm so confused now and I thought I had it figured out!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

sqlbuddy123 (9/13/2010)
I think this is correct
Total Memory >= Target Memory ------> Represents Memory Pressure
Total Memory < Target Memory ------> Represents No pressure.
http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx
http://blogs.msdn.com/b/teekamg/archive/2007/11/06/sql-server-memory-related-performance-counters.aspx
Thank You,
Best Regards,
SQL Buddy

I do not agree with this. In my case with the same methodology which you have up behaves exactly the opposite way. I believe if total < target then memory pressure.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

It's not this simple. The more I dig into things, the less I think I understand on memory pressure and the many ways to detect it.

First, if total (what you have) < (target), the instance does not have enough memory. It wants more, and can't get it for some reason.

If total > target, as Gail mentioned, you have some potential issue.

When I read the memory bottleneck section in here: http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA, I see no mention of target or total memory, but a detailed explanation of various types of memory pressure. I also see lots of references to DBCC MemoryStatus, which is explained here, but very confusing:

http://support.microsoft.com/kb/907877

shifty1981 Ten Centuries Points: 1071 More actions · Answer 13

Was reading up on this and thought I'd add some insight. We just spun up a new SQL server that will grow immensely, but right now it's barely touched. We put in 64GB memory and set max memory to be 32GB. On all our similar servers, Target ALWAYS = max memory. On this server, the same is true, but Total Memory is only 1GB.

So a few things are true:

Target means "max memory" if the value is set.

If "max memory" is not set it means what SQL thinks it needs (based on some calculation)

Total Memory means how much memory SQL is using. On all our seasoned SQL server, Total = Target meaning it's "possible" if we raised max memory target and subsequently Total memory would go up.

Finally, if Total < Target, then there is enough memory. There's no way our server which has almost zero activity is saying there's memory pressure and it wishes there were more memory than 1GB, because there is! If Total=Target, there may be memory pressure. I don't see how Total could ever get higher than target. That'd be like saying SQL only wanted so much or was allowed so much but SQL ignored itself and took more!

Point being if Total=Target, does not necessarily mean things are peachy. You need to turn off Max Memory, let it be dynamic and then see how Target and Total adjust. I'm almost certain if I did this on my servers I would see Target far exceed the 32GB we allow it to take normally and probably Total would grow as the OS and apps let it take memory.

mcroce Old Hand Points: 358 More actions · Answer 14

Good post Thats more or less the conclusion i came to in the months since i first posted here. looking at the original post there was confusion as to whether total memory being less than target memory represented pressure. This is counterintuitive if you assume target is the max the server can use and total is what ther server is acually using. but the key word is "significantly" . "If Total is significantly less than Target,then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure" so in the example if the otal memory never grew more than 1 gig and the server was running for awhile, that could indicate memory pressure just as much as could total=target. Figuring it out whether or not there is actual memory pressure based on these indicators is the reason they have DBA's i suppose, but I think there could be a better way to represent it.