I recently changed CTFP=15 on my producion server. Why I use that number and not the 50 rule of thumb from microsoft´s advice? Because on my query plans, I have none subtree plan cost bigger than that number. So I believe that it is a good starting point.
After doing this, on the stastitics I still have seen a lot of cxpackets waits that is a sign of paralelism. Also I have seen a lot of waiting tasks on activity monitor.
With this in mind I decided to tweak MAXDOP due to it is still had the default value of 0. I have to admit that my vision was not clear on what these db parameters mean in terms of performance. The short strory is: if you have a low CTFP value,by example 5 (defalut value), and you have MAXDOP=0 (default value) you will end with most of your querys will run in parallel in all of your core processor's. With this situation I ended with a lot of cxpacket waits and a lot of waiting tasks.
To be clear, parallel is NOT a bad thing. In fact it is necesarly for log running queries against a large data set. With MAXDOP=8, you are limiting the number of core processor´s that SQL will use to handle parallel execution situation.
In my case, with CTFP=5 and MAXDOP=8, I have noticed the following:
1.- CXPACKET waits have diminished dramatically.
2- Waiting tasks have decreased 90%.
3.- Batch requestes, have increased 50%.
The third point, It is a good thing. This means that the SQL now are doing more things than just to wait to resources releasing
I have to wait until the peak use (month close) to really test this settings. But I´m confident.
Now, after all this boring story, my question for you all, is: what is the right moment to tweak this settings again? Until I detect a lot of cxpacket? what if this settings can be improved?
Thank you in advance.