sp_PressureDetector: What SQL Server Looks Like Under Memory Pressure

In this video, I dive into how SP\_Pressure Detector can help identify memory pressure issues in SQL Server. I discuss two primary forms of memory pressure: queries asking for large memory grants and the buffer pool being constantly swapped out to make room for these grants. I walk through a demo using a specific query with a high memory grant request, showing how this affects the buffer pool and overall system performance. By leveraging SP\_Pressure Detector, you can gain insights into memory consumption patterns, understand the impact of memory grants on your server, and learn strategies to mitigate these issues, whether by adjusting resource governor settings or upgrading your server's hardware. CHAPTERS 00:00:00 - Introduction 00:00:59 - Memory Pressure Types 00:02:15 - Memory Grants and Buffer Pool Tug of War 00:04:02 - Dealing with Memory Grants 00:05:34 - Setting Max Server Memory 00:07:15 - Query Execution in Batch Mode 00:08:44 - Top Memory Consumers 00:10:10 - Buffer Pool Memory Usage 00:12:22 - CTE Query Execution 00:13:36 - SQL Reservations and Memory Grants 00:15:06 - Second Workload Introduction 00:17:02 - Common Memory Pressure Scenarios 00:18:28 - Resource Semaphore and Memory Grants 00:19:14 - SP Pressure Detector Insights 00:20:34 - Forced Grant Count Analysis 00:20:55 - Conclusion ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 TRAINING & COURSES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Get AI-Ready With Erik https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY SQL Server Performance Engineering Course https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING Learn T-SQL with Erik https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL Everything Bundle: https://training.erikdarling.com/?coupon=SPRINGCLEANING ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🛠️ CONSULTING & SERVICES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Need SQL Server performance help? https://training.erikdarling.com/sqlconsulting ━━━━━━━━━━━━━━━━━━━━━━━━━━ 💬 CONNECT ━━━━━━━━━━━━━━━━━━━━━━━━━━ Ask questions at Office Hours https://erikdarling.com/officehours/ Become a channel member https://www.youtube.com/@ErikDarlingData/join ━━━━━━━━━━━━━━━━━━━━━━━━━━