rarara (1/20/2016)
Hi guys, thanks for your input. i didn't include every detail for fear of getting bogged-down in process. In terms of what is currently running through the night we have:Snapshot creation which is used to populate an MI database. This snapshot is then dropped after the export completes.
Maxdop is changed as the performance of the MI population is improved, and it is changed back again after that.
Obviously as part of the above operations the cache is cleared, and that in itself isn't an issue - we are comfortable with that and there is no noticeable performance hit when queries are getting compiled again. I agree that "Create and drop snapshot in order to clear the cache?" was a lazy way to achieve an aim, but it wasn't me who did this step at the time.
Unfortunately I don't have the execution plan info as the issue hasn't occurred again before or since. To my mind this points towards a primary cause of:
1) At some point between the 6am FREEPROCCACHE running and the ramp-up of poor performance shortly afterwards, something caused the queries to be compiled with poor plans. There are a couple of small jobs but I believe that they use different queries to the type of ones that were performing poorly via general user access/tasks.
I am still confused as to why this happened in isolation - would I not expect to see this problem regularly if that was the case??
You wouldn't necessarily expect to see the problem regularly. To use the typical sort of parameter sniffing problem as an example, if some procedures just got plans generated for an atypical value that is used, say, 1/10000 calls, then it would be relatively rare for a recompile of that query to result in a plan optimized for that atypical value.
Of course, that's just one possible reason; without any data it's hard to say for sure what actually happened.
I second Eirikur's suggestion. There's no need to change MAXDOP for the entire server; use the MAXDOP query hint on the queries you have determined benefit from a particular MAXDOP.
Cheers!