The High-Performance DBA: Total Recall, Part 2: SQL Server Memory Troubleshooting, Plan Cache, and Query Patterns

This article explores the primary causes of SQL Server memory pressure, specifically focusing on plan cache bloat from ad hoc queries and inefficient query patterns that lead to excessive memory grants. To mitigate these issues, the author recommends implementing strategies such as page compression, using the `OPTIMIZE FOR AD HOC WORKLOADS` setting, and leveraging tools like the Resource Governor. Finally, the author provides a troubleshooting framework centered on identifying recent environmental changes and analyzing specific wait statistics and performance counters.