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:
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.
Select Create or upgrade a management data warehouse. Then click Next.
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.
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.
The Database Name will now be populated and you can click on next.
Select a user to map to the MDW and set the Database membership role and click Next.
A summary window for the configuration will display and you can click Finish.
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.
Select the Server and Database where you want to store the data and click Next.
A summary screen will display and click Finish. Make sure SQL Server Agent is running, otherwise the setup will fail.
Once complete you will see the Data Collection sets in the Object Explorer
To access the reports, you will right-click on the Data Collection node > Reports > Management Data Warehouse
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!