Using AWE Memory in SQL Server 2000
Microsoft considers AWE a premium feature, and because of this, it is not offered in all versions of Windows 2000 and SQL Server 2000. Below is a chart showing the various versions of Windows 2000 and SQL Server 2000 that support AWE memory.
|Windows Version||SQL Server 2000 Enterprise Edition||SQL Server 2000 Standard Edition||SQL Server 2000 Personal Edition||SQL Server 2000 Developer’s Edition|
|Windows 2000 Datacenter Server||64GB||2GB||2GB||64GB|
|Windows 2000 Advanced Server||8GB||2GB||2GB||8GB|
|Windows 2000 Server||4GB||2GB||2GB||4GB|
|Windows 2000 Professional||N.A.||N.A.||2GB||2GB|
Any combination above of 4GB or more is supported via AWE memory. So if you want to take advantage of AWE memory, you have no choice to purchase Microsoft’s premium-priced software.
If you use AWE memory, you can track its performance characteristics using some special Performance Monitor counters found under the SQL Server Buffer Manager object. They include:
AWE Lookup Maps/Sec: This counter measures how many times that a specific database page was requested by SQL Server, was found in the buffer pool, and then was mapped as AWE memory (or the server’s virtual address space). This would be a combination of the AWE Stolen Maps/Sec and AWE Write Maps/Sec described later.
AWE Stolen Maps/Sec: This counter measures how many times that a free database buffer was taken by SQL Server and mapped as AWE memory.
AWE Write Maps/Sec: When SQL Server runs out of free buffers to map to AWE memory, it has to write to a dirty buffer instead, which hurts performance because a disk write has to occur to clean up the dirty buffer before it can be used. This counter measures the number of times that SQL Server has to map a dirty buffer. If this figure is high, more memory should be considered.
AWE Unmap Call/Sec: Sometimes SQL Server will unmap buffers from AWE memory (because they have not been used lately). This counter measures how many times SQL Server calls for an unmap operation, which can affect one or more buffers at the same time.
AWE Unmap Pages/Sec: Closely related to the above counter, this counter specifically measures the number of SQL Server buffers that are unmapped.