SQL Server Performance Office Hours Episode 79 Dang is it hot again
In this video, I dive into a variety of SQL Server-related topics while enjoying the sweltering heat in Paris. Starting off, we tackle TempDb contention-while it's largely solved thanks to Microsoft's ongoing improvements, particularly the 2019 in-memory TempDb metadata feature, I share some personal experiences that highlight its potential pitfalls. Moving on, I discuss the pros and cons of relying on forced execution plans for extended periods, suggesting that while not inherently bad, it might not be the best long-term strategy. Next, we explore why SQL Server sometimes opts for a serial plan over parallel execution on high-core machines, delving into the intricacies of query costing and optimizer decisions. Finally, I address when batch mode can be less effective, pointing out specific scenarios where rowstore plans with batch mode bitmaps might not perform as expected. As always, I wrap up by encouraging viewers to stay cool (and hydrated) while enjoying their own adventures in SQL Server troubleshooting! CHAPTERS 00:00:00 - Introduction 00:01:28 - TempDb Contention in 2022+ 00:03:57 - Serial Plan on a 32 Core Machine 00:06:04 - Batch Mode Considerations 00:08:45 - Memory Pressure Indicators ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🕵️♂️ 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