SQL Server Performance

AWE Memory Allocation

Discussion in 'ALL SQL SERVER QUESTIONS' started by Naveed, Oct 4, 2012.

  1. Naveed New Member

    Dear Team,

    I have enabled AWE switch in SQL server 2005 and set minimum and maximum memory.
    Is there any standard for SQL server minimum and maximum memory requirement for SQL server and what is the effect if we does not set AWE.

    Minimum : 1024MB
    Maximum: 6400MB

    Total System Memory: 8192MB

    Regards
    Naveed Amir.
  2. Luis Martin Moderator

    Welcome to the forums!.
    What OS and SQL 2005 version do you have (x86 or x64)?
  3. Orlando Colamatteo New Member

    These are some general guidelines I pass along:

    http://sqlserverperformance.wordpre...-max-memory-settings-for-sql-server-20052008/

    The article talks about x64 but the settings are relevant for x86 instances as well which I assume you're on since enabling AWE does not apply to x64 instances.

    The article is just a guide though. The number you settle on will depend on what else runs on your server. The article assumes SQL Server is the only thing running on the server and that non-buffered memory (e.g. linked servers, clr, ssis, fulltext, erc.) is not a factor.

    I would recommend starting a little below what the article recommends then monitoring available memory on the server. I would look to keep at least .75GB free on a server at all times on a server with 8GB memory.
  4. Shehap MVP, MCTS, MCITP SQL Server

    Welcome to Forums,

    I can see we have 2 different talks ,

    · One is relevant to AWE which should be undertaken once you have 32 bit platform OS to expand memory limit to more than 4 GB memory.

    · And the 2nd one concerning Minimum and maximum memory limits which should be set to 0 GB for the minimum value ( Default value) and the maximum memory as total system memory limit with assigning a margin of 3 GB or 2 GB for OS services and other services such as antivirus , backup agent…etc like your case here, we can say 6 GB is good value as maximum value

    But you have to schedule memory recycling using the below T-SQL script by an appropriate frequency like daily , weekly ..etc according to your memory consumption

    DBCCFREESYSTEMCACHE('TokenAndPermUserStore')

    DBCCFREESESSIONCACHE

    EXECsys.sp_configureN'max server memory (MB)',N'2000'

    GO

    RECONFIGUREWITHOVERRIDE

    GO

    Waitfordelay'00:01:00'

    EXECsys.sp_configureN'max server memory (MB)',N'6000'

    GO

    RECONFIGUREWITHOVERRIDE

    GO

    Kindly let me know if any further help is needed
  5. Naveed New Member

    Thanks for your kind comment,

    Can we set MIN?MAX memory into x64bit and SQL server 2008 R2 Standard Edition. Is this really need for
  6. Shehap MVP, MCTS, MCITP SQL Server

    For Min setting , it doesn't matter to change as it is by default 0 MB but for Max setting it does worth much doing that to control memory utilization of SQL Server service even standard edition as no memory limitations of SQL Server standard similarly to Enterprise edition

    kindly let me know if any further help is needed
  7. Orlando Colamatteo New Member

    AWE can be useful on 32-bit systems with 4GB or less. AWE actually allows SQL Server to access more than the standard 2GB of memory reserved for applications on 32-bit systems and also locks pages in memory.

    The second bit of advice you offered to schedule memory recycling is not very good. The security token issue was addressed with SQL 2005 SP2: http://support.microsoft.com/kb/927396 and there is not nearly enough information posted to know whether it is a good idea to help resolve the original problem.

    PS there is no need to issue WITH OVERRIDE, which can allow invalid values to be set in the config table, it is recommended not to use it unless there is a specific reason and setting memory is not a good reason.
  8. Orlando Colamatteo New Member

    Min memory can be important on servers that host multiple instances so leaving at 0 may not be a good move, it depends on the system.
  9. Orlando Colamatteo New Member

    Yes, it is very important that you set max memory on 64 bit SQL Server. Please see the link I provided in my initial post for some guidelines. Monitor 'available memory' using Perfmon or the DMV sys.dm_os_performance_counters to ensure you are allowing some free space for Windows to operate and adjust max memory up or down as needed. Only worry about 'min memory' if you have multiple instances or have other processes running on the server competing with SQL Server for memory (hopefully not) and do not have 'lock pages in memory' enabled.

Share This Page