Jun 092008
 

In a previous post, I described what AWE memory is, and how it works with SQL Server 2005, against both Windows 2000 Server and Windows 2003 Server.  In this post, I’ll describe how it works in SQL Server 2000.

Firstly, run

SELECT @@version

to check what version of SQL Server you are running.  This will give you a version number, and the edition. 

The first thing to be aware of is that SQL Server 2000 will only use more than 2 GB of memory with Enterprise (or Developer) Edition.  This is the main limitation of SQL Server 2000 Standard, and if more than 2 GB of memory is required, you must upgrade to SQL Server 2000 Enterprise, or SQL Server 2005 Standard.

If you are running 8.00.2039 (Service Pack 4), you will also need to upgrade to 8.00.2040 to avoid a bug where only 50% of the available memory is available for use.  See http://support.microsoft.com/kb/899761/ for more information.

Enabling AWE on SQL Server 2000 is the same as SQL Server 2005:

  1. Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
  2. Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
  3. Set the configuration setting “AWE Enabled” to 1.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Note that AWE requires 2 GB of kernel VAS to address AWE memory above 16 GB, so using the /3gb switch in boot.ini will limit AWE to 16 GB.  See http://support.microsoft.com/kb/274750 for more information about the maximum memory useable for each operating system/SQL Server 2000 combination.

SQL Server 2000 will allocate all memory upon startup, to the value of the “Max Server Memory” configuration option.  If there is not enough memory, SQL Server will take as much as it can, leaving as little as 128 MB available.  This memory comes from the Windows non-pageable pool, and so this memory will never be swapped to disk.

If the server has less than 3 GB of memory available when SQL Server starts, AWE will not be available, and SQL Server will run in non-AWE mode.

It is always recommended to set a value for “Max Server Memory” to ensure that other processes running on the server have some memory available.  Leaving ~2 GB is a good setting, for under 32 GB total server memory, ~4 GB if 64 GB total server memory.  Additionally, when multiple instances of SQL Server are running on the one machine, “Max Server Memory” is necessary to prevent one instance from using too much memory and starving the other.  If “Max Server Memory” has not been set, the first instance of SQL Server 2000 starting will allocate all bar 128 MB, and leave no memory for the second instance.

  2 Responses to “SQL Server 2000 and AWE Memory”

  1. So…we’ve been having some performance issues with our SQL Server 2005 box – it’s running an SAP ERP solution and certain processes take a long, LONG time (upwards of five minutes) to execute. The box is a Windows Server 2003 Standard Edition machine with 4 GB of RAM (we only have about 30 employees, only half of which are ever using the ERP system at any time). We’ve been running performance logs and whatnot to see which queries are causing the problems…the big problem is a bunch of queries running against a table with 350,000 lines – the queries are not optimized correctly and as far as I’m able to determine the execution plan is underestimating the number of lines it needs to pull…creating a bunch of page faults, excessive disk reads, etc.

    So our IT guy (who’s not a SQL expert) noticed that turning on AWE created a temporary increase in performance when some of these slowdowns occur. Which – OK, we have only 4GB of RAM, so it’s obviously not enabling access to more than 4GB, like AWE is designed to do. Since AWE turns off some of SQL Server’s dynamic memory addressing, I’m sure that at some point it’s actually hurting our performance – because the performance problems return later, even with AWE switched on.

    I figure that the AWE switch must be doing something ELSE when it’s switched off / on to the way the system operates – clearing the buffer cache, or something, to clear up some of the bottlenecks. My concern would be that it’s clearing the ENTIRE buffer cache, including uncommitted transactions or data that a checkpoint hasn’t written to disk yet…regardless, it’s clear that he doesn’t have a firm grasp on what the AWE switch is doing to reconfigure memory when it’s turned on in the middle of business operations and users being logged in, etc. (something that, at the very least, should be tested before being rolled out).

    So – what does the AWE switch DO, exactly, to the buffer cache and other running processes when it’s turned on in medias res? I can’t find any information about this anywhere.

  2. Hi,

    Not exactly sure how enabling AWE is assisting you. Usually the memory above 4 Gb is entirely allocated as Buffer Cache. This can cover up I/O problems on the Server as the page read rate drops as more of the pages are in the buffer cache.

    Poor performance is usually caused by
    1. SQL running that does a tablescan rather than using an index – check the execution plan s for the most commonly running SQL that an index is being used to access the table.
    2, Statistics are not up to date or are inaccurate – consider switching autoupdate of statistics off and running a maintenance job that does it for each table in the application. If the tables are very big then consider an update of statitics with fullscan.
    3. Are the Indexes badly fragmented or don’t match the configured fill factors – Run a dbcc dbreindex on the affected tables
    4. Do you have excessive OS fragmentation on the tablespaces? Consider running a defrag of the disk
    5. You could also experiment with the Degree of Parallelism and see if it improves the execution times – This can help if the DB Server CPU goes through the roof

    Regards

    Mike

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>