Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    44

    Unanswered: SQL 2000 - AWE and "user application space"

    All,

    Question about what constitutes "user application space" with regard to enabling AWE, and telling SQL server what MAX memory amount to use.

    The doc says to leave room for user application space. In other words, don't tell SQL server to use the actual amount of total memory available on the machine.

    But what is "user application space"? I have users to login to the SQL server via query analyzer and/or toad. Are sessions like these treated under the umbrella of "user application space"?

    Also, we have a 3rd party product (Legato) that we use for backups. Do the backups fall under the "user application space" area too?

    We're in a situation right now where the DBA's set the MAX memory to be ALL of the 32 gigs availabile on the server.

    How much memory should be set aside for user applications? We're running Windows server 2003 - 32 bit.

    Thanks,

    Isaac

  2. #2
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    While enabling AWE ,Keep these points in mind, you need to put an entry in boot.ini file /3gb /pae and lock page in memory settings has to provided for SQL server startup acccount . Legato backup will mostly use SQL server memory allocated , U can allocate 24 GB to SQL and remaining will be taken over by OS . If u set maximum SQL server memory to 24GB it does not mean it always use the whole allocated , it will utilize till 24 GB based on requirement , If the demand of OS (operating system) is more , it can use beyond 8GB of memory allocated and demand increasing it will make use of memory available , Its a trade-off between SQL and OS ( load balancing technique)

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't remember ever having used the /3Gb and the /PAE switches at the same time on a server. I don't think that I'd ever user /3GB on a machine with 32 Gb of RAM due to the AWE performance hit it can cause.

    There are multiple tuning issue in this scenario. Windows itself and other windows applications (such as Legato) do need RAM to run and if SQL Server monopolizes the RAM, then there will be no memory for anything else to run.

    More importantly, AWE does "page switching" where it takes a large amount of memory and logically "carves" it into pieces that can fit in a given address space. AWE uses some memory and some CPU in order to keep this memory "shell game" working. I normally find that there is a "performance knee" at around 12 Gb where adding more memory to SQL Server actually slows SQL performance down instead of speeding it up. I can't imagine a 32 bit SQL Server being able to effectively use more than half of a 32 Gb machine and I'd expect it to perform best when given between 10 and 12 Gb.

    If you want to effectively use the hardware you've got, I think that you need to switch both Windows and SQL Server to x64 versions to use 64 bit.

    -PatP

  4. #4
    Join Date
    Dec 2003
    Posts
    44
    Thanks for the info.

    Does anyone know what falls under the umbrella of "user application space"?

    Isaac

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I thought it was Virtual Application Space....

    Sorry fella, I'm flying through and haven't read the thread proper, but this is my #1 SQL Server memory article:
    Sql Stuff : PAE and /3GB and AWE oh my...
    Mebbe that covers it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have a link to the document you are consulting?

  7. #7
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    Small correction if total physical memory available is 8GB then /3GB switch
    If physical memory > 6 GB - /PAE switch must be used

  8. #8
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    please read as this physical memory > 8 GB - /PAE switch must be used

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Rajganesh
    Small correction if total physical memory available is 8GB then /3GB switch
    If physical memory > 6 GB - /PAE switch must be used
    /3GB is only used to increase process memory for user applications that are normally limited by 2GB of address space. This switch has nothing to do with PAE and AWE. If enabled, - consider light pooling setting in SQL, but dcisions like this should not be made without hard-core evidence on their positive affect.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •