these settings were decision of our development team:
there are 2 settings:
1 - max degree of parallelism
2 - optimize for ad hoc workloads
1. Run the following script: --//activate ‘Optimize for ad hoc workloads’ sp_configure 'show advanced options',1 GO reconfigure GO --// Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install. sp_configure 'optimize for ad hoc workloads',1 GO reconfigure go EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', 6; GO RECONFIGURE WITH OVERRIDE; GO
what should I monitor BEFORE and AFTER the changes in order to conclude whether these changes are worth applying in LIVE?
For how long should I monitor if the purpose of the monitoring is ONLY to prove THESE changes above?
I have put a simple question, expecting just a starting point. I have nothing in place at the moment, and have not started this task so far.
I prefer not to rely on third party tools - for the purpose of this exercise.