With MDW, you can collect SQL Server performance information, IO usage, disk usage, memory usage, locks, blocking, queries taking longer time, couple of performance counters etc. The useful information it collects can be preserved for long time and can be analyzed for the purpose of troubleshooting.
MDW creates it’s own database warehouse (MDW) to collect information. There are pre-configured collection sets including Server Activity, Query Statistics and Disk Usage. The data collected in MDW is published in SQL Server Management studio with very useful reports. This feature is not configured by default and can be found under Management in Object Explorer of SQL Server Management Studio.

As mentioned above this feature is by default disabled, this can be enabled and configured by selecting ‘Configure Management Data Warehouse wizard’ option after right clicking on Data Collection.

Selecting ‘Configure Management Data Warehouse’ starts a wizard as shown below to configure data warehouse.

First part of configuration is to create Database Warehouse and second part is where you setup data collection.
In the following part, we are setting the database for first time, and selected option “Create or upgrade a management data warehouse”.

Create New database to collect information by select New option from following screen.


From the following screen, select Login and map MDW users to that Login or Logins. There are three roles for MDW, admin, writer and reader. You may either create a single Login and give admin or you may give appropriate rights to Logins, as per your requirements.

Review the information before clicking on Finish.


Since the database has been configured, the next part is to set the data collection. In case if MDW database has been setup on another server, this will be your first step for this server.

Data collector collects some information from traces and other parts which it keeps locally in a folder before pushing it to database. In the following Window you may chose directory to keep this information.

Review the information before clicking on Finish.


Once this process is completed, Next step is to schedule the jobs, configure the maximum retain period of collection.

Right click on Disk Usage, select Properties. In the property Window review and change the information like the frequency of collection, default service account to run jobs associated with current collector. You can also configure the ‘Retain data’ property and specify number of days you want to keep the information.

Follow the same practice for all three collector sets. Once it will configure correctly you will see reports and useful information by right clicking on Data Collection and selecting the appropriate report, as shown in following screen shot.

Server Activity report will look like as shown below.

By default it will show you data for last four hours, however you can change the date and range you want to see the reports. As shown below, click on calendar icon and select date range and number minutes/hours you want to see the report.

In this case, the report will be shown for data collected for 15 minutes starting 4 PM.

Following same way, you can view other reports as well. You may explore this feature to understand this and get used to this.
This is a great and may save money for your organization, if you are planning to buy third party monitoring tool for SQL Server.