Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 15. Using SQL Server Management Data Warehouse

WHAT'S IN THIS CHAPTER

• 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.

INTRODUCING MANAGEMENT DATA WAREHOUSE

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