question

Heisenberg avatar image
0 Votes"
Heisenberg asked TomPhillips-1744 answered

value setting for cost threshold for parallelism

I want to review cost threshold for parallelism settings on my servers. How can i come up with optimal value that wont impact negatively on my production server after this setting is changed? In other words i want to find out right value for this parameter.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @WannaBeSQLServerPro, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

As you may know, the default value for this parameter 5. And, as you also may know, this default value is considered to be too low with the hardware of today, and people recommend it to be set at 30 or 50.

Whether there actually is some data to back this up, or if this is just a fad, I don't know. I don't think that I have ever seen someone to provide hard numbers on this.

And even if someone would have hard numbers for one system, how would we know that they apply to our own systems?

The lower this value is, the more likely it is that SQL Server will apply parallelism in query plans. And what's wrong with? Hey, that could mean that the query runs faster! Well, at least as long as we have the server to ourselves, that's alright. But if 100 processes runs the query simultaneously, there will not be CPUs to run all at once. And since parallelism comes with overhead, it is likely that the total execution time is longer than if the plan had been serial.

But would you care about 100 processes running in parallel? Yes, on a busy OLTP system many concurrent users you do. Less so if you have a system with just a few users.

In the end, you can only find out what is the optimal setting by testing with your own workload. And this is a lot of work. One is to set up the test. Another is to decide on the metrics. Should you go by CPU time? Or query duration? Keep in mind that, it is the latter that matters to users. But if the CPU rises while the duration goes down, it may indicate that if the workload grows by N per cent, you may max out on the CPU, and on that point the duration will start to rise.

If you really want to test this, I say this: turn on Query Store, and collect data for a week. Save the data somewhere, and clear Query Store and change the parameter some other value and run for one more week (unless it is clear that things have deteriorated). Save and compare. Clear again and try next value etc. But here you also need to understand if differences are due to changes to differences in the workload, or because one week was busier than another.

Personally, I don't care to change the value from the default value of 5, unless I can see that I have too much parallelism. Then again, if someone else has set it to 30, I don't bother to change it. Because, to be honest, I don't have a clue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @WannaBeSQLServerPro,

Welcome to Microsoft Q&A!

Please get the values for the costs on your plans from this blog. The next step is to take these results and derive an average and a standard deviation.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

As with most of these configuration options "it depends". Bottom line is you will need to test it yourself and come up with what works for your specific situation.

As Erland said, I normally default it to 50 for OLTP servers, and test performance. I find the performance is generally better when you have small lookup tables and mostly single row operations in OLTP environments.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.