Learning the Dark Side of SQL Server Parallelism

Lately, I was investigating a query performance issue in one of the client SQL environment.

The SQL box is a very powerful one with high-end hardware specification. Thus, the SQL Server Max Degree of Parallelism (MDP) had been optimised for giving best the coordination with multiple CPU cores. However, we soon found out that there is a "untold" relationship between MDP and Cost Threshold for Parallelism. 

The initial evidence I had gathered was from process activities. Whenever the application take that specific action, dozens of CXPACKET wait type were popped up in SQL Server Activity Monitor. And with the help from SQL Server Profiler, I was able to retrieve the problematic query from the tracer in very soon. 

By analysing the execution plan, the clear evidences were shown that parallelism had overkilled it. And to put it into the simple words that the query wasn't well handled by SQL Server who was trying to split the work to multiple cores.  

So, in order to prevent parallelism holding back the execution, I had updated "Cost Threshold for Parallelism" (The optimizer uses that cost threshold to figure out when it should start evaluating plans that can use multiple threads) and given a value higher than the cost of above query. By doing that, I am literally telling SQL Server "do not dividing the work across multiple cores unless the query cost is bigger than xxx".

After reset is done, the query execution was dropped from 9 seconds to 0.3 seconds. And wow-la, I can happily enjoy my cup of coffee now.