Management Data Warehouse is a very powerful tool for monitoring the SQL Server (CPU, Memory, Disk) on the server, database, and query level.
Configure the Management Data warehouse:
In the Monitoring server:Right click on Data Collection > Tasks > Configure Management Data Warehouse:
Then walk on the wizard, and choose the security configuration, the user logins that should use and administer the MDW:
Finalize the installation:
On the monitoring server, you will find one job named mdw_purge_data_[Data Collection Name chosen]:
Make sure your SQL Agent is working on both machines.
Running the MDW reports requires First populating the trace structure in the MDW then visualizing the reports.
1.b by their default schedule:
Check the setting of this collection set and schedule by right clicking on the collection set and select properties:
Then see the history of running this collection set by right clicking on the collection set then choosing “View Logs”.
Running the reports: Right click on “Data Collection” node > Reports > Management Data Warehouse > Choose from: Query Statistics History – Server Activity History – Disk Usage Summary:
An example of the reports: Query Statistics History:
You can choose the time period, zoom in to a specific query by clicking on its bar in the bar chart, and check the top 10 query statistics:
Removing the collection set:
To remove the collection set from the monitored server first disable the Data collection:
To complete removing the collection sets – cleanup the collector: go Data Collection node > Tasks > Cleanup Data Collection > then on the wizard Choose the data collector sets to clean:
This will remove all the above Jobs.
Then you can remove the individual collect sets manually:
The Old way: Create new Collection Set
This method works for SQL Server 2008.
Then install run script against the monitored server.