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 allows the operating system or application to address up to 32GB for Enterprise Edition (EE) and 64GB for Datacenter Edition of physical memory. With the release of Windows Server 2003, SP1, X64, and IA64 platforms are fully supported. The platform of choice is expected to shift from 32-bit to 64-bit in the near future. The hardware aspects of all of these issues are discussed in Chapter 11.

SQL Server 2005 memory architecture and capabilities vary greatly from those of SQL Server 2000. 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 2005 also introduced resource monitor, Dynamic Management View (DMV), and a common caching framework. We discuss these concepts throughout this chapter.

Tuning SQL Server Memory

The SQL Server Cache Hit Ratio signifies the balance between servicing user requests from data in the data cache and having to request data from disk. Accessing data in RAM (or data cache) is much faster than accessing the same information from disk; thus, the desired state is to load all active data in RAM. Unfortunately, RAM is a limited resource. A desired cache hit ratio average hovers around 98 percent or higher. This does not mean that the environment would not benefit from additional memory. A lower number signifies that the system memory or data cache allocation is below desired size. Another reliable indicator of instance memory pressure is the page-life-expectancy counter. This counter indicates the amount of time that a buffer page remains in memory in seconds. The ideal number should be above 300 seconds or five minutes; anything lower signifies memory pressure.

Be careful not to underallocate total system memory, as it will force the operating system to start hard-paging to disk. Hard-paging is a phenomenon that occurs when the operating system goes to disk to resolve memory references. The operating system will always incur some paging. However, when excessive paging takes places, it uses disk I/O and CPU resources, which can introduce latency in the overall server, resulting in slow database performance. You can identify a lack of adequate system memory by monitoring the Memory: Pages/sec performance counter. It should be as close to 0 as possible, since a higher value indicates that more hard-paging is taking place.

SQL Server 2005 has several new features that should help with this issue. With the assistance of Windows Server 2003, SQL Server 2005 now has support for dynamic address windowing extensions (AWE) memory allocation, Hot Add Memory, a manager framework, and other enhancements. The SQL Server operating system (SQL OS) layer is the new and improved version of the User Mode Scheduler (UMS), now simply called scheduler. Consistent with its predecessor, SQL OS is a user-mode cooperative and on-demand thread-management system. An example of a cooperative workload is one that yields the processor during a periodic interval or while in a wait state, meaning that if a batch request does not have access to all of the required data for its execution, it will request its data and then yield its position to a process that needs processing time.

SQL OS is a thin layer that sits between SQL Server and Windows to manage the interaction between these environments and enables SQL Server to scale on any hardware. This was accomplished by moving to a distributed model and by bringing about an architecture that would foster locality of resources in getting rid of global resource management bottlenecks. The issue with global resource management is that in large hardware design, global resources cannot keep up with the demands of the system, slowing overall performance. Figure 12-7 highlights the SQLOS components that perform thread scheduling and synchronization, perform SQL Server memory management, provide exception handling, and host the Common Language Runtime (CLR).

Figure 12-7

The goal of this environment is to empower the SQL Server platform to exploit all of today's hardware innovation across the X86, X64, and IA64 platforms. SQL OS was built to bring together the concepts of data locality, support for dynamic configuration, and hardware workload exploitation. This architecture also enables SQL Server 2005 to better support both Cache Coherent Nonuniform Memory Access (CC-NUMA), Interleave NUMA (NUMA hardware with memory that behaves like an SMP system), Soft-NUMA architecture (registry-activated, software-based emulated NUMA architecture used to partition a large SMP system), and large SMP systems, by affinitizing memory to a few CPUs.

The architecture introduces the concept of a memory node, which is one hierarchy between memory and CPUs. There will be a memory node for each set of CPUs to localize memory and its content to these CPUs. On an SMP architecture, a memory node shares memory across all CPUs, while on a NUMA architecture, there will be a memory node per NUMA node. As shown in Figure 11-8, the goal of this design is to support SQL Server scalability across all hardware architectures by enabling the software to adapt to or emulate various hardware architectures.

Figure 12-8

Schedulers will be discussed later in this chapter, but for the purposes of this discussion, they manage the work being executed on a CPU.

Memory nodes share the memory allocated by "Max Server Memory," setting evenly across a single memory node for SMP system and across one or more memory nodes for NUMA architectures. Each memory node has its own lazy writer thread that manages its workload based on its memory node. As seen in Figure 12-9, the CPU node is a subset of memory nodes and provides for logical grouping for CPUs.

Figure 12-9

A CPU node is also a hierarchical structure designed to provide logical grouping for CPUs. The purpose is to localize and load-balance related workloads to a CPU node. On an SMP system, all CPUs would be grouped under a single CPU node, while on a NUMA based system, there would be as many CPU nodes as the system supported. The relationship between CPU node to memory node is explicit. There can be many CPU nodes to a memory node, but there will never be more that one memory node to a CPU node. Each level of this hierarchy provides localized services to the components that it manages in a hierarchy, resulting in the ability to process and manage workloads in such a way to exploit the scalability of whatever hardware architecture SQL Server is running on. SQL OS also enables services such as dynamic affinity, load-balancing workloads, dynamic memory capabilities, Dedicated Admin Connection (DAC), and support for partitioned resource management capabilities.

SQL Server 2005 leverages the common caching framework (also part of SQL OS) to have fine-grain control to manage the increasing number of cache mechanisms (Cache Store, User Store, and Object Store). This framework improves the behavior of these mechanisms by providing a common policy that can be applied to internal caches to manage them in a wide range of operating conditions. For additional information about these caches, refer to BOL.

SQL Server 2005 also features a memory-tracking enhancement called the Memory Broker, which enables the tracking of operating system-wide memory events. Memory Broker manages and tracks the dynamic consumption of internal SQL Server memory. Based on internal consumption and pressures, it automatically calculates the optimal memory configuration for components such as buffer pool, optimizer, query execution, and caches. It propagates the memory configuration information back to these components for implementation. SQL Server 2005 also supports dynamic management of conventional, locked, and large-page memory, as well as the Hot Add Memory feature. New to SQL Server 2005 Standard Edition (32-bit) is the introduction of support for the AWE memory mechanism. The Windows property, Lock Pages in Memory, is disabled by default and is only available to local administrative accounts. To ensure that the AWE enabled Memory runs as expected, it needs this privilege to allow SQL Server to manage which pages are being flushed out of memory.

Hot Add Memory offers the ability to introduce additional memory in an operational server without taking it offline. In addition to OEM vendor support, Windows Server 2003 and SQL Server 2005 EE are required to support this feature. Although there is a sample implementation script in the following section, refer to BOL for additional implementation details.

64-bit Version of SQL Server 2005

If your platform is IA64 or X64, you are in luck. SQL Server 2005 64-bit supports 1 TB of RAM, and all of it is flat (no need for /3GB or /AWE switches in your boot.ini file). The mechanism used to manage AWE Memory in 32-bit systems is also used to manage memory on 64-bit systems. Specifically, this mechanism is used to ensure that SQL Server manages what is flushed out of its memory. For this feature to work properly, the SQL Server service account requires the "lock pages in memory" privilege.

Configuring SQL 2005 for Dynamic Memory on an X86 platform

Although some of these concepts are covered in Chapter 11, we will reintroduce them as part of this chapter to bring all of these concepts together. PAE and 3GB technologies can be used independently or together. You use a boot.ini switch to activate either feature. The following provides guidelines on their use with SQL Server 2005.

Insert both the /PAE and /3GB switches into the boot.ini file to boot with the PAE-enabled kernel and give SQL Server (and other applications) 3GB of virtual memory. This allows the system to recognize up to 16GB of physical memory.

• Use only the /PAE switch with more than 16GB memory.

• SQL Server setting "AWE enabled" must be enabled for SQL Server to access memory beyond 3GB.

• Use only the /3GB switch where physical memory is greater than 4GB or less than 16GB.

See the following table for clarification:

Open table as spreadsheet

System Memory	less than 3GB	More than 3GB	More than 16GB
PAE	NO	YES	YES
/3GB	NO	YES	NO
AWE	NO	YES	YES

To activate the AWE mapped memory feature with the ability to lock pages in memory, follow these steps. The PAE kernel must be booted by adding boot.ini and AWE must be enabled in SQL Server with the sp_configure directive.

First, add this entry to your boot.ini file:

 [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(1)\WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINNT=" Microsoft Windows 2003 Datacenter Server /PAE /3GB" /fastdetect /PAE /3GB multi(0)disk(0)rdisk(0)partition(1)\WINNT=" Microsoft Windows 2003 Datacenter Server" /fastdetect 

Next, issue the following sp_configure statements:

 sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'min server memory', 15360 (15 GB) RECONFIGURE GO sp_configure 'max server memory', 30720 (30 GB) RECONFIGURE GO 

Note

Unlike its predecessor, SQL Server 2005 will not go beyond this setting even if memory is available and the system is under memory pressure. Ensure that this is the desired limit. Once enabled, memory is dynamically allocated in SQL Server 2005 Standard and Enterprise Editions running and any versions of Windows Server 2003.

Next, configure your SQL Server for Hot Add memory. In the SQL Server Configuration Manager, click SQL Server 2005 Services. In the right pane, right-click SQL Server service, and then click Properties. Select the advanced tab, and add ; -h in the Startup Parameters box. The semicolon is used as a separator for startup options. The server must be restarted to enable this feature.

Note

A 500MB memory cost to this feature is reserved for administrative purposes.

Finally, enable lock pages in memory like this:

1. Click StartRun, type gpedit.msc, and then click OK. The Group Policy window appears.

2. In the left pane, expand Computer Configuration, and then expand Windows Settings.

3. Expand Security Settings, and then expand Local Policies.

4. Click User Rights Assignment. The policies appear in the right pane.

5. In the right pane, double-click Lock pages in memory.

6. In the Local Security Policy Setting dialog box, click Add User or Group.

7. In the Select Users or Groups dialog box, add the account that has permissions to run Sqlservr.exe, and then click OK (see Figure 12-10).

8. Close the Group Policy window, and then restart the SQL Server service.

Figure 12-10

Remember that when allocating SQL Server memory on a system with more than 16GB, Windows 2003 will require at least 2GB of available memory to manage the remaining RAM). Therefore, when you start an instance of SQL Server with AWE mechanism enabled, you should not use the default max server memory setting, but instead limit it to 2GB less than the total available memory. Additional information can be found in BOL, or refer to article Q 283037.

AWE mapped memory cannot be managed or monitored through the task manager; this will have to be accomplished through sys.dm_os_memory_clerks DMV:

 select sum(awe_allocated_kb)/1024 as [AWE Allocated Memory in MB] from sys.dm_os_memory_clerks 

Note

Removing memory from the server will still require rebooting the system.

Memory-Friendly Applications

You need to identify the type of application driving the database and verify that it can benefit from a large SQL Server data-cache allocation. In other words, is it memory friendly? Simply stated, a database that does not need to keep its data in memory for an extended length of time will not see benefits from a larger memory allocation. For example, a call-center application where no two operators handle the same customer's information and where no relationship exists between customer records has no need to keep data in memory, since data won't be reused. In this case, the application is not deemed memory friendly; thus, keeping customers' data in memory longer than required would not benefit performance. Another type of inefficient memory use occurs when an application brings into memory excessive amounts of data, beyond what is required by the operation. This type of operation suffers from the high cost of data invalidation. (Larger amounts of data than are unnecessary are read into memory; thus, they must be flushed out.)