Management Data Warehouse

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:


The new way:
In the Monitored server:Right click on Data Collection > Tasks > Configure Data Collection:

Then one the wizard: choose the monitoring server and MDW Database Name that you created above. Choose a cache directory to place the data in your monitored server:

Complete it:

Once finished: You will find a set of all jobs that are installed:

These default jobs get created on the monitored server:

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.

Populating the MDW reports: This requires running the collection sets. Make sure to start them:

Then the collection sets can populate the MDW in 2 different ways:
1.a. Manually: By right clicking on the specific collection set and choosing “Collect and Upload Now”:

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:

When you want to disable the job in the monitored server, just right click the Data Collection node > choose Disable Data Collection:
See all the jobs above have been disabled consequently:

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:


exec msdb.dbo.sp_syscollector_cleanup_collector


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.

Open SQL Profiler > Create a new trace > Select Events as SQL:BatchComlpeted and RPC:Completed to limit your logs.

Export your trace. Go: File > Export > Script Trace Definition > For SQL Trace Collection Set:

Then install run script against the monitored server.

Leave a Reply

Your email address will not be published. Required fields are marked *