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:

image

Then walk on the wizard, and choose the security configuration, the user logins that should use and administer the MDW:

image30

Finalize the installation:

image36

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

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:
image44

Complete it:
image28

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

These default jobs get created on the monitored server:
image40

On the monitoring server, you will find one job named mdw_purge_data_[Data Collection Name chosen]:

image41

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:
image42

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”:
image33

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:

image34

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:

image39

 

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:

image29


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:
image27

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:

image45

exec msdb.dbo.sp_syscollector_cleanup_collector

image43

This will remove all the above Jobs.
Then you can remove the individual collect sets manually:

image32

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.
image38

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

Then install run script against the monitored server.

Leave a Reply

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