Total Server Memory Vs Target Server Memory

  • 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

  • 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
  • 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

  • Yes, Instance 4 appears to be experiencing Mmeory Pressure.

    Thank You,

    Best Regards,

    SQLBuddy

  • 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

  • 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

  • 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

  • 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

  • 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
  • so who to believe?

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

  • 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.

  • 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

  • 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.

  • 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.

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

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