SQL Server Performance

SQL 2005 and AWE memory function.

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by Kenknuds, Sep 24, 2008.

  1. Kenknuds New Member

    Hi.

    I have the following single SQL 2005 server :

    A.OS= W3K std. edition w/SP2

    B. SQL 2005 server std.edition as follows:
    Microsoft SQL Server Management Studio 9.00.3042.00
    Microsoft Analysis Services Client Tools 2005.090.3042.00
    Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
    Microsoft MSXML 2.6 3.0 4.0 6.0
    Microsoft Internet Explorer 6.0.3790.3959
    Microsoft .NET Framework 2.0.50727.42
    Operating System 5.2.3790

    C. 12 small SQL DB instances with a memory consumption from min. 65MB to 1735 when monitored in Task manager.
    D. 4 GB physical memory.

    My main question is: is it a good idea to enable AWE memory function at this single SQL 2005 server ?

    Can I expect a big difference in how this single SQL 2005 server will behave regarding to the topic memory consumption?


    regards
    Ken

  2. SQL2000DBA New Member

    Welcome to the forum.Hi, I would recommend /3GB switch in boot.ini. As you know all 32-bit applications have a 4-gigabyte (GB) process address space (32-bit addresses can map a maximum of 4 GB of memory) and windows operating system provide application with access of 2GB only which is known as user mode virtual address space. In your case to allow SQL Server use maximum of 3GB of virtual address space (Reduce OS-Kernel Mode address space to 1GB) , you need to configure /3GB switch in boot.ini. AWE is recommended only in case you need to use physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems.
    I hope this help you.
  3. Kenknuds New Member

    Thanks a lot for your good answer.
    Regards
    Ken
  4. satya Moderator

    Worry about AWE settings if the server operating system is 32BIT and for 64 bit no need.
  5. DBADave New Member

    You may also want to utilize the USERVA option to make sure enough memory is available to the kernel.
    Windows XP and Windows Server 2003 introduced a new boot option, /USERVA, to be used in conjunction with /3GB that allows a finer degree of control than /3GB alone. You add /USERVA to your BOOT.INI just as you would /3GB. The advantage of /USERVA over /3GB alone is that it permits you to specify exactly how much address space to set aside for user mode access. For example, /USERVA=2560 configures 2.5GB for user mode space and leaves the remaining 1.5GB for the kernel. The caveats that apply to the /3GB switch alone apply here as well.
    http://msdn.microsoft.com/en-us/library/aa175282(SQL.80).aspx
    Dave
  6. Saurabh Srivastava New Member

    Is there any performance issue or have you observed memory pressure on system. If not, why to disturb the system with these advanced settings. It is not always true that enabling /3GB or userva swtich is going to increase your system performance unless you are expereincing virtual memory pressure.

Share This Page