I got a performance tuning assignment for an esteemed customer in the Financial Services domain. I went through the wait stats of SQL Server using the built-in SQL Server Report, called Performance Dashboard. I found CPU – Parallelism referred to as CXPACKET was leading the list, followed by Buffer IO, Lock, and Latch.
Parallelism in SQL Server refers to two settings – MAXDOP and Cost threshold for parallelism. Both are married to each other.
MAXDOP setting indicates the number of parallel threads that SQL Server can use for a query. However, the cost threshold for parallelism setting defines when SQL Server should go for parallelism (Parallel-threads).
Brent Ozar has wonderfully explained these two terminologies with a real-world example here. I think no-one can explain it in a better way.
In the journey of exploring these two settings, I also came across an excellent video from Kendra Little. You can watch the full video here.
After going through these two contents from Brent and Kendra, you would get sufficient information and knowledge to deal with the parallelism-related trade-offs in SQL Server.
I was able to resolve the wait issues of CPU – Parallelism with these knowledgeful contents. I was amazed to see, other waits such as Lock and Latch were also drastically reduced along with the CPU – Parallelism.
You would be wondering why? This answer also lies in the videos mentioned above.