Microsoft® SQL Server® 2012 Bible

977

CHAPTER

43

Management Data Warehouse

IN THIS CHAPTER

Using the MDW

Confi guring MDW

Viewing MDW Reports Creating Custom Data Collections

M

anagement Data Warehouse (MDW) is a resource and performance monitoring and reporting

framework available in SQL Server 2012 Enterprise Edition. It was fi rst introduced in SQL

Server 2008 and continues to exist in SQL Server 2012.

MDW consists of a relational database, predefi ned data collectors, and reports. The relational data-

base is used as a repository where performance statistics gathered by the data collectors are stored.

A series of reports are provided out-of-the-box with the capability to drill down into more detailed

reports about resource utilization, query performance, index utilization, and even suggestions to

improve overall SQL Server performance. MDW can collect data from SQL Server 2008 and SQL Server

2012 but for no earlier versions.

Using the Management Data Warehouse

MDW provides you with detailed performance statistics that can help you with diagnosis, trouble-

shooting, 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 fi nd 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 pro-

cesses that interfere with SQL Server operations. All potential performance problems can be iso-

lated and dissected quickly, leading to a prompt solution.

c43.indd 977c43.indd 977 7/31/2012 10:18:42 AM7/31/2012 10:18:42 AM

http://www.it-ebooks.info