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).
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.