2

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.

2

I believe starting with Baseline Data would be good to monitor before and after changes.

Information which you might want to capture like:

  1. Basic Information
  2. System Usage
  3. File and Database Sizing Information
  4. Wait Statistics etc..

If you do not want to use third party tools, you should start looking into collecting above data and when as explained 5 Reasons You Must Start Capturing Baseline Data

Also, in order to set these baselines, you will need to create a database and can capture for duration according to the workload behaviors as explained in Capturing Baselines on Production SQL Servers.

Also refer to this MSDN forum discussion on same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.