none
Total Server Memory VS Target Server Memory made me crazy. RRS feed

  • Question

  • I often see somebody use the Total Server Memory compare to Target Server Memory to decide whether the SQL Server under memory pressure. a book say:”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”;

    that is to say: if Total Server Memory <Target Server Memory ,it Represents Memory Pressure .

     

    but I also  see a article say:”

    If the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access  to more physical memory.”

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

     that is to say: if Total Server Memory >= Target Server Memory ,it Represents Memory Pressure   

     

    So from those twomaterial:

    if Total Server Memory <Target Server Memory ,it Represents Memory Pressure.

     if Total Server Memor>=Target Server Memory ,it still Represents Memory Pressure.

     That made me crazy,

    So what's your opinion? thank you.




    • Edited by Jacky_shen Saturday, November 12, 2011 8:55 AM
    Saturday, November 12, 2011 8:51 AM

Answers

  • The first one is correct.  If Total Server Memory is less than Target Server Memory it can be a sign of memory pressure.  However, I very rarely rely on this as a way to look for memory pressure, there a plenty of other counters that give you a better clue when SQL is in memory contention.  

    Look at Page Life Expectancy, which should be well above the 300 number that most of the stuff online says.  This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes.  If you have 120GB of buffer pool and it is churning ever 5 minutes, that equates to 409.6 MB/sec sustained disk I/O for the system which is a lot of disk activity to have to sustain. 

    Look at Lazy Writes/sec which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process.  This should be near zero.

    Look at Free Pages/sec and Free List Stalls/sec.  You don't want to see Free Pages bottom out which will result in a Free List Stall while the buffer pool has to free pages for usage.

    Look at Memory Grants Pending which will tell you if you have processes waiting on workspace memory to execute.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Jacky_shen Saturday, November 12, 2011 3:59 PM
    Saturday, November 12, 2011 3:03 PM
    Moderator
  • In addition to Jonathan's response, be aware that SQL Server will acquire memory as needed.  If memory demands are small, then Total Server Memory will remain much lower than Target Server Memory.  Memory pressure is not indicated in this case.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Jacky_shen Saturday, November 12, 2011 4:00 PM
    Saturday, November 12, 2011 3:24 PM
    Moderator

All replies

  • The first one is correct.  If Total Server Memory is less than Target Server Memory it can be a sign of memory pressure.  However, I very rarely rely on this as a way to look for memory pressure, there a plenty of other counters that give you a better clue when SQL is in memory contention.  

    Look at Page Life Expectancy, which should be well above the 300 number that most of the stuff online says.  This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes.  If you have 120GB of buffer pool and it is churning ever 5 minutes, that equates to 409.6 MB/sec sustained disk I/O for the system which is a lot of disk activity to have to sustain. 

    Look at Lazy Writes/sec which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process.  This should be near zero.

    Look at Free Pages/sec and Free List Stalls/sec.  You don't want to see Free Pages bottom out which will result in a Free List Stall while the buffer pool has to free pages for usage.

    Look at Memory Grants Pending which will tell you if you have processes waiting on workspace memory to execute.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Jacky_shen Saturday, November 12, 2011 3:59 PM
    Saturday, November 12, 2011 3:03 PM
    Moderator
  • In addition to Jonathan's response, be aware that SQL Server will acquire memory as needed.  If memory demands are small, then Total Server Memory will remain much lower than Target Server Memory.  Memory pressure is not indicated in this case.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Jacky_shen Saturday, November 12, 2011 4:00 PM
    Saturday, November 12, 2011 3:24 PM
    Moderator
  • Thank you ,thank for both of you help me many times.

    Saturday, November 12, 2011 4:02 PM