This is so general that first you need to check whole box for bottlenecks (CPU, Memory, Disk and Network).
You can do that by running a Performance Monitor. At the same time isn't a bad idea to run the SQL Profiler to check for Blocking Locks and long running queries.
And this is only the beginning...

Parallelism can be explicit (you set it in the query with MAXDOP option) or implicit (the engine will decide to use it or not depending on the conditions) and it's used to split a query execution in many task as existing CPU/core or as many is set by the MAXDOP option.
When well used it can be a perform boost but in the other hand can be a big bottleneck if you use it wrong.

To verify the MAXDOP configured in the SQL Server instance run the following in a New Query window:

No one can't says a good value for a MAXDOP for a query only by looking to the code.
You need to provide the query plan and the server configuration (it's a VM or physical machine? Has hyperthreading on? How many CPU? How many cores?).

By the way, why do you think that query is the one that has bad performance?

They mean cores. But if your server has hyper threading then you need to count only half of the cores and if it's NUMA architecture then you should only count the cores for one CPU.
What you can do with that query in particular is to set MAXDOP=1 so the engine won't parallelize it.

If it's a Quadcore NUMA architecture, then it's recommend to set the value to 4. If not, 8 it's the recommend value but since it's only a recommendation, you need to make tests to see if with 4 the performance is better.

Means that it will parallelize the query to run in 5 cores at same time. If the server has 8 cores, five of them will be used to run the query and the others three cores will be available for other tasks.

Ok, when I understand the right way, I can choose MAXDOPS until 8.
But 1 is not logic, because this means just one CPU and then parallelism is off.
With MAXDOP=0 means they use default all processors and cores.

Ok, when I understand the right way, I can choose MAXDOPS until 8.
But 1 is not logic, because this means just one CPU and then parallelism is off.

You should let the engine to chose to use or not the parallelism but since you are telling to always use it will but that doesn't mean that will work better.
From MSDN article (point 3): "To distinguish between queries that benefit from parallelism and those that do not benefit, The Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure. "
So you shouldn't touch the costs option also.

The threshold don't make nothing if the query has MAXDOP option set. The threshold is only to say to the engine when he can parallelize the queries. So you set it to 50, means that queries that takes less than 50 seconds won't be parallelized. Hope that is that you want.

Featured Post

Attending an event? Speaking at a conference? Or exhibiting at a tradeshow? Easily inform your contacts by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft.
If s…

To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention.
Multiple USB devices need t…

This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten.
The USB drive must be s…