Microsoft SQL Server 2012 Bible

Using the Management Data Warehouse

MDW provides you with detailed performance statistics that can help you with diagnosis, troubleshooting, trending, and reporting of key performance metrics. If some performance problem exists, you can use MDW to quickly diagnose and troubleshoot performance bottlenecks and analyze them over time. Examining the same time periods on different days gives you a way to find high-resource utilization patterns, performance bottlenecks, and expensive queries that cause performance degradation.

If performance suddenly decreases, you can immediately investigate and pinpoint the root cause of performance issues, such as high-memory utilization, an increase in data volume, or other processes that interfere with SQL Server operations. All potential performance problems can be isolated and dissected quickly, leading to a prompt solution.

In the past, it required a large effort to gather the necessary metrics and necessary statistics to pinpoint a performance issue. Database administrators would normally resort to third-party monitoring tools or spending a large amount of time profiling queries and consuming Dynamic Management Views (DMVs) to pinpoint a performance issue. MDW makes the process easier; it takes less time and provides an automated and simple way to report what is happening in real time, and also view performance patterns historically. It collects data, aggregates data, analyzes data, and reports on data. It does this with a minimal performance ...