How to confirm the positive impact of changing Cost Threshold for parallelism to a value higher than default value of 5

Question

We have a DB Server where we found a simple update query going parallel concurrently and causing blockings and deadlock. The cost of the query was around 7. CTFP on the server was set to default value of 5.

Now we have decided to change the value of CTFP from 5 to a higher value.

Before that we just want to know what specific metrics should we consider/capture/track before and after making the change which can confirm that changing the value of CTFP to a higher value has actually improved the overall performance of DB Server. This DB has multiple user databases.

Any help would be appreciated.

Note: This server is having 4 logical processors. Hence we are not going to change the MaxDOP from its default value.

Answer 1

Checking the impact of the change

Changing the cost threshold for parallelism affects the way CPU is consumed, therefore you should lookout mainly for the behavior of the CXPACKET wait type upon your changes. Paul Randal's article SQL Server Wait Statistics contains a neat query to capture the waits.

Regarding your configured MAXDOP, see Configure the max degree of parallelism Server Configuration Option as having 4 logical processors gives you some room for configurations other than the default.

Notice about your parallel queries

A simple update query going parallel might also indicate missing indexes or badly written queries rather than a wrong cost threshold parallelism value for your server. Before changing that value you can benefit from making sure there's no tuning to be done on queries like that.

I advise you to capture expensive or repeatedly executed parallel Execution Plans on your server and analyze if they can be tuned so that they don't need to go parallel (it's different from can't go parallel). Cutting the resources can be a painful measure and will cause those queries to take longer to complete and your users could not like the delay.