these settings were decision of our development team:
there are 2 settings:
1 - max degree of parallelism
2 - optimize for ad hoc workloads
On OUR_DEV_SERVER:
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.