SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 7. Memory Issues

SQL Server can consume hundreds of gigabytes or even terabytes of memory. This is completely normal and often a good thing—using this much memory reduces the need for physical I/O and recompilations, improving server performance.

In this chapter, I will discuss how SQL Server works with memory. I will start with an overview of how SQL Server uses memory and give you a few tips on memory configuration. Next, I’ll discuss the memory allocation process and show you how to analyze the memory usage of internal SQL Server components. Then I’ll talk about query memory grants and the ways to troubleshoot extensive query memory usage. Finally, I’ll briefly discuss memory management and potential issues with In-Memory OLTP implementation.

SQL Server Memory Usage and Configuration

SQL Server is a memory-intensive application. By default, it tries to allocate as much memory as possible and as required for the operations it performs. It does not allocate all the memory at start time; the allocation occurs as needed—for example, when it reads data pages to the buffer pool or stores compiled plans in the cache.

You can often see SQL Server consuming most of the OS memory. This is completely normal. When properly configured, SQL Server responds to OS requests and deallocates some process memory when needed. This condition is called external memory pressure. It usually occurs when the OS does not have enough memory for other applications. In small amounts, external memory ...