What is the Management Data Warehouse?
The Management Data Warehouse(MDW) is a single database/Data Warehouse created inside of a SQL Server 2008 instance, which holds various performance related statistics. The performance statistics in the MDW are gathered via special data gathering routines, known as Data Collections. The MDW can contain Data Collection information from a single instance, or it can hold data collected from multiple instances(Steps shown at the end). The Data Collection process uses SSIS and SQL Agent jobs to gather, schedule and purge information in the MDW.
SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity. Each of these Data Collection definitions identifies the data that … Read the rest