SQL Server: Not Good – Cost Threshold for Parallelism Default is Five

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

As per the Microsoft, Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries.

The Cost Threshold for Parallelism parameter is very important for Query Optimizer to prepare the query execution plan.

I wrote a title like Not Good – Because the default value of Cost Threshold for Parallelism is 5 which is very less now a day.

The value you define here which is for query optimizer where it finds a cheaper parallel execution plan, and if it fails to find parallel execution plan in given cost, it chooses the serial execution plan which degrades the query performance.

Now a day, we are dealing with millions/billions of data, so parallel query execution plan is good for big/complex queries so the default value 5 which is a tiny number.

The parallel execution plans will give you more suggestion on missing indexes which we can take for further query optimization.

I would suggest to change the value of Cost Threshold for Parallelism from 5 to 50 or even more.

How to change?

Using Script:

Using SSMS: