SQL Server Performance
  1. travistan New Member

    Hi ,

    I have a server installed with

    1. Win 2003 R2 32 bit
    2. Sql Server 2005 32 bit
    3. 4 Processor 2800 Mhz
    4. 8 GB of RAM.

    1. What is the setting I should go for it base on these spec ?
    2. This server is use for Datawarehouse purpose. ETL will run on mid night and
    user need to query the DB on office hour

    Knowing the Sql Server 2005 have AWE setting which allow to play around with memory.

    If I set min memory = 1 and max memory = 6 and enable AWE. Do this means it reserve 6 GB of memory to Sql Server and 2 GB for windows ?

    Thanks ,
  2. satya Moderator

    In order to enable memory for other application and also in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server. On 32-bit editions of Windows, applications have 4 gb of virtual address space available. The virtual address space is divided so that 2-GB is available to the application and the other 2-GB is available only to the system.

    Configuring max server memory alone does not make SQL Server allocate all the memory at startup—not under SQL Server 2005. But, depending on the real load, the configured amount can be consumed very quickly. Using SQL Server 2005 under Windows Server 2003 (editions that support AWE), you get dynamic memory management even if you switch on AWE support. On almost all other configurations, SQL Server switches dynamic memory management off as soon as you switch AWE on.

    Following links shoudl give more on the subject:
    http://www.sql-server-performance.com/sql_server_configuration_settings.asp
    http://msdn2.microsoft.com/en-us/library/ms190673.aspx
    http://msdn2.microsoft.com/en-us/library/ms190731.aspx
    http://blogs.msdn.com/slavao/archiv...ted-reserved-in-dbcc-memorystatus-output.aspx
    http://blogs.msdn.com/slavao/archive/category/9005.aspx

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. joechang New Member

    for DW apps,
    you really want full 64-bit, OS & SQL
    since you are SQL 2005, there is no impediment
    start with W2K3 64-bit & 32-bit SQL if you are conservative (chicken)
    then install 64-bit SQL side-by-side to convince yourself it is ok

    there have been some 64-bit issues, most of which have been corrected

    the reason you want 64-bit for DW is that large queries aggregating values will generate hash and sort operations, for which you want a large VAS,
    the 32-bit AWE trick is only useful for OLTP app
  4. travistan New Member

    Hi Satya ,

    1. Means that if I did not enable AWE , the server will only use up 4GB of RAM
    although the server have 8 GB

    2. From the article , we should add the "Lock pages in memory" before enable AWE which
    prevent the memory to write into virtual memory.

    3. My intention is just to make sure Sql Server 2005 able to fully utilize all the
    memory on the server. Please advised.

    Thanks ,
    Travis
  5. satya Moderator

    True, ensure to add that option before enabling the AWE and reboot may be required.
    You can easily manage the DW with the above specified hardware, provided you have a predicted growth for next 5 years and be assured to handle the sizes with this hardware.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. dtipton New Member

    What about the /3GB switch?

    This would allow SQL Server to use a 3GB address space and additional memory above the 4GB bar for data buffers when AWE is enabled.

    I agree on the 64 bit recommendation.
  7. satya Moderator

    If you are using SQL Server 2005 to configure memory to use more than 2 GB of physical, see the following topics in SQL Server 2005 Books Online:• Memory Architecture
    • Server Memory Options
    • Using AWE
    • Enabling Memory Support for Over 4 Gb of Physical Memory
    • Enabling AWE Memory for SQL Server


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. WingSzeto Member

    Hi,

    If the OS is windows 2003 standard, even with the AWE enabled and even with the max server memory set to 8 GB, SQL 2k5 can only use up to 4 Gbytes, correct?

    I am still not sure when to use /3GB switch. Our server has 6 Gbytes of memory and again we are using W2k3 standard with SQL 2k5 sp2. It is a dedicated SQL server with no other applications running. If I want OS use 1 Gb and SQL use 5 GB, is it doable under W2k3 standard?




    quote:Originally posted by satya

    If you are using SQL Server 2005 to configure memory to use more than 2 GB of physical, see the following topics in SQL Server 2005 Books Online:• Memory Architecture
    • Server Memory Options
    • Using AWE
    • Enabling Memory Support for Over 4 Gb of Physical Memory
    • Enabling AWE Memory for SQL Server


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. satya Moderator

  10. WingSzeto Member

    I got some idea from that link. I assume your 'yes' is for my first question.

    Just in case, please give me an answer on each question below.

    1. Even our server has 6 Gb of RAM, the W2k3 standard can only see 4 GB so no matter if I turn on /PAE and /3GB and enable AWE in SQL 2k5, SQL 2K5 can only use 3 GB, right?

    2. With 6 Gbyte of RAM, W2k3 and SQL 2k5, what switches do I really need to use the most RAM that SQL 2k5 can utilize?




    quote:Originally posted by satya

    Yes as per KBAhttp://support.microsoft.com/kb/889654

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page