Setting memory

  • How would I know whether I need to enable AWE for my SQL Server sandard edition

    Server RAM 8GB

    Server properties-memory settings

    Minimum server momery 0MB

    Maximum server memory 2147483647 MB

    OTHER MEMORY OPTIONS

    Index creation memory 0KB

    memory per query 1024 KB

    Please let me know if you need further details to answer to my question. Thanks!

  • 32 bit or 64 bit?

    If you're not sure, run this and post the output

    SELECT @@version

    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
  • GilaMonster (12/21/2009)


    32 bit or 64 bit?

    If you're not sure, run this and post the output

    SELECT @@version

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  • You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

    If you need to add the switches, you need to reboot. Then, just tick the AWE box. Cannot remember if you need to stop and restart the SQL Server service but perfmon will tell you if this is working.

    http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/

    http://msdn.microsoft.com/en-us/library/ms190730.aspx

  • Mark_Pratt (12/21/2009)


    You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

    If you need to add the switches, you need to reboot. Then, just tick the AWE box. Cannot remember if you need to stop and restart the SQL Server service but perfmon will tell you if this is working.

    http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/

    http://msdn.microsoft.com/en-us/library/ms190730.aspx%5B/quote%5D

    If I understood correct..

    I need to enable the lock pages in memry option, then check AWE box in server memory settings

    please correct me if Iam wrong

  • Nevermind, clicked wrong link.

  • Mark_Pratt (12/21/2009)


    You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

    /PAE yes, /3GB no, not on 8GB of memory.

    Using /3GB limits how much memory the kernel can address. As total amount of memory goes up, the kernel needs more memory to manage it. Use /3GB together with /PAE and there's a risk of 'starving' the OS of memory. Not fun.

    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
  • If you do use AWE, you need to set SQL's max memory to ensure it doesn't use too much and hurt the OS. Set max to 6.5GB at most.

    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
  • So in my case shall I enable AWE ?

  • kareem007 (12/21/2009)


    So in my case shall I enable AWE ?

    If you want SQL Server to be able to access more than 2GB of memory, then yes - you need to enable AWE.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    If I understood correct..

    I need to enable the lock pages in memry option, then check AWE box in server memory settings

    In Std. edition SP3+CU-4 only support lock pages in memory option

    http://www.sqlservercentral.com/Forums/Topic803211-525-1.aspx

    AWE Disscussion

    Edit:Post the URL

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

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

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