You would have seen number of articles describing the importance of MAXDOP(MAXimum Degree Of Parallelism) and why should it be changed and shouldn’t be left to its default value, which is set at 0 after installation of SQL Server – I have covered them in one of my earlier post – Why MAXDOP shouldn’t be left to default(0) and Must Be Changed. I shall be covering about CTP(Cost Threshold for Parallelism) soon in my post. The default value of that is set at 5.
As you know, MAXDOP dictates how many CPU cores will be used when queries run in parallel and CTP dictates when the query could go in parallel. And there are tonnes of article recommending them to change to a reasonable value basis hardware specification of your server and type of the load – whether it is OLTP or Data warehouse.
Changing value of MAXDOP and CTP is dynamic and will take effect immediately and doesn’t require restart of SQL services. This leads to question whether we should change these values at working hour on Production server and if yes, what is the impact. When we do change these values – it triggers cleaning up procedure cache(DBCC FREEPROCCACHE) and hence all the queries which were running fast earlier will suddenly become slower. This is because now cache is not having any query plan and it has to load them again when they are running for the first time. Next time onward, it will become normal however imagine this on a really busy server when there are tonnes of them being used from the cache and now time will be elapsed in loading them from the disk.
There are few other configurations also along with these two, which clears cache from the server and hence we need to be careful before running them on production server and should evaluate the impact. These parameters need to be changed in maintenance window.
Please leave your comment if you liked this post or have any feedback.