Chapter 15. Using SQL Server Management Data Warehouse


  • Purpose of the Management Data Warehouse (MDW)

  • Configuring the MDW

  • Creating custom collectors

  • Reporting on the MDW

The Management Data Warehouse provides a solution to medium and long-term database performance data collection and reporting. When SQL Server 2005 was released, the user community became very excited about the visibility of systems internals provided by dynamic management views (DMVs) and dynamic management functions (DMFs). However, the DMVs have a limitation because data is not persisted between service restarts. This is a limitation because data that is useful for performance analysis, trending, and capacity analysis is lost each time the SQL Server service is restarted.

Performance Data Collection is the method of collecting SQL Server performance data organized as sets, from targets for consolidation and reporting in the MDW. Three System Data Collection Sets are created during the MDW setup and these provide server performance overview. Additionally MDW provides a framework for custom data collection that is useful for independent software vendors or applications that were developed in-house. The Management Data Warehouse is a relational data warehouse configured and managed within SQL Server Management Studio.


The Management Data Warehouse is intended as a centralized repository for performance data from servers across an enterprise, providing an out-of-the-box ...

Get Professional SQL Server® 2008 Internals and Troubleshooting now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.