November 6, 2018 at 11:50 am
Hi all,
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.
November 6, 2018 at 2:56 pm
mig28mx - Tuesday, November 6, 2018 11:50 AMHi all,
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.
Not sure that's a reliable way to figure cost threshold for parallelism. But if you want to do that based on subtree costs then you probably want to get a good understanding of where that cost comes from. Check the following article:
what “estimated subtree cost = 1†means ? or a great SQL server history story
Sue
November 6, 2018 at 4:04 pm
Hi Sue,
Thank you for your input. That was a really cool story. I don´t know if the design of the page was on intention or it really comes from another latitude of earth where the page designs looks upside down for us.
Miguel.
November 6, 2018 at 4:51 pm
mig28mx - Tuesday, November 6, 2018 4:04 PMHi Sue,
Thank you for your input. That was a really cool story. I don´t know if the design of the page was on intention or it really comes from another latitude of earth where the page designs looks upside down for us.Miguel.
Some of the left side is in another language so it could look upside down. All languages look a bit upside down to me. Except SQL.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply