Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s.

MDW provides 3 reports and you are able to create your own custom reports. The reports will provide suggestions for improving performance, based on information gathered by the Data Collector. The 3 main reports are Server Activity History, Query Statistics History and System Disk Usage.

Using a basic configuration of the MDW enables a DBA to perform tasks such as:

  • Proactive Tuning
  • Historical Query Analysis
  • Performance Baselining
  • Database Growth Forecasting
  • Storage Planning

With just a few simple clicks you can answer questions such as:

  • What are my most expensive queries in terms of a given resource measure (CPU/Duration/IO)?
  • What is my SQL Server instance waiting on typically?
  • Which are my fastest growing databases?
  • Why is query X blocking?
  • Is SQL Server busier than normal for this time of day?

Setting up MDW takes about 15 minutes, it is quick and simple!

To configure MDW you can follow these steps:

Step 1:
Expand the Management node in Object Explorer and right click on Data Collection and select Configure Management Data Warehouse. This will open up a wizard.
Step1
Step1wizard

Step 2:
Select Create or upgrade a management data warehouse. Then click Next.
Step2

Step 3:
Type in a Server Name, by default the server you are currently on will be populated in the name. You are able to set up MDW on a separate server and do the data collection for another server.
Step3

Step 4:
Click on New next to Database Name. A pop-up will appear to create a database for the MDW to collect and write the data to. Type in Database Name and click ok.
Step4

Step 5:
The Database Name will now be populated and you can click on next.
Step4db

Step 6:
Select a user to map to the MDW and set the Database membership role and click Next.
Step6

Step 7:
A summary window for the configuration will display and you can click Finish.
Step7
Step7comp

Step 8:
Next we need to set up a data collection to collect data from the server and databases you are interested in getting information from. Right click on the Data Collection node again and select Configure Management Data Warehouse. This time you will select Set up data collection and click Next.

Step8

Step 9:
Select the Server and Database where you want to store the data and click Next.

Step9

Step 10:
A summary screen will display and click Finish. Make sure SQL Server Agent is running, otherwise the setup will fail.
Step10
Step11

Once complete you will see the Data Collection sets in the Object Explorer
Step12

To access the reports, you will right-click on the Data Collection node > Reports > Management Data Warehouse
Step13

In my next post I will be going into more detail about the MDW reports and how to get the most value out of them!

2 thoughts on “Management Data Warehouse (MDW)

Leave a Reply