Professional Microsoft® SQL Server® 2008 Administration

12.5. Memory Considerations and Enhancements

Since memory is fast relative to disk I/O, using this system resource effectively can have a large impact on the system's overall ability to scale and perform well. Most 32-bit systems available on the market today support Intel's 32-bit Intel (IA-32) microprocessor PAE, or physical address extensions, technology. This technology enables the operating system and application to address an amount of physical memory up to 32GB for Enterprise Edition (EE) and 64GB for Datacenter Edition. With the release of Windows Server 2003 Service Pack 1 of x64, and IA64 platforms, more physical accessible memory is supported. The platform of choice is shifting from 32-bit to 64-bit, and the hardware aspects of all of these issues are discussed in Chapter 11.

SQL Server 2008 memory architecture and capabilities vary greatly from those of previous versions of SQL Servers. Changes include the ability to consume and release memory-based, internal-server conditions dynamically using the AWE mechanism. In SQL Server 2000, all memory allocations above 4GB were static. Additional memory enhancements include the introduction of hierarchical memory architecture to maximize data locality and improve scalability by removing a centralized memory manager. SQL Server 2008 has resource monitoring, dynamic management views (DMVs), and a common caching framework. All of these concepts are discussed throughout this chapter.

12.5.1. Tuning SQL Server Memory

The following ...