SQL Server Performance Office Hours Episode 82 - In the Azores
In this video, I dive into some common SQL Server performance questions, offering insights and advice based on practical experience. I start with the topic of lock pages in memory, explaining why it might not be beneficial for servers with ample memory, and discuss how memory can sometimes have an unexpected impact on performance. I explore the reasons behind performance degradation when increasing memory, suggesting that it might shift bottlenecks from IO to CPU, and delve into scenarios where this can occur. I also address the issue of SQL Server underestimating row counts, identifying common culprits such as non-sargable predicates and out-of-date statistics. Lastly, I tackle the challenge of debugging constantly changing blocking chains, recommending the use of read committed snapshot isolation and the block process report. With these insights, I aim to provide a concise yet informative session, allowing viewers to quickly address their performance concerns. CHAPTERS 00:00:00 - Introduction 00:01:00 - Lock Pages in Memory 00:03:24 - Memory and Performance 00:04:38 - Row Count Estimation 00:05:30 - Blocking Chains ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🕵️♂️ FREE SQL SERVER MONITORING & Plan Analysis ━━━━━━━━━━━━━━━━━━━━━━━━━━ https://github.com/erikdarlingdata/PerformanceMonitor https://github.com/erikdarlingdata/PerformanceStudio https://plans.erikdarling.com/ ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 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