"Total Server Memory" & "Target Server Memory" are very low

  • Dear all,

    My server's configuration :

    Windows 2003 Enterprise Edition 5.2 Service Pack 2 3790.srv03_sp2_gdr.070304-2240

    Physical memory : 3,407,160 KB

    SQL 2005 9.0.3042

    There were no difference between "Total Server Memory" & "Target Server Memory"

    However, I found both of its "Total Server Memory" & "Target Server Memory" are far below the physical memory : 214960 KB - 605368 KB.

    How can we increase these 2 values ?

    Do I need to modify the boot.ini file by adding : /3GB ?

    Or sp_configure ‘awe enabled’, 1 ?

    Please give me advices. Thanks.

  • what is the output of below query?

    sp_configure 'max server memory'

    go

    sp_configure 'Min server memory'

    go

  • We use default value :

    min. 0 MB, max : 2147483647 MB

  • Are you saying that SQL is used heavily and still SQL is not using the memory?

  • Exactly.

    In fact, its Total & Target Server Memory are far below its Physical Memory, and high in Paging File(Total) % Usage : around 28 %, high PhysicalDisk (Total) % Disk Time and high PhysicalDisk (Total) Avg. Disk Queue Length.

    I think it might be due to : low memory usage, thus, the server will use page file very frequently, so, it got a high Paging File % and high Physical Disk % Disk time and disk queue length.

    So, I want to know how to increase its Target and Total Memory usage.

  • right now, the SQL is set to use dynamic memory. SQL will use memory when it feels it should be used.

    Try AWE enabled to 1 and set min and max server memory to around 2 GB.

  • Hi, at first I just thought that perhaps SQL simply wasn't needing to use more memory. Its ok for target/ total to be less than physical RAM.

    Then you mentioned the paging. Are you certain it is SQL server that is causing the paging?

    What are other counters saying like "page life expectancy" and "buffer cache hit ratio?" If these numbers look normal ~ +300 and +90, respectively, you may not have any problems from a SQL server perspective. Basically, what does the big picture say? Target=Total < Physical in itself isn't a bad thing...

    Anyhow, I'm sure you have resolved the situation by now. So, I would like to hear what became of this!

  • onlo (7/2/2008)


    There were no difference between "Total Server Memory" & "Target Server Memory"

    However, I found both of its "Total Server Memory" & "Target Server Memory" are far below the physical memory : 214960 KB - 605368 KB.

    How can we increase these 2 values ?

    Do I need to modify the boot.ini file by adding : /3GB ?

    Or sp_configure ‘awe enabled’, 1 ?

    Please give me advices. Thanks.

    Total memory should always ideally be less than target memory.

    you dont need AWE enabled with under 4GB RAM, so leave that alone.

    It may be worth using the /3GB switch in the boot.ini (you could certainly try it).

    As already mentioned what do the other counters show?

    Balmukund Lakhani (7/2/2008)


    set min and max server memory to around 2 GB.

    always ensure that min memory is less than max memory never the same or higher

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 9 posts - 1 through 8 (of 8 total)

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