One of the key concepts in SQL Server’s query optimization process is the Cost Threshold of Parallelism. This setting determines the minimum cost required for a query to be considered for parallel execution. However, it is important to note that queries do not always respect this threshold.

Let’s perform a quick test to illustrate this. First, we will set the Cost Threshold of Parallelism to 10 and the Max Degree of Parallelism to 0 on our server. Assuming we have 8 logical processors, setting the Max Degree of Parallelism to 8 allows the query to utilize all available processors.

Now, let’s run the following query on our server, using the AdventureWorks sample database:

USE AdventureWorks2014
GO
SELECT sd1.UnitPriceDiscount
FROM [Sales].[SalesOrderDetail] sd1
ORDER BY sd1.ProductID

During the execution of the query, we have enabled the actual execution plan. When we examine the execution plan, we can see some important information. The SELECT operator has a query cost just above 4 and a query cost below 5. Additionally, the Parallelism operator indicates a degree of 8.

In an ideal world, one might assume that if the Cost Threshold of Parallelism is set to 10, a query with a cost of only around 4 should not go parallel and instead use only one CPU. However, the real world does not always follow this notion.

There are many scenarios where queries with costs below the Cost Threshold of Parallelism still go parallel. This example serves as a reminder that the behavior of parallelism in SQL Server can be more complex than expected.

Understanding the intricacies of SQL Server’s query optimization process and the factors that influence parallelism can greatly impact the performance of your database. It is important to consider the specific characteristics of your workload and adjust the Cost Threshold of Parallelism accordingly.

For more in-depth discussions and examples on SQL Server performance optimization, feel free to explore our Comprehensive Database Performance Health Check.