Post reply

How to Set Minimum and Maximum memory in AWE SQL2005

  • October 9, 2012 at 4:16 am

    #145715

    Dear All,

    I have 16 GB server physical ram windows 2003 EE 32 Bit.

    I want to enable AWE with :-

    Minimum 7000 MB means 7 Gb

    Maximum 11000 MB means 11 GB

    but when i run. dbcc memorystatus

    it shows me old values.

    Please help me in this regard. I will be obliged.

    Please find attached screenshot.

    Thanks

    Malik Adeel Imtiaz

  • October 9, 2012 at 5:25 am

    #1547163

    Setting AWE on requires a service restart to take effect.

    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

  • October 9, 2012 at 5:42 am

    #1547169

    I already restarted the services and complete server restart .

    But still no results.

  • October 9, 2012 at 5:56 am

    #1547172

    Is the PAE switch present in boot.ini? (or is it a 64-bit OS?)

    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

  • October 9, 2012 at 6:11 am

    #1547183

    Windows 2003 32 Bit Enterprise Edition.

    PAE switch is enabled.

  • October 9, 2012 at 6:26 am

    #1547191

    I have resolved the problem.....

    I have added /3g switch with / PAE and restart the server. and problem has been resolved.

    Thank you so much for your support.

    Thanks

    Malik Adeel Imtiaz

  • October 9, 2012 at 10:58 pm

    #1547448

    Dear Gila Monster,

    Thank you so much for your great support and time.

    Thanks

    Malik Adeel Imtiaz

  • October 10, 2012 at 1:05 am

    #1547484

    If you have 16 GB of memory, you do not want the /3GB switch enabled, it can be harmful. I suggest you remove that switch. You just need the /PAE and AWE enabled within SQL Server.

    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

  • October 10, 2012 at 1:43 am

    #1547503

    Actually first we have tried with out /3g switch and without /3g switch that we have noticed that AWE memory did not cross 2 GB memory.

    After that we have enabled /3g switch and AWE memory crosses 5 GB.

    Please see attachment.

  • October 10, 2012 at 2:22 am

    #1547515

    Again, you do not need /3gb for memory usage above 4GB. That's the /PAE switch and the AWE settings alone. With 16 GB memory, /3GB can be harmful as it reduces the number of page table entries you have and can result in memory issues for the OS.

    Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    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

  • October 10, 2012 at 2:58 am

    #1547528

    Quit right Let me check in the testing environment.

    One thing more. Now i am in another situation.

    In Production i have set 11 GB to MAXIMUM and 7 Gb to MINIMUM as mentioned in my earlier replies

    and when i ran my queries (Heavy load queries).

    it touches to 11 GB and after that with out completion's of query memory drop down to 2 to 3 GB. And my query stuck because

    1) Memory drops

    2) Query required memory and there is no memory.

    Why this is happening.

    Thanks

    Malik Adeel Imtiaz

  • October 10, 2012 at 3:05 am

    #1547531

    SQl doesn't drop it's memory usage unless restarted or the server is under memory pressure or max server memory is reconfigured to a low value. Please read chapter 4 of that book.

    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

Viewing 12 posts - 1 through 11 (of 11 total)

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