Post reply

Performance Issues and Sql Server Memory

  • August 25, 2009 at 2:51 pm

    #138328

    I've looked at a few posts and I need some clarification please.

    You have the ability to set the SQL Server Memory and I have mine set

    MIN: 0

    MAX 3839

    and I have about 3.75GB of memory. Is is recommended that I decrease the MAX amount?

    If it is recommended, is there a percentage I should set the MAX to?

  • August 25, 2009 at 3:00 pm

    #1044544

    whats the SQL Server version ? 2000 or 2005, 32 bit or 64 bit ?

    as a general guideline with the given amount of memory i would leave SQL to manage things on its own.... but if you DO have to set the max and min values, i would leave some room for the OS.

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

  • August 25, 2009 at 3:03 pm

    #1044547

    Sql 2000, 32 bit.

    How much should I leave the OS?

  • August 25, 2009 at 3:21 pm

    #1044554

    leave 1gb

    ~BOT

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • August 25, 2009 at 3:29 pm

    #1044560

    JonJon (8/25/2009)

    Sql 2000, 32 bit.

    Is either /3GB or /PAE set in boot.ini? Is AWE enabled?

    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

  • August 25, 2009 at 3:32 pm

    #1044565

    JonJon (8/25/2009)

    I have about 3.75GB of memory.

    I dont think PAE and AWE need to be enabled ... or What am i thinking :doze:

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

  • August 25, 2009 at 3:53 pm

    #1044578

    Amit Singh (8/25/2009)

    JonJon (8/25/2009)

    I have about 3.75GB of memory.

    I dont think PAE and AWE need to be enabled ... or What am i thinking :doze:

    If neither of them is enabled, SQL can only use 2GB of memory as that's the max memory allowed for a process on 32 bit.

    In this situation, I would recommend /3GB, not setting /PAE, not using AWE and setting the max memory to 3GB.

    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

  • August 25, 2009 at 5:41 pm

    #1044608

    how do I set /3GB and where do I check AWE?

    Thanks

  • August 26, 2009 at 1:22 am

    #1044724

    /PAE and /3GB in boot.ini, AWE is a SQL setting, check sp_configure.

    In your situation, I would suggest /3GB to be in boot.ini, /PAE not to be there and AWE to be disabled.

    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

  • August 26, 2009 at 1:27 am

    #1044726

    Edit: double post

    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

  • August 26, 2009 at 2:50 pm

    #1045187

    How do I enter the /3GB into the boot.ini file?

    do I just add /3GB

    [boot loader]

    timeout=30

    default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS

    [operating systems]

    multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut /3GB

  • August 26, 2009 at 3:14 pm

    #1045195

    ok I think I found on how to set it up...

    Thanks for all the help.

  • August 27, 2009 at 7:00 am

    #1045485

    1) I didn't notice anyone else asking what else is running on the server. If this is not a dedicated SQL Server box beware using /3GB.

    2) If you do use /3GB you still need to set max memory to 3GB or a tad less. If you don't use /3GB, set max memory to a bit under 2GB, maybe 1.5-1.7. Need to leave room for non-buffer pool memory usage.

    3) Have you been monitoring for memory pressure?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 27, 2009 at 7:19 am

    #1045510

    1) Its not a dedicated Sql Box. I have a reporting solution running on the box.

    3) I have been monitoring the memory pressure, and that is how I noticed why SQL wasnt using more of the RAM.

    I guess I can test out the /3GB switch and monitor the box. Worst case scenario I take the switch out.

    Thanks for the heads up.

  • August 27, 2009 at 8:33 am

    #1045576

    If you have other stuff running you likely won't be happy with /3gb.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 18 total)

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