Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 2. Understanding Memory

WHAT'S IN THIS CHAPTER

• Understanding physical memory and how virtual memory addressing is used

• Optimizing 32-bit systems with large amounts of memory

• Optimizing the memory configuration on 64-bit systems

• AWE and its uses on 32-bit and 64-bit environments

• Explaining MemToLeave

• SQL Server's memory clerks, caches, and pools

• Looking at SQL Server's plan cache

• Determining a setting for Max Server Memory

• An in-depth look at Query/Workspace memory

Memory, disk, and CPU are the holy trinity of resources in a computer system, and memory is first because it's the area that you're most likely to have an issue with. Memory issues can cause both disk and CPU saturation, so when troubleshooting a server issue (or at least a performance issue) you need to start by looking at the memory profile of the system.

This chapter explains what to expect from different system architectures, what can be configured, what the Microsoft best practices are, and, most important, how they were originally determined.

The first part of this chapter covers memory addressing, which includes physical memory, virtual memory, the Virtual Memory Manager, and the different options you have for tuning 32-bit and 64-bit systems. The second part focuses more on SQL Server's internal memory structures and consumers. It covers how to manage SQL Server's memory usage and how to view the details of each consumer.

WINDOWS AND MEMORY

This section covers topics that are generally considered to be outside the scope ...