SQL server 2005 AWE

  • benn.jacobs

    SSC Enthusiast

    Points: 189

    Dear all,

    I have some issue and I’ll be happy to hear some ideas.

    1. I have 12GB RAM in my windows 2003 SBS server (Dell power edge 2900)

    2. My SQL server is SQL server 2005 Enterprise.

    3. I am using large DBs

    4. In my Boot.ini file I put the /PAE switch.

    When I leave the AWE options (in SQL server) unchecked I see in the task manager that “sqlserver” process allocate 1.7GB (never more than that-even if I set maximum memory 8GB in SQL properties)

    Now,

    when I checked the AWE options I see in the task manager that “sqlserver” process allocate ONLY 70MB RAM!!!

    Any idea?

    What is the recommend setting to set in Boot.ini and in SQL server (AWE) for 12GB RAM?

    Sincerely

    Benn.jacobs@gmail.com

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    Dear all,

    I have some issue and I’ll be happy to hear some ideas.

    1. I have 12GB RAM in my windows 2003 SBS server (Dell power edge 2900)

    2. My SQL server is SQL server 2005 Enterprise.

    3. I am using large DBs

    4. In my Boot.ini file I put the /PAE switch.

    When I leave the AWE options (in SQL server) unchecked I see in the task manager that “sqlserver” process allocate 1.7GB (never more than that-even if I set maximum memory 8GB in SQL properties)

    Now,

    when I checked the AWE options I see in the task manager that “sqlserver” process allocate ONLY 70MB RAM!!!

    Any idea?

    What is the recommend setting to set in Boot.ini and in SQL server (AWE) for 12GB RAM?

    Hi,

    Check the following.This ll help for u.

    AWE only supported OS-32-bit not for 64-bit.

    1.SP_CONFIGURE 'SHOW ADVANCED', 1

    RECONFIGURE

    SP_CONFIGURE 'AWE ENABLED', 1

    RECONFIGURE

    EXEC SP_CONFIGURE N'MIN SERVER MEMORY (MB)', N'min'

    RECONFIGURE WITH OVERRIDE

    EXEC SP_CONFIGURE N'MAX SERVER MEMORY (MB)', N'max'

    RECONFIGURE WITH OVERRIDE

    SP_CONFIGURE 'SHOW ADVANCED', 0

    RECONFIGURE

    2.Before enable AWE u must add the sql server account permission Lock pages in memory.

    (Its only for Enterprise edition )

    3.If ur memory has configured hot-add memory u no need to setup /PAE in boot.ini

    4.Once done the 3-steps U must restart the sql-Box.

    Also verify the following

    1.Check the sqlerrorlog

    sp_readerrorlog

    Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

    2.Check the memory

    SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

    Edit:To remove the deprecated object.

    Muthukkumaran KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • benn.jacobs

    SSC Enthusiast

    Points: 189

    Hi Muthu,

    Many thanks for your help.

    I did what you advise me, and its work. however the "Target Server Memory" and "Total Server Memory" never going up more then 3.6GB even if it set to 5GB

    any Idea?

    thanks

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    Hi Muthu,

    Many thanks for your help.

    I did what you advise me, and its work. however the "Target Server Memory" and "Total Server Memory" never going up more then 3.6GB even if it set to 5GB

    any Idea?

    thanks

    Hi,

    Its depends upon urs App.How big ur Database?

    Also Check the following Counters in perfmon.msc and replay.

    1.Memory: Pages/sec

    Average between 0 and 20

    2.Memory: Available Bytes >

    Less than 20 to 25 percent of installed RAM is an indication of insufficient memory

    3.SQL Server: Buffer Manager: Buffer cache hit ratio >90

    4.SQL Server: Buffer Manager: Page Life Expectancy >300

    Muthukkumaran KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • Paul White

    SSC Guru

    Points: 150341

    Hey Benn,

    Windows 2003 Small Business Edition can use up to a maximum of 4GB RAM - see http://msdn.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003

    SQL Server Enterprise with 12GB RAM would need Windows 2003 Enterprise or above.

    If you were running a 64 bit edition of SQL Server, any 64 bit OS would do.

    So, it is the OS that is limiting the memory usage - not SQL Server.

    You should have a stern chat with your infrastructure people for installing SQL Enterprise on a 12GB RAM box under a 32-bit OS limited to 4GB!

    Cheers,

    Paul

  • benn.jacobs

    SSC Enthusiast

    Points: 189

    Hi all,

    Many thanks for your effort to help!!!

    Yes you right, the problem is that my Windows SBS cant see more then 4GB RAM, I reinstall windows 2003 enterprise X32 with SQL 2005 and I am using AWE.

    Its work fine now.

    again thanks a lots

    Ben

  • EdVassie

    SSC Guru

    Points: 60154

    It is not just Windows SBS that cannot see more than 4 GB RAM. All 32-bit editions of Windows are not able to address more than 4 GB RAM, so you will never see anything using more than this in Task Manager.

    One of the functions of PAE allows Windows to store data in memory above the 4GB line, but it has to be copied back below the 4GB line for an application to read or update it. Applications that are aware of PAE facilities can use the memory above the 4GB line as a cache.

    SQL Server has the AWE option which allows it to take advantage of PAE. SQL only ever stores database bufferpool data above the 4GB line. If you want to see what use SQL Server is making of AWE memory then you have to look at the relevant Perfmon counters - BOL has more details. You will never see how AWE memory is being used in Task Manager.

    Original author: SQL Server FineBuild[/url] 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara

  • Paul White

    SSC Guru

    Points: 150341

    If you have 3.5GB or more in your server (or you would like it) you need a very good reason not to move to a 64-bit platform.

    Paul

Viewing 8 posts - 1 through 8 (of 8 total)

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