0

I have two databases in the same SQL Server instance. One is a test database with a smaller number of records and total size of 500GB. Other one is production database with a size of 2.2 TB. Both database are similar with same database objects. The server is enabled for parallel execution with MAXDOP = 8 and Cost Threshold for Parallelism = 20. There are 40 logical CPUs on SQL Server 2016 Standard edition.

When I run a somewhat complex query that has multiple joins and views, on the production database the query executes fast in parallel mode.

When I run the same query in the test database (on the same instance) the query runs in serial mode. Data in test database is static too.

I do not understand why it is not running in parallel mode.

From my research, since the test database is small compared to the production database, maybe the cost of the query in the test db does not exceed 20 (cost threshold for parallelism). How can I calculate the cost of the query to confirm?

I made the query run in parallel by removing a scalar function and replacing it with an inline function, but the same query runs differently in two identical databases, that is why I am confused. I have gone through the post which explains why SQL Server does not engage parallelism.

Estimated Subtree Cost is different in both. The one running in serial has a low value of 44. The parallel one has a higher value of 312.

Since the parallel one has to deal with more data, its individual node subtree cost is higher and adds up to 312. So maybe cost is the real issue?

My cost threshold for parallelism is 20 and the one running in serial has an estimated subtree cost of 44 (> 20). So shouldn't it run in parallel?

Improve this question
1
  • 3
    It's possible that SQL Server attempted to generate a parallel plan, and its cost was higher than 44, so the serial plan was still chosen. Really all the details are in the other answer (include a trace flag to "force" a parallel plan just to see why it's not being chosen). If you have a more specific question, you should post it, along with the two execution plans for comparison.
    – Josh Darnell
    Sep 6, 2019 at 18:34

1 Answer 1

Reset to default
-2

The cost that is determined by the query optimizer is really, a best guess. The guess is based off of CPU/IO resources the optimizer believes that the query will use. If you want to really get some good information (including query costs) install sp_blitzcache - https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.