Using SQL Server Management Data Warehouse for performance troubleshooting

Troubleshooting SQL Server performance problems is a common and often frustrating task for SQL Server DBAs. Performance problem reports arrive after they have occurred. The typical response is to setup limited monitoring for a few days or to attempt to replicate the problem on a copied database. SQL Server 2008 introduces the Management Data Warehouse (MDW) to SQL Server Management Studio for streamlined performance troubleshooting. MDW is a set of components that enable a database developer or administrator to quickly track down the problems that could be causing performance degradation. The advantage of MDW is that it is possible to store performance data from a number of SQL Servers in one central location, an isolated server and collect in parallel SQL Server and Operating System performance counters. This data is collected by a collection set on each server and stored in a shareable management data warehouse (MDW). After collecting data, the built-in reports can be used for streamlined performance troubleshooting. The advent of the Management Data Warehouse (MDW) changes the environment by allowing continuous performance monitoring hundreds of performance counters with little effort or performance impact. This is a significant change from reactive to proactive management. The DBA needs to know the time when the problem occured and MDW allows immediate detail analysis. This paper shows how to run MDW, after that reports provided by MDW, which is used to monitor operation of SQL Server, and presented. A few examples illustrate how the use of MDW SQL DBA helped a group in the Croatian Telekom to discover the causes of blocking, downtime (time-out ) occurrencies, and slowing global operation application using SQL Server data base. Finally, it is shown how one can create a new MDW collector for collecting SQL Server Analysis Services (SSAS) 2008 performance counters.