SQL Server Performance

AWE Enable and 8 GB RAM

Discussion in 'Performance Tuning for Hardware Configurations' started by GuanWeiYen, Jan 21, 2004.

  1. GuanWeiYen New Member

    Hi:
    I encountered a problem when enable the AWE function.
    The server is W2K Advanced version/SQL2K EE with 7600 MB RAM.
    The boot.ini does has /3GB /PAE and 'AWE enabled' is 1 in SQL. The current 'max server memory' and 'min server memory' are 5678 MB. But when I
    sp_configure 'max server memory', 6146
    reconfigure
    go
    there is a warning message in SQL errorlog which describe
    'Warning: unable to allocate 'min server memory' of 6146'.
    I am most appreciated if someone can tell me what cause this problem and is there any negative impact to the server?
  2. ChrisFretwell New Member

    Have you done as was recommended in BOL for determining what the memory requirements of other apps are"
    From BOL

    "You can determine the amount of memory you can safely allocate to instances of SQL Server 2000 by identifying how much memory is available after all other applications to be used on the computer have been started.
    Use the SQL Server Performance Monitor Total Server Memory (KB) counter to determine how much memory is allocated by the instance of SQL Server running in AWE mode. Configure the max server memory option to leave some additional memory free to allow for the varying needs of other applications and Windows 2000. For more information, see Monitoring Memory Usage."

    Chris



  3. GuanWeiYen New Member

    Hi. Look like I might push the memory usage up to the limit or as biged123456 suggest that the OS may take 2GB disregard /3GB command. Here are some readings from performance monitor
    Memory - Available Bytes : 1,468,594,217
    Process - Working Set (sqlservr) : 9,024,297 (?)
    SQL Server - Buffer Manager - Total Page : 726,784
    SQL Server - Memory Manager - Total Server memory : 5,833,171

    From the 'Available Bytes' reading, that I still should have around 1400 MB free memory. Here are several questions that confuse me;
    1. BOL said 'Do not set min server memory and max server memory server configuration options to the same value, thereby fixing the amount of memory allocated to SQL'. However, after enable AWE and /3GB /PAE, the SQL can not dynamically allocates SQL memory? In other words, SQL memory will stick to 'max server memory' instead of navigating between 'min server memory' and 'max server memory'?
    2. Although there is 1400 MB free memory (from perf mon), is it possible that the OS still take 2 GB and left physically 400 MB avaiable for system? Or I just got a lucky day that there is no outstanding processes are using a lot of memory? How can tell the reason that cause the warning message 'Warning: unable to allocate 'min server memory' of 6146'?
    3. I reconfigure 'max server memory', but the SQL warning message is target on 'min server memory'. Is SQL server tried to allocate min server memory first and hit the cap of memory limit?

    Your help is highly appreciated.
  4. Luis Martin Moderator

    How about live SQL server use memory in dynamic way and look Performance Monitor again?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. biged123456 New Member

    It looks like somehow your min server memory get set to 6146 from the error message you mention. If your min is higher than your max (don't know for sure if this is possible to do), you would get an error that it cannot allocate that much. Really, since dynamic allocation isn't available with AWE, you may as well use SQL Enterprise Manager -> Server Properties -> Memory and set it to used a fixed size of X...
  6. GuanWeiYen New Member

    Thanks for the great help from Chris, Luis and biged123456. I think I will stick to fix memory usage as biged123456 suggested.
    It is also a very interesting issue that biges123456 has mentioned - 'how to detect OS is truely using 1 GB RAM after /3GB is setup in boot.ini'. Is anyone has information regarding this issue?
  7. gaurav_bindlish New Member

    As far as I remember, when using the AWE option, the OS needs 1 GN of memory for maintaining the address space. This is why on a systems even with 4 GB of ram, /3GB switch allows the maximum memory to go to 3 GB.

    About the min memory error, I think for AWE configuration, the min mem is equal to max memory and therefore may be some developer flashed a general message for changes in both min / max memory when the configured memory goes above what is allowed.

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

    Hi Guys,

    The /3GB option does not imply that the OS must use 1GB of RAM, it says that out of the possible 4GB that could be assigned to a process (since that is the maximum theoretical limit without AWE) the OS reserves 1GB of that virtual address space. Whether some of all of this virtual address space then is in RAM depends on other processes that might also be running. The 3GB option merely allows the process to use up to 3GB of its virtual address space

    Cheers
    Twan

Share This Page