How to Decipher CXPACKET Waits and Control Parallelism (4 hours)

Learn to tune parallelism and interpret CXPACKET waits in SQL Server.

Try the course quiz anytime.

Tuning parallelism has always been complicated — and with the introduction of new CXCONSUMER waits, it’s only getting more tricky to interpret.

What you’ll learn

In this four-hour recorded seminar, you’ll explore the “max degree of parallelism” and “cost threshold for parallelism” settings in SQL Server, and learn what they mean. You’ll learn what NUMA is, and how to tell if SQL Server is using Automatic Soft NUMA for advanced configuration of your SQL Server instances.

You’ll see CXPACKET and CXCONSUMER waits in action and learn how to interpret these waits in your own environments. Finally, we’ll discuss why there’s no single simple formula for MAXDOP for every SQL Server — but we’ll lay out a process which you can use to tune parallelism settings in each of your environments.

Scripts and Wait Stats/Duration Samples

https://github.com/LitKnd/SQLWorkbooks/tree/main/decipher_cxpacket_tune_parallelism

Lessons

Configuring MAXDOP - Instance and Database Levels (40 minutes)

Resource Governor and Query Hints (22 minutes)

Recap and Competing MAXDOP Configs (29 minutes)

How Cost Threshold for Parallelism Works (21 minutes)

Forcing Parallelism (35 minutes)

Tuning Cost Threshold for Parallelism (11 minutes)

Parallelism Killers & Inhibitors (15 minutes)

NUMA - Including Automatic Soft NUMA (26 minutes)

Wait Stats: CXPACKET & CXCONSUMER (25 minutes)

Tuning MAXDOP & Cost Threshold (21 minutes)

Links That Came Up In Discussion

Quiz on MAXDOP and Cost Threshold for Parallelism