O'Reilly logo

Professional SQL Server 2012 Internals and Troubleshooting by Amit Banerjee, Gavin Payne, Justin Langford, Glenn Berry, James Rowland-Jones, Christian Bolton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 3

Understanding Memory

WHAT’S IN THIS CHAPTER?

  • Understanding physical memory and how to use virtual memory addressing
  • NUMA architecture and how SQL Server uses it
  • SQL Server’s memory clerks, caches, and pools
  • Looking at SQL Server’s plan cache
  • An in-depth look at Query/Workspace memory
  • Memory configuration options including Max Server Memory, Lock Pages in Memory, and Optimize for Ad-hoc Workloads

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=1118177657 on the Download Code tab. The code is in the Chapter 3 download and individually named according to the names throughout the chapter.

INTRODUCTION

Memory, disk, and CPU are the holy trinity of resources in a computer system, and memory is first because it’s the area 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.

Understanding how Windows and SQL Server interact with and manage memory is crucial for gaining an understanding of the actual memory usage of a server you’re troubleshooting.

The first part of this chapter explains the fundamentals of how Windows manages memory and explains the difference and relationship between physical and virtual memory. The second part focuses on SQL Server’s internal memory structures, how they are managed, and how you ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required