November 17, 2016 at 8:24 am
I recently inherited a vendor database and one of things I noticed was a daily maintenance job
set to reconfigure maxdop before launching and then setting it back upon completion. I understand the logic behind this but I'm concerned about the cachestore flushes. Thoughts and suggestions appreciated.
Configuration option 'affinity mask' changed from 0 to 0. Run the RECONFIGURE statement to install.
Configuration option 'cost threshold for parallelism' changed from 50 to 10. Run the RECONFIGURE statement to install.
Configuration option 'max degree of parallelism' changed from 6 to 0. Run the RECONFIGURE statement to install.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
November 17, 2016 at 12:29 pm
Yeah, you're recompiling all the plans after that runs. It might not be that big a deal. It really depends on your system, the code, all sorts of factors. Also, it could be negatively impacting performance. I'd certainly gather wait statistics on the server to see if there's a spike there, how bad it is. Also look at CPU and queuing around it because of the compiles.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 17, 2016 at 12:39 pm
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply