Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216

Question: Do Queries Always Respect Cost Threshold of Parallelism?

Answer: No. Not Always.

Let us do a quick test about it. First, let us set the Cost Threshold of Parallelism for my query to 10 and Max Degree of Parallelism to 0 for my server. I have 8 logical processors on my server, so when I set Max Degree of Parallelism to 8, it can use a total of 8 processors.

Now let us run the following query on our server. I am using the AdventureWorks sample database for this query.

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

When I run the above query, I had enabled the actual execution plan for the query. Now let see the result of the execution plan.

When you mouse of the SELECT operator, it gives us two important information.

It says the Query Cost just above 4 and query cost below 5. Additionally, the cost will be you can see that Parallelism operator says 8. In an ideal world, people would say that if the Cost Threshold of Parallelism is set to 10, the query with the cost of only around 4 should not go parallel and use only one CPU. However, the real world does not follow the same notion.

We usually talk about many such scenarios where we claim that if the query cost is less than the Cost Threshold of Parallelism, there will be no parallelism. However, as you can see in this example, it is not true.

There are many such scenarios and examples we usually talk in our Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)