SQL Server Performance

Memory used

Discussion in 'General DBA Questions' started by ykchakri, May 10, 2004.

  1. ykchakri New Member

    After enabling AWE on my SQL Server (Enterprise Edition), the memory used by SQL Server is showing a very low value (around 70 KB), under Task Manager. Is this Normal ?
    The 'Total Server Memory' performance counter does show the right value (around 3GB).
  2. gaurav_bindlish New Member

    I guess you have to change the minimum and maximum memory to maximum value as SQL Server no longer dynamically ajusts its memory after this option is enabled.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. ykchakri New Member

    Yes Gaurav,

    I've fixed the Memory size used by SQL Server to 3072 MB (and I have 3743 MB total on the server). But my question is, Prior to enabling AWE, Task Manager used to show me around 2GB being used by SQL Server, but after enabling AWE, Task Manager shows only 70 KB being used by SQL Server. Is this normal ?
  4. gaurav_bindlish New Member

    I can't comment on this being normal or not. Let's wait for Satya and everybody else to post.

    Do you see any performance degradation?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  5. Luis Martin Moderator

    In my experince, Task Manager some times match, but not allways, with Performance Monitor Total Memory, don't know why. Others Monitors confirm Performance Monitor information.

    Similar discution in:
    Similar discution was in:
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1011





    Luis Martin
    Moderator
    SQL-Server-Performance.com

  6. ykchakri New Member

    quote:Originally posted by LuisMartin


    Similar discution in:
    Similar discution was in:
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1011





    Luis Martin
    Moderator
    SQL-Server-Performance.com



    Thanks a bunch Luis,

    This was exactly what I was looking for. So, it looks like the /PAE in my boot.ini causing this. As it is suggested in this link, Since I have only 4GB in my server, I better of with /3GB than /PAE and I hope task manager shows it correctly after this.

    I will try it tonight and let everyone know.
  7. satya Moderator

    Also I would suggest to capture PERFMON counters for MEMORY alongwith SQLServer Memory counters for assessment.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. ykchakri New Member

    I've replaced /PAE with /3GB and re-started the server. But, task manager still shows a low value (90 MB this time). So, I guess it is AWE that is causing this.

    Do I need AWE with 4GB memory on the server ?
  9. satya Moderator

    How about values from PERFORMANCE MONITOR, as I feel Task manager values are not completely accurate to return.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Luis Martin Moderator

    If Target Memory and Total Memory show good values, don't worry about TM.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  11. ykchakri New Member

    Yes, Like I said before, the 'Total server memory' perf counter is showing the right value.

    But, If I don't need to have AWE enabled with 4GB memory, I'll disable that so that TM also shows the right value. Can anyone confirm that I don't need AWE if I don't have more than 4GB memory ?
  12. Raulie New Member

    The reason you are getting low memory readings in TM is because SQL Server is hogging up all memory. Did you specify a value in Max Server Memory? If not this is what is causing TM to a show low memory reading. AWE enabled is only usefull if you want to access more than 4GB of RAM if you only have 4GB max leave this setting off. Remember if you are running multiple instances of SQL, AWE enabled won't share the the memory with the other instances.
  13. satya Moderator

    I come thru few basis about AWE:
    Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition can use up to 2 GB of physical memory. With the use of the AWE enable option, SQL Server can use up to 4 GB of physical memory.

    Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 4 GB of memory.

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;291988 on AWE information.

    My suggestion is if the current performance without AWE is fair then do not test and screw up the performance, leave as it is.... if ain't troubles then don't fix it.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. ykchakri New Member

    Satya,

    So, If I don't enable AWE, my Enterprise edition can't use more than 2GB of RAM. Which means I will be simply wasting the remaining 2GB on my server. And that was one of the reasons for me to upgrade to EE, to utilize more memory.

    If enabling AWE is the way to make SQL Server to use the 4GB available, then I'm up for it. And I don't see how the performance screws up by adding more memory.
  15. Raulie New Member

    What version of Windows are you running??? If you are running EE SQL Server on Windows 2000 Server than you dont need AWE enables. AWE Enabled is only good if you want to access more than 4GB but you must have Windows 2000 AdvancedServer or DataCenter editions to access more than 4GB.
  16. Raulie New Member

    oops, I cant leave NT out of this....if you are running on NT server the total max amount of memory is 2GB and for NT Enterprise it's 3GB I think. If you meet the specifications above EE and W2k server with 4GB than you dont need AWE enabled.
  17. ykchakri New Member

    quote:Originally posted by Lazy_DBA

    What version of Windows are you running??? If you are running EE SQL Server on Windows 2000 Server than you dont need AWE enables. AWE Enabled is only good if you want to access more than 4GB but you must have Windows 2000 AdvancedServer or DataCenter editions to access more than 4GB.
    Thanks, Lazy_DBA

    I'm running SQL Server EE on Windows 2000 Advanced Server, with 4GB RAM. I'm using the /3GB switch in Boot.INI. So, if I disable AWE, my SQL Server will be able to use all the available memory in the server and the Task Manager can show me the exact memory being used by SQLserver.exe, right ?

  18. Raulie New Member

    It should, in essence I have the same specs as you but running on W2k Server, I don't have AWE enabled and I just let SQL Server configure memory dynamically. I would reset all original settings PAE/AWE/Server Memory Tab settings, and let SQL handle the memory the best way it knows how, also take advantage of Advanced Servers memory capacities and go out and purchase more RAM since you know now that it can handle more than 4GB! Once this is done adjust AWE/PAE accordingly like Satya suggested. Good Luck!
  19. satya Moderator

    True, I would also suggest to test the performance with and without AWE enabled on the server which would give better information to dealwith.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  20. Raulie New Member

    Also let us know if this changed the behavior of Task Manager, I was still curious about that.

Share This Page