Is setting MAXDOP and Cost Threshold for Parallelism always easy? скачать в хорошем качестве

Is setting MAXDOP and Cost Threshold for Parallelism always easy?

In this video, I dive into the complexities of setting 'max degree of parallelism' (MAXDOP) and 'cost threshold for parallelism' in SQL Server. Often, advice is given to set MAXDOP based on the number of cores or sockets, and to start with a cost threshold higher than five, like 20 or 50, but I argue that these settings can be trickier than they seem, especially when dealing with highly concurrent workloads or poorly tuned queries. By running various large queries on my laptop with different MAXDOP values, I demonstrate how the number of threads per parallel branch affects query performance and worker thread usage. This video aims to provide a deeper understanding of these settings beyond just following generic advice, encouraging viewers to consider their specific environments and workloads before making changes. CHAPTERS 00:00:01 - Introduction 00:00:35 - Annoying Question 00:01:13 - Max Degree of Parallelism 00:02:17 - Cost Threshold for Parallelism 00:03:49 - Setting Max Degree Weirdness 00:04:56 - Thread Stat Explanation 00:07:03 - Thread Pool Limitations 00:08:34 - Balancing Settings Changes ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 TRAINING & COURSES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Get AI-Ready With Erik https://training.erikdarling.com/get-... SQL Server Performance Engineering Course https://training.erikdarling.com/sql-... Learn T-SQL with Erik https://training.erikdarling.com/lear... Everything Bundle: https://training.erikdarling.com/?cou... ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🛠️ CONSULTING & SERVICES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Need SQL Server performance help? https://training.erikdarling.com/sqlc... ━━━━━━━━━━━━━━━━━━━━━━━━━━ 💬 CONNECT ━━━━━━━━━━━━━━━━━━━━━━━━━━ Ask questions at Office Hours https://erikdarling.com/officehours/ Become a channel member    / @erikdarlingdata   ━━━━━━━━━━━━━━━━━━━━━━━━━━