We have a prod system that sometimes gets stuck and nothing we do helps but changing maxdop parameter on live system, after we change that all gets back normal. And it does not even matter if we change maxdop to 0 from 4, or to 2 from 1 , as long as we change maxdop. It has been going on for about a year (and during that time this has happened about 4 or 5 times) and we have not been able to find what is causing it. Since the system is going to be replaced soon and we have sort of workaround for it, I am not asking for help to figure this out, just to understand what happens to existing sessions when maxdop is changed, so my question is: What happens to queries that are in progress when we change maxdop in live system and while they are executing?
Add a comment
|
1 Answer
plan cache
Changing MAXDOP will clear out the plan cache, which means queries will have to come up with new execution plans. If your server was tanked because of an issue with one or more poor query plans, this could "fix" the problem. There are a number of other plan affecting settings and server commands that would have a similar outcome.
Nothing happens to queries that are currently executing, but usually it's enough that subsequent executions use a different plan that the performance issue calms down, even if a couple/few queries are still running with it.
answered Dec 8, 2021 at 17:15
Erik Reasonable Rates DarlingErik Reasonable Rates Darling
44.2k14 gold badges140 silver badges514 bronze badges