locked
Memory Manager DMV RRS feed

  • Question

  • In SQL Server 2005 is there a DMV that I can use to report on the Performance Monitor counters called,

    Memory Manager :Target Server Memory

    Memory Manager :Total Server Memory

    These objects are available with Performance Monitor but are they available with a DMV?

    Thursday, December 30, 2010 1:02 AM

Answers

  • Page Life Expectancy of 60 minutes is good number to have on a production box. That means the data cache (buffer cache) is being flushed for about 60 minutes.

    When SQL Serevr re-starts, SQL Server will use very little memory. As it processes new queries, SQL Server will bring the data pages from disk to cache and it slowly uses up the RAM. This is called the RAMP UP period. Once, it uses all of the memory allocated to it (using max server memory), it will NOT release the memory back to the OS unless OS trims the working set of SQL Server. Tracking Page file usage is also important here. There are many aspects here with 32 bit vs 64 bit architecture also.

    Jonathan wrote on this topic extensively and it may help in your understanding.

     

    I would also recommend Troubleshooting performance problems in SQL Server whitepaper.

     

    http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Memory+Allocation/default.aspx
    http://SankarReddy.com/
    • Proposed as answer by Tom Phillips Thursday, December 30, 2010 3:00 PM
    • Marked as answer by FogHorn I Thursday, December 30, 2010 11:47 PM
    Thursday, December 30, 2010 7:43 AM

All replies

  • select * from sys.dm_os_performance_counters where counter_name like '%Target Server Memory%'
    select * from sys.dm_os_performance_counters where counter_name like '%Total Server Memory%'
    

    http://SankarReddy.com/
    Thursday, December 30, 2010 2:56 AM
  • Thank you Sankar.

    If I have allocated 10GB to the SQL Server and both counters mentioned above display 10GB, In other words it is willing to consume 10GB and it is currently consuming 10GB however, the pagelifeexpectancy is about 60 minutes,

    is this how SQL Server uses RAM? in other words if you have specified 10GB as the MAXServer Memory the above counters will never change because SQL Server will naturally grab all 10GB?

    Thursday, December 30, 2010 4:39 AM
  • Page Life Expectancy of 60 minutes is good number to have on a production box. That means the data cache (buffer cache) is being flushed for about 60 minutes.

    When SQL Serevr re-starts, SQL Server will use very little memory. As it processes new queries, SQL Server will bring the data pages from disk to cache and it slowly uses up the RAM. This is called the RAMP UP period. Once, it uses all of the memory allocated to it (using max server memory), it will NOT release the memory back to the OS unless OS trims the working set of SQL Server. Tracking Page file usage is also important here. There are many aspects here with 32 bit vs 64 bit architecture also.

    Jonathan wrote on this topic extensively and it may help in your understanding.

     

    I would also recommend Troubleshooting performance problems in SQL Server whitepaper.

     

    http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Memory+Allocation/default.aspx
    http://SankarReddy.com/
    • Proposed as answer by Tom Phillips Thursday, December 30, 2010 3:00 PM
    • Marked as answer by FogHorn I Thursday, December 30, 2010 11:47 PM
    Thursday, December 30, 2010 7:43 AM