Professional SQL Server® 2005 Performance Tuning

3.4. Memory Bottlenecks

In so many performance problem scenarios, it seems as though memory is an easy target, blamed by system administrators the world over. There are a small handful of performance counters that will allow you to correlate performance data to determine whether memory is a bottleneck in your environment.

SQL Server performance and stability are entirely dependent on sufficient available memory. A memory shortage often results in Windows serving the virtual address space (VAS) from the paging file, which will usually have an immediate and very apparent impact on performance. The first stage is to identify that you are actually seeing a memory bottleneck. After you've established a memory problem, you can then work to discover whether you have internal or external memory pressure.

Understanding the nature and characteristics of memory problems will help with early identification of these conditions and help you make a decision about the best route to resolution.

3.4.1. Types of Memory Bottlenecks

Memory problems experienced on SQL Server machines can typically be categorized in two ways: VAS or physical. If memory pressure is caused by the process itself, it's usually considered internal memory pressure. Physical memory pressure is usually caused by other Windows processes.

3.4.1.1.

3.4.1.1.1. External Memory Pressure

External memory pressure can occur on servers running SQL Server alongside other applications that may be competing for memory. From a SQL Server ...